MySQL ALTER AUTO_INCREMENT （追加）

目的

今存在してるMySQLのテーブルにAUTO_INCREMENT 設定ができてないので、ALTER を使って追加する。

やり方

ALTER TABLE テーブル名 ADD PRIMARY KEY (カラム名);

ALTER TABLE テーブル名 CHANGE カラム名 カラム名 データ型 AUTO_INCREMENT;

参考

• カラム名 カラム名  って指定しないといけないんですなー。。。
• データ型も指定しないといけないのか
  • 間違って意図せずデータ型変更しないように注意
  • NOT NULLもつけないと消える？

以下のようなエラーで怒られました。キー設定がされていないとダメなんですね。

上述のコマンドのとおり、AUTO_INCREMENTを付ける前に KEY を設定しないとアカン。

※試したところ、ユニークキーでもAUTO_INCREMENTは使えます。

AUTO_INCREMENT 最初の値に0（ゼロ）は指定できるのか？

今、仕事でOracleからMySQLへデータを移行して最中なんだが、Oracleだと、KEYの値が0から始まってる。揃えられるか？

最初に結論を言ってしまうとauto_increment属性のカラムには0(ゼロ)は使えません。

auto_increment属性のカラムは、
そのカラムに０かnullをインサートしようとした時に自動採番した数値をカラムに代入するから

はてぶ

なん…じゃと…orz

なんだけど、設定で例外にできる↓

ＳＱＬモード変数で、NO_AUTO_VALUE_ON_ZEROモードを有効にすると、この挙動が少しだけ変わり、nullをインサートしようとした時のみ自動採番するようになります。この場合は、０もデータとして利用できるようになります。

教えてgoo

なんだけど、

auto_incrementは、値の数字そのものには全く興味が無いがとりあえず、重複しない値が欲しいという時に使います。

むぅ。。。そうなんだがなるべく移行前後で同じデータにしたいんだよなー。。。

ということで、NO_AUTO_VALUE_ON_ZEROについて調べる。まず、公式サイトに書かれてる意味↓

NO_AUTO_VALUE_ON_ZERO は AUTO_INCREMENT カラムの処理に影響します。通常は、NULL または 0 をカラムに挿入することによって、カラムの次のシーケンス番号を生成します。NO_AUTO_VALUE_ON_ZERO は 0 のこの動作を抑制するため、NULL のみが次のシーケンス番号を生成します。

このモードは、テーブルの AUTO_INCREMENT カラムに 0 が格納されている場合に便利なことがあります。(ただし、0 を格納することは、推奨される方法ではありません。)たとえば、mysqldump でテーブルをダンプして、テーブルをリロードする場合、MySQL は通常、0 という値を検出すると、新たなシーケンス番号を生成するため、その結果、ダンプされたものとは異なる内容を持つテーブルになります。ダンプファイルをリロードする前に NO_AUTO_VALUE_ON_ZERO を有効にすると、この問題が解決されます。この問題を防ぐために、mysqldump には現在、NO_AUTO_VALUE_ON_ZERO を有効にするステートメントがその出力に自動的に含まれるようになりました。

MySQL公式

公式でも0を許可することは非推奨なんですな…。

しかしmysqldump したときには自動的に有効になる（つまり0があった場合、ちゃんと0がDUMPされる）みたいですな。

mysqldumpしたファイル確認してみる…

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE=’NO_AUTO_VALUE_ON_ZERO’ */;

このへんになるのか？

qiitaを見てみると、そうっぽい。コメント部分と思いきや、ちゃんと設定される動きをするんですな。冒頭の数字「!40101」は適用されるMySQLのバージョンぽいですな。↓

MySQLバージョン4.1.1.1以上のサーバーにでないと実行されないという意味になる。

NO_AUTO_VALUE_ON_ZEROの設定の仕方

mysql> SET GLOBAL sql_mode = ‘NO_AUTO_VALUE_ON_ZERO’;

参考

とすればいいらしいが、、

SET GLOBALを利用すると，サーバーが終了した場合には変更したシステム変数は元の設定に戻ってしまいますが，SET PERSISTを利用するとリセットされず，

gihyo

のとおり、恒久的な変更にならない。

恒久的な変更をするには、

SET PERSIST

を使うか、（MySQL8.0以降）my.cnfに記述する必要がある。

現在、0をINSERTできるか確認する

mysql> SHOW VARIABLES LIKE 'sql_mode';

参考

上記のコマンドで設定を確認し、NO_AUTO_VALUE_ON_ZERO　が在れば、0をINSERTできる。

自分の環境で確認してみる‥

+---------------+-----------------------------------------------------------------------------------------------------------------------+
| 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.10 sec)

NO_AUTO_VALUE_ON_ZERO　が無いので、設定されてない模様。

AUTO_INCREMENTの最初の値変更

MySQLのAUTO_INCREMENTのカラムの開始の値を変更したいです。

以下のSQLでできる。

ALTER TABLE xxxx AUTO_INCREMENT=100;

参考

1 以外の AUTO_INCREMENT 値で開始するには、次のように、その値を CREATE TABLE または ALTER TABLE でセットします。

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

MySQL公式