MySQLまたはMariaDBのデータベース内のカラム数を確認する方法をお伝えします。
サンプルデータベースを次のように準備します。
-- データベース作成
CREATE DATABASE sample_database;
(out)
-- データベース指定
use sample_database;
(out)
-- テーブル作成
CREATE TABLE sample01 (col11 INT, col12 CHAR(10));
CREATE TABLE sample02 (col21 INT, col22 CHAR(10), col23 DOUBLE);
データベース内のテーブルに関する情報はinformation_schema.columnsにあります。
SHOW FULL COLUMNS FROM information_schema.columns;
(out)
(out)+--------------------------+---------------------+-----------------+------+-----+---------+-------+------------+---------+
(out)| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
(out)+--------------------------+---------------------+-----------------+------+-----+---------+-------+------------+---------+
(out)| TABLE_CATALOG | varchar(512) | utf8_general_ci | NO | | | | select | |
(out)| TABLE_SCHEMA | varchar(64) | utf8_general_ci | NO | | | | select | |
(out)| TABLE_NAME | varchar(64) | utf8_general_ci | NO | | | | select | |
(out)| COLUMN_NAME | varchar(64) | utf8_general_ci | NO | | | | select | |
(out)| ORDINAL_POSITION | bigint(21) unsigned | NULL | NO | | 0 | | select | |
(out)| COLUMN_DEFAULT | longtext | utf8_general_ci | YES | | NULL | | select | |
(out)| IS_NULLABLE | varchar(3) | utf8_general_ci | NO | | | | select | |
(out)| DATA_TYPE | varchar(64) | utf8_general_ci | NO | | | | select | |
(out)| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | NULL | YES | | NULL | | select | |
(out)| CHARACTER_OCTET_LENGTH | bigint(21) unsigned | NULL | YES | | NULL | | select | |
(out)| NUMERIC_PRECISION | bigint(21) unsigned | NULL | YES | | NULL | | select | |
(out)| NUMERIC_SCALE | bigint(21) unsigned | NULL | YES | | NULL | | select | |
(out)| DATETIME_PRECISION | bigint(21) unsigned | NULL | YES | | NULL | | select | |
(out)| CHARACTER_SET_NAME | varchar(32) | utf8_general_ci | YES | | NULL | | select | |
(out)| COLLATION_NAME | varchar(32) | utf8_general_ci | YES | | NULL | | select | |
(out)| COLUMN_TYPE | longtext | utf8_general_ci | NO | | NULL | | select | |
(out)| COLUMN_KEY | varchar(3) | utf8_general_ci | NO | | | | select | |
(out)| EXTRA | varchar(27) | utf8_general_ci | NO | | | | select | |
(out)| PRIVILEGES | varchar(80) | utf8_general_ci | NO | | | | select | |
(out)| COLUMN_COMMENT | varchar(1024) | utf8_general_ci | NO | | | | select | |
(out)+--------------------------+---------------------+-----------------+------+-----+---------+-------+------------+---------+
そのinformation_schema.columnsを用いて、sample_databaseデータベース内のカラム数を確認するSQLコードは次になります。
SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = 'sample_database';
(out)
(out)+----------+
(out)| COUNT(*) |
(out)+----------+
(out)| 5 |
(out)+----------+
(out)1 row in set (0.00 sec)
MySQL データベース内のカラム数を確認する方法
