LoginSignup
42
28

More than 5 years have passed since last update.

HameeAdvent Calendar 2017Day 9
@wakuwaku_gattusoinNE株式会社

君の知らないMySQL挙動

Last updated at Posted at 2017-12-08

Hamee Advent Calendar 2017 9日目の記事です。
タイトルのネタが旧いですね。

そろそろ付き合いの長いRDBMSであるMySQLのトラップ豆話を書いてみます。
「あるある」と納得してもらったり、思い出してフフッとしたり、対面して「あっこれかぁ！」って思う時があれば幸いです。
人によっては「私は知ってるMySQL挙動」かもしれませんがご容赦願います。

確認環境

RDBMS：MySQL 5.6.22
今回言及するテーブルのDBエンジン：innoDB

カラムのサイズ定義の意味合い

terminal
mysql > SHOW COLUMNS FROM `number_table`;
+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| id     | int(10) | NO   | PRI | NULL    | auto_increment |
| number | int(5)  | NO   |     | NULL    |                |
+--------+---------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

たとえばこんなテーブル。この整数型intの隣の（10）とか（5）ですが、文字数の最大長の定義ではありません。そしてやはり最大値の定義でもありません。
※例えばint型カラムの運用可能範囲は2147483647〜-2147483648、UNSIGNEDを指定すると0〜4294967295であり、このいずれかになります。

この定義は ZEROFILL（上の桁を0で埋めて文字列長さを固定にする） に対してのみ機能し、設定したサイズにまで上位桁に0を詰めてくれます。

create_number_zerofill_table.sql
CREATE TABLE `number_table` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `number` int(5) UNSIGNED ZEROFILL NOT NULL, -- ZEROFILL指定
  UNIQUE KEY `id` (`id`)
);
terminal
mysql > INSERT INTO `number_table`(`number`) VALUES (1),(10),(100),(1000),(10000);
Query OK, 5 rows affected (0.01 sec)

mysql > SELECT * FROM `number_table`;
+----+--------+
| id | number |
+----+--------+
|  1 |  00001 |
|  2 |  00010 |
|  3 |  00100 |
|  4 |  01000 |
|  5 |  10000 |
+----+--------+
5 rows in set (0.00 sec)

逆に言うとZEROFILL運用しない場合、整数型カラムのサイズ定義には意味がありません。
文字列型のサイズ定義（＝文字数の最大長を表す）と同じノリで設定すると私のように面食らうかも。

なお、ZEROFILLを超える桁の数値が入力された場合は、普通に定義した桁数を越えます。

terminal
mysql > INSERT INTO `number_table`(`number`) VALUES (100000);
Query OK, 1 row affected (0.02 sec)

mysql > SELECT * FROM `number_table`;
+----+--------+
| id | number |
+----+--------+
|  1 |  00001 |
|  2 |  00010 |
|  3 |  00100 |
|  4 |  01000 |
|  5 |  10000 |
|  6 | 100000 | <- ZEROFILL桁を超えている
+----+--------+
6 rows in set (0.00 sec)

整数型に限らず、サイズ定義の意味合いは、データ型ごとに気を配るようにしましょう。

参考：MySQL 5.6 リファレンスマニュアル 11.1.1 数値型の概要

整数型カラムの解釈

たとえばデフォルト値を1にしたNULLを許容しないTINYINTカラムがあるとします。そのカラムに対して空文字やNULLを与えてINSERTするとどうなるでしょうか？

terminal
mysql > SHOW COLUMNS FROM `tinyint_table`;
+---------+------------+------+-----+---------+----------------+
| Field   | Type       | Null | Key | Default | Extra          |
+---------+------------+------+-----+---------+----------------+
| id      | int(10)    | NO   | PRI | NULL    | auto_increment |
| tinyint | tinyint(5) | NO   |     | 1       |                |
+---------+------------+------+-----+---------+----------------+

mysql > INSERT INTO `tinyint_table`(`tinyint`) VALUES (''), (NULL); -- 空文字とNULLを`tinyint`カラムに設定
Query OK, 2 rows affected, 2 warnings (0.02 sec) -- 実はこの時点でwarningが発せられている

mysql > SELECT * FROM `tinyint_table`;
+----+---------+
| id | tinyint |
+----+---------+
|  1 |       0 |
|  2 |       0 |
+----+---------+

上記の通り、このINSERT結果は0になります。「空を入力する時、新規ならデフォルト値入れて欲しい！更新なら無視して欲しい！」って場合にはパラメータを与えて更新するのではなく、きちんとカラム指示自体を除去しなければなりません。

terminal
mysql > SHOW COLUMNS FROM tinyint_table;
+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | int(10)    | NO   | PRI | NULL    | auto_increment |
| tinyint      | tinyint(5) | NO   |     | 1       |                |
| other_column | varchar(4) | NO   |     |         |                |
+--------------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql > INSERT INTO `tinyint_table`(`other_column`) VALUES ('hoge'), ('huga');
Query OK, 2 rows affected (0.02 sec)

mysql > SELECT * FROM `tinyint_table`;
+----+---------+--------------+
| id | tinyint | other_column |
+----+---------+--------------+
|  1 |       1 | hoge         | <- デフォルト値が設定されている
|  2 |       1 | huga         | <- 同上
+----+---------+--------------+
2 rows in set (0.00 sec)

ちなみに、整数型に文字列を与えても同様に0になるのですが、数値を含む文字列を与えると中途半端に解釈して数値が登録される怪現象が出来します。

試してみよう

では、intカラムに手当たり次第にいろんなパラメータを突っ込んでみます。

terminal
mysql > SHOW COLUMNS FROM int_table;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(10) | NO   | PRI | NULL    | auto_increment |
| int   | int(5)  | NO   |     | 1       |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql > INSERT INTO `int_table`(`int`) VALUES
>('hoge'),
>('hoge123'),
>('123hoge'),
>('123hoge456huga789'),
>('１２３456'),
>('123４５６'),
>('1.0E+1'),
>('2.0e+2'),
>('123.456'),
>('123.456.789'),
>('123.456.789.012'),
>('1,234'),
>('1,234,567'),
>('2+4'),
>('5-3'),
>('4*4'),
>('8/2'),
>('5%2'),
>(2+4),
>(5-3),
>(4*4),
>(8/2),
>(5%2);
Query OK, 23 rows affected, 15 warnings (0.01 sec)
Records: 23  Duplicates: 0  Warnings: 15 -- 案の定この時点でwarningが発せられている

mysql > SELECT * FROM `int_table`;
+----+--------+
| id | int    |
+----+--------+
|  1 |      0 | <- 'hoge' ： 先頭が文字列なら0
|  2 |      0 | <- 'hoge123' ： 先頭が文字列なら0
|  3 |    123 | <- '123hoge' ： 文字列が出現するまでの先頭数値だけ解釈する
|  4 |    123 | <- '123hoge456huga789' ： 文字列が出現するまでの先頭数値だけ解釈する
|  5 |      0 | <- '１２３456' ： 全角は文字（先頭が文字列なら0）
|  6 |    123 | <- '123４５６' ： 全角は文字（文字列が出現するまでの先頭数値だけ解釈する）
|  7 |     10 | <- '1.0E+1' ： 指数入力を解釈可能
|  8 |    200 | <- '2.0e+2' ： 指数入力を解釈可能（小文字OK）
|  9 |    123 | <- '123.456' ： 小数点以下を切り捨て
| 10 | 123456 | <- '123.456.789' ： ？ ※
| 11 | 123456 | <- '123.456.789.012' ： ！？ ※
| 12 |      1 | <- '1,234' ： カンマは文字列（文字列が出現するまでの先頭数値だけ解釈する）
| 13 |      1 | <- '1,234,567' ： カンマは文字列（文字列が出現するまでの先頭数値だけ解釈する）
| 14 |      2 | <- '2+4' ： +は文字（文字列が出現するまでの先頭数値だけ解釈する）
| 15 |      5 | <- '5-3' ： -は文字（文字列が出現するまでの先頭数値だけ解釈する）
| 16 |      4 | <- '4*4' ： *は文字（文字列が出現するまでの先頭数値だけ解釈する）
| 17 |      8 | <- '8/2' ： /は文字（文字列が出現するまでの先頭数値だけ解釈する）
| 18 |      5 | <- '5%2' ： %は文字（文字列が出現するまでの先頭数値だけ解釈する）
| 19 |      6 | <- 2+4 ： 非文字列なら演算可能（加算）
| 20 |      2 | <- 5-3 ： 非文字列なら演算可能（減算）
| 21 |     16 | <- 4*4 ： 非文字列なら演算可能（乗算）
| 22 |      4 | <- 8/2 ： 非文字列なら演算可能（除算）
| 23 |      1 | <- 5%2 ： 非文字列なら演算可能（剰余演算）
+----+--------+
23 rows in set (0.00 sec)
  • 文字列の先頭（左）から解釈可能な範囲だけ解釈する
    • カンマは文字列扱いなので、金額表記（カンマ込数値）等をそのまま叩き込むとデータが途切れる
  • 指数表現は解釈可能
  • 演算も文字列に指定してしまうと機能しない

※小数点はちょっと挙動が理解できませんでした。情報求ム

当たり前ですが良い子も悪い子も間違っても文字列型でないカラムに文字列を突っ込む運用を常態化してはいけません。

文字列検索における大文字と小文字の区別

平常のMySQLでは大文字小文字を区別しません。

terminal
mysql > SHOW COLUMNS FROM `key_table`;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int(10)     | NO   | PRI | NULL    | auto_increment |
| key          | varchar(10) | NO   | UNI |         |                |
| other_column | varchar(10) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql > SELECT * FROM `key_table`;
+----+-----+--------------+
| id | key | other_column |
+----+-----+--------------+
|  1 | AAA | NULL         |
|  2 | bbb | NULL         |
|  3 | CcC | NULL         |
|  4 | dDd | NULL         |
+----+-----+--------------+
4 rows in set (0.00 sec)

mysql > SELECT * FROM `key_table` WHERE `key` = 'aaa'; -- 大文字レコードを小文字で検索
+----+-----+--------------+
| id | key | other_column |
+----+-----+--------------+
|  1 | AAA | NULL         | <- hitしている
+----+-----+--------------+
1 row in set (0.00 sec)

mysql > SELECT * FROM `key_table` WHERE `key` IN ('aaa', 'BBB', 'cCc', 'DdD'); -- 大文字小文字を反転した条件で検索
+----+-----+--------------+
| id | key | other_column |
+----+-----+--------------+
|  1 | AAA | NULL         |
|  2 | bbb | NULL         |
|  3 | CcC | NULL         |
|  4 | dDd | NULL         |
+----+-----+--------------+
↑すべてhitしている
4 rows in set (0.00 sec)

mysql > INSERT INTO `key_table` (`key`, `other_column`) VALUES
>('aaa', 'insert'),
>('BBB', 'insert'),
>('cCc', 'insert'),
>('DdD', 'insert')
>ON DUPLICATE KEY UPDATE `other_column`='update'; -- 登録したら'insert'、更新したら'update'
Query OK, 8 rows affected (0.00 sec)
Records: 4  Duplicates: 4  Warnings: 0

mysql > SELECT * FROM `key_table`;
+----+-----+--------------+
| id | key | other_column |
+----+-----+--------------+
|  1 | AAA | update       |
|  2 | bbb | update       |
|  3 | CcC | update       |
|  4 | dDd | update       |
+----+-----+--------------+
↑すべて更新されている
4 rows in set (0.00 sec)

上記のように、小文字で検索しても大文字レコードがhitしますし、逆も然りです。

実は、意図的に大文字小文字を区別して検索したり、指定したカラムでは大文字小文字を区別するように設定することができます。

大文字と小文字を区別する検索の実行

terminal
mysql > SELECT * FROM `key_table` WHERE `key` = BINARY'aaa';
Empty set (0.00 sec) -- hitしない

mysql > SELECT * FROM `key_table` WHERE `key` = BINARY'AAA'; -- 検索パラメータ前にBINARYを設定
+----+-----+--------------+
| id | key | other_column |
+----+-----+--------------+
|  1 | AAA | update       |
+----+-----+--------------+
1 row in set (0.00 sec)

mysql > SELECT * FROM `key_table` WHERE `key` IN (BINARY'aaa', BINARY'BBB', BINARY'cCc', BINARY'DdD'); -- 各検索パラメータ前にBINARYをせってい
Empty set (0.00 sec) -- hitしない

mysql > SELECT * FROM `key_table` WHERE `key` IN (BINARY'AAA', BINARY'bbb', BINARY'CcC', BINARY'dDd');
+----+-----+--------------+
| id | key | other_column |
+----+-----+--------------+
|  1 | AAA | update       |
|  2 | bbb | update       |
|  3 | CcC | update       |
|  4 | dDd | update       |
+----+-----+--------------+
4 rows in set (0.01 sec)

カラムに大文字小文字を区別するよう設定する

terminal
mysql > ALTER TABLE `key_table` MODIFY `key` VARCHAR(10) BINARY; -- 大文字小文字を区別するようカラムを設定変更
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql > SELECT * FROM `key_table` WHERE `key` = 'aaa'; -- 通常のWHERE検索
Empty set (0.00 sec) -- hitしない

mysql > SELECT * FROM `key_table` WHERE `key` = 'AAA';
+----+------+--------------+
| id | key  | other_column |
+----+------+--------------+
|  1 | AAA  | update       |
+----+------+--------------+
1 row in set (0.01 sec)

mysql > SELECT * FROM `key_table` WHERE `key` IN ('aaa', 'BBB', 'cCc', 'DdD'); -- 通常のWHERE検索
Empty set (0.00 sec) -- hitしない

mysql > SELECT * FROM `key_table` WHERE `key` IN ('AAA', 'bbb', 'CcC', 'dDd');
+----+------+--------------+
| id | key  | other_column |
+----+------+--------------+
|  1 | AAA  | update       |
|  2 | bbb  | update       |
|  3 | CcC  | update       |
|  4 | dDd  | update       |
+----+------+--------------+
4 rows in set (0.00 sec)

mysql > INSERT INTO `key_table` (`key`, `other_column`) VALUES
>('aaa', 'insert'),
>('BBB', 'insert'),
>('cCc', 'insert'),
>('DdD', 'insert')
>ON DUPLICATE KEY UPDATE `other_column`='update'; -- 登録したら'insert'、更新したら'update'
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql > SELECT * FROM `key_table`;
+----+------+--------------+
| id | key  | other_column |
+----+------+--------------+
|  1 | AAA  | update       |
|  2 | bbb  | update       |
|  3 | CcC  | update       |
|  4 | dDd  | update       |
|  5 | aaa  | insert       |
|  6 | BBB  | insert       |
|  7 | cCc  | insert       |
|  8 | DdD  | insert       |
+----+------+--------------+
↑カラムが大文字小文字を区別する状態なら、ユニークキーが区別されINSERTされる
8 rows in set (0.00 sec)

この大文字小文字の分別の条件がクエリとクエリ発行側の処理で入り乱れると、いとも簡単に地獄の釜が開くことが想像できますね。使う場合も、使わない場合でも気をつけましょう。

（ところで、「大文字小文字を区別する」というケースを一般にはcase-sensitiveと呼称するそうですね。恥ずかしながら最近知りました）

まとめ

実際は「えっ！？」って思った挙動の個人的まとめ備忘録なんですが、こうしてまとめてみると、DBとクエリ発行側の処理の住み分けって改めて大事だなぁと。

どこまでをDB側が処理を請け負うのか？
どこまでの指示をクエリやサーバロジックが行うべきなのか？

説明したケースのように、なまじDBがデータを調整してくれることは少なくありませんから、それぞれのRDBMSの特徴と機能を抑えて、一貫性を保たせつつ、明確に切り分けていく。これからでも気を配っていきたいです。

おわり

innoDB「8kbの壁」やANALYZEとOPTIMIZE、登録&更新日時の自動設定の話も入れる予定だったのですが、一人で盛り上がって一通り試してたら変な文量になってしまったので、また別の機会にします。
「待て待て勘違いしてる」「実はこういう機能もあるよ」といったご意見等ありましたらぜひお願い致します。

RDBという思想と概念が続く限りは自分にとって手放せないので、引き続き愛着持って付き合っていきたいと思います。
ありがとうございました。

明日はHamee Advent Calendar 2017 10日目、 asflash8さんの記事です。

42
28
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
42
28