MySQLの INFORMATION_SCHEMA.COLUMNS CHARACTER_MAXIMUM_LENGTH で得られる値についての調査
概要
諸事情によりMySQLのテーブルの文字列型カラムに入る最大値を取得したい欲求が発生し、 INFORMATION_SCHEMA データベースの COLUMNS テーブルの CHARACTER_MAXIMUM_LENGTH*1 を利用することになったので、
CHARACTER_MAXIMUM_LENGTH を参照した時、それぞれのデータ型がどのような値を返すのかのメモを残します。
まとめ
- 文字列型の場合にのみ、そのカラムに入る文字列の長さの最大値が返ってきます。
- サイズが指定できる文字列型(VARCHAR, VARBINARYなど)の場合、指定されたサイズが返ってきます。
- サイズが指定できないような文字列型(TEXTなど)の場合、その型に格納できる文字列の長さの最大値が返ってきます。
- ENUM型、SET型の場合は少し特殊です
- ENUM型の場合、要素の中で最も長い文字列の長さを返します
- SET型の場合、
sum(各要素の文字列の長さ + 1) - 1
と言った風になります(例えば、SUM('A', 'BB', 'CCC')
となっているカラムがあれば、 8が返ってきます)
- 文字列型以外のカラムの型の情報を見ようとすると NULL が返されます
調査内容について
- 環境 : MySQL5.6, MySQL8.0
- 調査のために作ったテーブル
CREATE TABLE sandbox ( `char` CHAR(64), `char_big` CHAR(255), `varchar` VARCHAR(16), `varchar_big` VARCHAR(255), `tinytext` TINYTEXT, `text` TEXT, `mediumtext` MEDIUMTEXT, `longtext` LONGTEXT, `binary` BINARY(32), `binary_big` BINARY(255), `varbinary` VARBINARY(128), `varbinary_big` VARBINARY(255), `tinyblob` TINYBLOB, `blob` BLOB, `mediumblob` MEDIUMBLOB, `longblob` LONGBLOB, `enum` ENUM( 'hoge', 'fugafuga', 'piyopiyopiyo' ), `set` SET( 'foo', 'barbar', 'bazbazbaz' ), `set2` SET( 'wktk', 'hshs', 'blahblahblah', 'orz' ), `date` DATE, `datetime` DATETIME, `timestamp` TIMESTAMP, `time` TIME, `year` YEAR, `tinyint` TINYINT, `smallint` SMALLINT, `mediumint` MEDIUMINT, `int` INT, `bigint` BIGINT, `decimal` DECIMAL, `float` FLOAT, `double` DOUBLE, `bit` BIT, `geometory` GEOMETRY, `point` POINT, `line_string` LINESTRING, `polygon` POLYGON, `geometorycollection` GEOMETRYCOLLECTION, `multipoint` MULTIPOINT, `multilinestring` MULTILINESTRING, `multipolygon` MULTIPOLYGON );
実行結果
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'sandbox';
+---------------------+--------------------------+ | COLUMN_NAME | CHARACTER_MAXIMUM_LENGTH | +---------------------+--------------------------+ | char | 64 | | char_big | 255 | | varchar | 16 | | varchar_big | 255 | | tinytext | 255 | | text | 65535 | | mediumtext | 16777215 | | longtext | 4294967295 | | binary | 32 | | binary_big | 255 | | varbinary | 128 | | varbinary_big | 255 | | tinyblob | 255 | | blob | 65535 | | mediumblob | 16777215 | | longblob | 4294967295 | | enum | 12 | | set | 20 | | set2 | 26 | | date | NULL | | datetime | NULL | | timestamp | NULL | | time | NULL | | year | NULL | | tinyint | NULL | | smallint | NULL | | mediumint | NULL | | int | NULL | | bigint | NULL | | decimal | NULL | | float | NULL | | double | NULL | | bit | NULL | | geometory | NULL | | point | NULL | | line_string | NULL | | polygon | NULL | | geometorycollection | NULL | | multipoint | NULL | | multilinestring | NULL | | multipolygon | NULL | +---------------------+--------------------------+