PRIMARY KEYがついているカラムをAUTO_INCREMENTに

ある、hoge_tb というテーブルをつくって、しばらく運用してしまった後に、そのカラム ID をAUTO_INCREMENTにしたくなったときにどうしたらよいか、という話。

といあえず思いついた2パターンを試してみる。

mysql> ALTER TABLE hoge_tb CHANGE id INT(11) NOT NULL AUTO_INCREMENT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT(11) NOT NULL AUTO_INCREMENT' at line 1

mysql> ALTER TABLE hoge_tb CHANGE id id INT(11) NOT NULL AUTO_INCREMENT;
ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

ダメだ、、、。

調べてみると、どうやら sql_mode をいじればよいとのこと。

mysql> SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';
mysql> ALTER TABLE hoge_tb CHANGE id id INT(11) NOT NULL AUTO_INCREMENT;
mysql> SET SESSION sql_mode='';

ちなみに、その後大体テストをすると思うのだが、それはすなわち AUTO_INCREMENT のインデックスが進んでしまうということ。 インデックスをリセットするためには、

ALTER TABLE hoge_tb AUTO_INCREMENT=1000;

とかすればよい。

参考： MySqlでauto_increment を追加しようとしたら、エラー！！ | Webridge Tech Blog