この記事はMySQL Casual Advent Calendar 2018 13日目の記事です。
テーブル作成時
カラムの型を定義しますが、数字列に対して
というようにカッコ()の中に数字を入れていると思いますが、この数字がそもそも省略できることは知っていると思います。
省略するとデフォルト値(bigintなら20、tinyintなら4)が入ることまではちょっと調べたり、テーブル作った後に show create table
した結果から確認できるのでその数字まで知っている人も多いと思います。
この数字、入力可能桁数だと思ってません?
僕は最初そう思ってました。 他のDBもそうだし、文字列型についてはそうですから。
でも整数型は違うんです。
この数字、表示幅なんです。
何を言っているのかというと、
- MySQLはそれぞれ整数型は入力できる桁数が固定(入力可能桁数についてはこちら参照)
- MySQLの整数型は表示幅をオプションで指定する拡張をサポートしている
- カラムの指定時に表示する値を左から0埋めすることがオプションで可能
というわけです。
ここを読むと、あのカッコ内の数字は「表示幅」ということが分かります。
https://dev.mysql.com/doc/refman/5.6/ja/numeric-type-attributes.html
つまり、MAXの表示幅があって、それ以下の桁数の数字に対しては左側にスペースが埋められてるということだそうだ。
実際に見てみましょう
MySQL8.0.13で試してみました。
mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 25006739 Server version: 8.0.13 MySQL Community Server - GPL Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed
カラムにzerofill という文字列を追記します
unsigned
は zerofill
を指定した時点でデフォルトとして指定されているんですが、あえて書いておきます。(ちなみにsigned
を使えば負数を使わないようにできます)
mysql> CREATE TABLE `test_t_int` ( -> `test_tint_id` tinyint(1) not null COMMENT 'test_tint_id', -> `test_tint_id2` tinyint(1) unsigned zerofill default null COMMENT 'test_tint_id2', -> PRIMARY KEY (`test_tint_id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='test_t_int'; Query OK, 0 rows affected (0.49 sec)
tinyint型は-128から127までの整数値を扱います。 https://dev.mysql.com/doc/refman/5.6/ja/numeric-type-overview.html 但し、型にはtinyint(1)と指定しました 試しにデータを入れていきましょう。
mysql> insert into test_t_int values(0,0); Query OK, 1 row affected (0.10 sec) mysql> insert into test_t_int values(1,1); Query OK, 1 row affected (0.08 sec) mysql> insert into test_t_int values(2,2); Query OK, 1 row affected (0.05 sec) mysql> insert into test_t_int values(10,10); Query OK, 1 row affected (0.05 sec) mysql> insert into test_t_int values(100,100); Query OK, 1 row affected (0.06 sec) mysql> insert into test_t_int values(127 ,127); Query OK, 1 row affected (0.07 sec)
もし桁数であれば10を入れたところでこけるでしょうが、問題無く入ってます。
mysql> insert into test_t_int values(128 ,128); ERROR 1264 (22003): Out of range value for column 'test_tint_id' at row 1
128を入れたところでこけましたね。想定通りです。
mysql> insert into test_t_int values(-1 ,1); Query OK, 1 row affected (0.07 sec) mysql> insert into test_t_int values(-1 ,-1); ERROR 1264 (22003): Out of range value for column 'test_tint_id2' at row 1
通常のカラムのほうには負数も入りました。 ただし、zerofill 指定のカラムのほうには入りませんでした。
mysql> select * from test_t_int; +--------------+---------------+ | test_tint_id | test_tint_id2 | +--------------+---------------+ | -1 | 1 | | 0 | 0 | | 1 | 1 | | 2 | 2 | | 10 | 10 | | 100 | 100 | | 127 | 127 | +--------------+---------------+ 7 rows in set (0.00 sec)
結果を見ると、特段気にすることはなさそうな雰囲気。 (0埋めする桁が1なので実質0が入る部分が無いんだから当たり前なんですが)
カッコの数字を255(MAX)にしてみましょう
mysql> CREATE TABLE `test_t_int2` ( -> `test_tint2_id` tinyint(255) not null COMMENT 'test_tint2_id', -> `test_tint2_id2` tinyint(255) unsigned zerofill default null COMMENT 'test_tint2_id2', -> PRIMARY KEY (`test_tint2_id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='test_t_int2'; Query OK, 0 rows affected (0.42 sec)
上記の通りです。カッコの中の数字とテーブル・カラム名をちょっと変更したこと以外は変えてません。
mysql> insert into test_t_int2 values(0,0); Query OK, 1 row affected (0.08 sec) mysql> insert into test_t_int2 values(1,1); Query OK, 1 row affected (0.06 sec) mysql> insert into test_t_int2 values(2,2); Query OK, 1 row affected (0.08 sec) mysql> insert into test_t_int2 values(10,10); Query OK, 1 row affected (0.06 sec) mysql> insert into test_t_int2 values(100,100); Query OK, 1 row affected (0.05 sec) mysql> insert into test_t_int2 values(127 ,127); Query OK, 1 row affected (0.11 sec) mysql> insert into test_t_int2 values(128 ,128); ERROR 1264 (22003): Out of range value for column 'test_tint2_id' at row 1 mysql> insert into test_t_int2 values(-1 ,1); Query OK, 1 row affected (0.06 sec) mysql> insert into test_t_int2 values(-1 ,-1); ERROR 1264 (22003): Out of range value for column 'test_tint2_id2' at row 1 mysql>
データ登録についても同様ですね。 ではSELECTしてみましょう。
mysql> select * from test_t_int2; +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test_tint2_id | test_tint2_id2 | +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -1 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 | | 0 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | | 1 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001 | | 2 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002 | | 10 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010 | | 100 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100 | | 127 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000127 | +---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 7 rows in set (0.00 sec)
2個目のカラムが0で左が全て埋められてますね。 数えてみると、確かに255文字になっていました。
なぜこの話をしたのか?
実は先日書いた非公式版のほうのUpgrade Checkerに項目として入ってて、公式版も少し前のバージョンには入ってました。 チェックとしてはfulfillを意識したカラム定義になっているものはないか?というものでした。 (つまり、デフォルトじゃない数字が入ってる整数型カラムが無いかというチェック)
公式からはチェックとしてはずれていたので、yoku0825さんと話してみたところ、おそらくOracleはzerofill属性はSQL標準ではないのでzerofill のサポートをやめたいという思いがあって、警告として当初はいれてたんだろうという見解でした(外した理由は、まあ、決まってないからなんですかね?)。
自社のDBではzerofill は使ってないし、使うような予定も無さそうですが、このチェックにひっかかるカラムはMySQL上にたくさんあるので、あんまり指定する意味も無いし、移行時は直しちゃおうかなぁというのが個人的な気持ちです。