0から始まるかもしれない固定長整数 をINT型に入れたい! @uwabami_garden MySQL Casual Talks vol.12

自己紹介 • Twitter: 蟒蛇(@uwabami_garden) • 名前: 松浦 庸介 • 所属: hachidori株式会社 • 詳しくはこちら https://hachidoriinc.com/ • 表紙のキャラクターは弊社サービスCASTのマスコット「てんちょ」です • https://twitter.com/CAST_tencho

現状 • 店舗ごとに9桁の数字(identifier)を発行し、識別子として使っている • “012345678”のように0から始まることもある • 今はvarchar型に文字列として入れている • 簡略化したテーブル設計はこんな感じ • これはこれで問題なく動く show create table shops¥G *************************** 1. row *************************** Table: shops Create Table: CREATE TABLE `shops` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `identifier` varchar(255) NOT NULL, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`), ) ENGINE=InnoDB AUTO_INCREMENT=**** DEFAULT CHARSET=utf8mb4

INTにしたい • 行数が増えると、データ格納効率が無視できなくなってくるので、INTにしたい • とにかくINTにしたい • INT(9)で桁を指定すればよいのでは??? CREATE TABLE `int_table01` ( `id` int(11) NOT NULL AUTO_INCREMENT, `identifier` int(9) NOT NULL, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

どうして mysql> INSERT INTO int_table01 SET identifier=012345678, name="現場猫"; Query OK, 1 row affected (0.00 sec) mysql> SELECT identifier FROM int_table01 WHERE name like "現場猫"; +------------+ | identifier | +------------+ | 12345678 | ←9文字にならない! +------------+ 1 row in set (0.02 sec)

ただINT(n)と書いてもn桁にならない • WHERE句による比較はともかく、SELECTで桁数が足りないIDが返ってくると困る • 実はこの問題があるので文字列型で格納していた • 解決するには2通りある • 他にもあったら教えてください 1. ZEROFILL INTを使う 2. SELECT時にLPADでパディングする

INTにZEROFILLをつける • INT(n)にZEROFILL attributeをつけると、0フィルでn桁にしてくれる • INTの引数はZEROFILLオプションがないと無意味（だと思う） • INT以外の他の長さの整数型でも同様に使える • こんな感じ CREATE TABLE `int_table02` ( `id` INT NOT NULL AUTO_INCREMENT, `identifier` INT(9) ZEROFILL NOT NULL, `name` VARCHAR(45) NOT NULL, PRIMARY KEY (`id`)) DEFAULT CHARACTER SET = utf8mb4;

同じことをする mysql> INSERT INTO int_table02 SET identifier=012345678, name="現場猫"; Query OK, 1 row affected (0.00 sec) mysql> SELECT identifier FROM int_table02 WHERE name like "現場猫"; +------------+ | identifier | +------------+ | 012345678 | ←先頭が0で埋められている! +------------+ 1 row in set (0.00 sec)

LPAD関数で0パディングする • SELECTするときにLPAD関数を通して、パディングする • LPAD(パディングする文字列, 出力文字長, 埋める文字) • こんな感じ mysql> SELECT LPAD(identifier,9,0) FROM int_table01 WHERE name like "現場猫"; +----------------------+ | LPAD(identifier,9,0) | +----------------------+ | 012345678 | ←先頭が0で埋められている! +----------------------+ 1 row in set (0.00 sec)

どちらがいいのか • LPADは格納される数字と使う数字に一貫性がないのでいけてない気がする • これをやるくらいなら文字列型の方がだいぶマシに思える • ZEROFILLの方が筋がいいのでは • 将来的に桁が増えたときにALTER TABLEするのを忘れずに

Thank you!