👌

MySQLでAUTO_INCREMENTが指定されているカラムに「0」を入れるには

2021/07/05に公開

MySQL

tech

はじめに

MySQLでAUTO_INCREMENTが指定されているカラムに「0」が入っているケースに遭遇しました。
0は入れれないと勝手に思っていたので、デフォルトがどうなっているのか、どう設定したら入れれるのか調べてみました。

デフォルトの挙動について

適当なテーブルを作って、試してみます。
MySQLのバージョンは8.0です。

mysql> SELECT * FROM drinks;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | coffee |   500 |
|  2 | tea    |   400 |
|  3 | juice  |   600 |
+----+--------+-------+
3 rows in set (0.00 sec)

id=0を指定して挿入してみます。

mysql> INSERT INTO drinks VALUES(0, "water", 0);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM drinks;
+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | coffee |   500 |
|  2 | tea    |   400 |
|  3 | juice  |   600 |
|  4 | water  |     0 |
+----+--------+-------+
4 rows in set (0.00 sec)

idに0を指定したにも関わらず連番の4が振られています。
この挙動については、MySQL 8.0のマニュアルに下記のように載っています。

Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it.

デフォルトでは、AUTO_INCREMENTが指定されているカラムに、0（もしくはNULL）の値を挿入しようとすると、自動的に、そのカラムが持っている最大の値に「+1」された値が挿入されます。

(Storing 0 is not a recommended practice, by the way.)

AUTO_INCREMENTが指定されているカラムに0を使うことは非推奨のようです。

ちなみに、MySQL 5.7も確認したところ、同じ仕様でした。

0を挿入するには

NO_AUTO_VALUE_ON_ZEROを有効にするとAUTO_INCREMENTが指定されているカラムに、0を挿入することができます。

下記のコマンドでグローバルな設定をすることができます。

mysql> SET GLOBAL sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

なお、NO_AUTO_VALUE_ON_ZEROは「0」のみ許可し「NULL」は対象外です。

その他

調べている中で使えそうだなと思ったことをまとめておきます。

sql_modeを確認するコマンド

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Dockerでsql_modeを指定

my.cnfに下記の設定を追加します。

my.cnf

[mysqld]
sql_mode=NO_AUTO_VALUE_ON_ZERO, ......