DBAといえば私も OracleMaster （ありがとう）
オンプレ時代から長くOracle製品を愛用していたエンジニアです。
自宅に私用ライセンスを保有しており家計簿に愛用（無駄
とはいえ気がつけばAWSの渦中にはまり込んだ 5-6年前から
クラウド環境になり MySQLを利用する頻度が高くなっておりました。
終活をする中で当時の移行検討をしていた資料を振り返ったところ
かなり草なことが判明したので投稿。
なんとなく文字でマッピングすると駄目な罠
OracleDB側のテーブル定義
CREATE TABLE EMPLOYEE
ID VARCHAR2(256),
NAME VARCHAR2(256),
H_M_B INTEGER(5)
);
※ HMB・・How many breads have you eaten in your life? の略
MySQL側のテーブル定義
CREATE TABLE EMPLOYEE
ID VARCHAR(256),
NAME VARCHAR(256),
H_M_B INT(5)
);
現行システムでは今までに食べたパンの枚数を最大 99,999枚まで記憶できます。
1日1枚、1年間で365枚、80年間生存したとして29,200枚の換算になります。
朝昼晩食べたら 87,600枚。いやこれだけあれば十分でしょう。なので 5桁。
クラウドに移行してから運転しているとコロナ禍のため食パンの消費速度が
急上昇している弊社従業員を確認しました。
彼女はサンドイッチに恋していることから毎食 2枚ずつ具材を挟んで食事を
しています。結論 175,200枚。
安易な計算で桁幅を決めてしまった設計者に魔の手が、笑。
しかし安心してください。桁幅は５桁ですから。
DBMS が違えば食べられる枚数は違う
現行システムでは食パンを 36分割してラスクにして計上してしまった場合でも
DBMS側で「もう食べられないよ / それはパンじゃない」などエラーを発砲。
手抜きエンジニアのシステムでは画面上ではHTMLのMAXLENGTHがあるから
開発者ツールで不適切な加工をしなければ基本は大丈夫。
もしHTMLを変更されたとしてもDBMSでエラーを返却するので大丈夫と過信。
そうです。過信です。それは Oracle先生の化身。
MySQLでは INT(5) の場合には 200万枚ほど食パンを食べられるのです。
気がつけば桁あふれには気が付かない自分をよそに
食パン業者さんから御社はパンを食べすぎのため取引できないとクレーム。
え。カッコの中の数字ってなんだ草
> INSERT INTO EMPLOYEE (ID, NAME, H_M_B) VALUES ('YAMADA', 'Taro YAMADA', 175200);
Query OK, 1 row affected (0.00 sec)
-- なんとエラーにならない
> SELECT * FROM EMPLOYEE;
+----------+-------------+----------+
| ID | NAME | H_M_B |
+----------+-------------+----------+
| YAMADA | Taro YAMADA | 175200 |
+----------+-------------+----------+
INT(5) とはどこにｗ そうなのです。INT(5)のカッコの中はダミーです。駄目。
ちなみに。
参考）MySQLのデータ型が取りうるパンの枚数。
tinyint：
符号付きの場合；-128 ～ 127
符号なしの場合；0 ～ 255
smallint：
符号付きの場合；-32,768 ～ 32,767
符号なしの場合；0 ～ 65,535
mediumint：
符号付きの場合；-8,388,608 ～ 8,388,607
符号なしの場合；0 ～ 16,777,215
int:
符号付きの場合；-2,147,483,648 ～ 2,147,483,647
符号なしの場合；0 ～ 4,294,967,295
bigint：
符号付きの場合；-9,223,372,036,854,775,808 ～ 9,223,372,036,854,775,807
符号なしの場合；0 ～ 18,446,744,073,709,551,615
MySQL のカッコの中の数字は肌年齢。
そのまま INT(5) を定義すると差異がないため、以下でテーブルを作成してみます。
CREATE TABLE EMPLOYEE
ID INT(5),
NAME VARCHAR(256),
H_M_B INT(5) ZEROFILL -- ★ 重要
);
ZEROFILL。ゼロ埋め。そうです。端数を切り捨てて年齢を報告するのと同様（違う
INT型に設定できるのは表示書式です。データの桁幅ではない（まじか
> desc EMPLOYEE
+------------+---------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------------------+------+-----+---------+----------------+
| ID | int(5) | YES | | NULL | |
| NAME | varchar(256) | YES | | NULL | |
| H_M_B | int(5) unsigned zerofill | YES | | NULL | |
+------------+---------------------------+------+-----+---------+----------------+
> INSERT INTO EMPLOYEE (ID, NAME, H_M_B) VALUES ('29', 'Kin29-man', 29);
Query OK, 1 row affected (0.00 sec)
> SELECT * FROM EMPLOYEE;
+----------+-----------+----------+
| ID | NAME | H_M_B |
+----------+-----------+----------+
| 29 | kin29-man | 00029 |
+----------+-----------+----------+
なんと。登録したのは29なのにSELECTしたら先頭に「0」ついてきたｗ
いらなくね草
肌年齢を利用する方法 （非推奨
-- いつも実行するのはこっち
> SELECT ID, NAME, LPAD(H_M_B, 3, '0') As H_M_B FROM EMPLOYEE;
+----------+-----------+----------+
| ID | NAME | H_M_B |
+----------+-----------+----------+
| 29 | kin29-man | 029 |
+----------+-----------+----------+
※ちょっと 3桁にしてみた
ちなみに MySQL 8.0.17 では、整数データ型の表示幅属性と同様に
数値データ型の ZEROFILL 属性は非推奨（ ですよね ）
こんな謎仕様で他DBMSから流れてきたエンジニアを惑わす機能不要に賛成。
MySQL で食べられる枚数をDBMSで制限するには
正論；INT ではなく NUMERIC（もしくはDECIMAL）を利用してください
文字だけ見るとNUMERICは整数だけ、DECIMALは小数点以下がありそうにみえるけど
両方とも同じです。内部的には同じデータ型。どちらを利用するかは趣味嗜好の範囲。
NUMERICでも小数点は指定できるし、DECIMALでも整数値だけを宣言可能です。
まとめ
似ているからと言って適当に移行すると肌年齢が悪化してからいいことがないので反省。
そして NUMERIC とDECIMALとか見た目で設定をすると駄目。差異ないし。
あとで後悔するぞっ！って数年前の自分に伝言したいのでひっそりと公開。