MySQLの連番（データ番号）を1から振り直すには

PHPエラーの原因と対策
26 view

MySQLの連番（データ番号）を1から振り直すには

テーブルの既存データに割り当てられた連番（id列の値など）を、1から振り直したい場合は、どうすればよいでしょうか。たとえば、次のようにすきまのあるid列を整理して「1, 2, 3, 4」という値をもう一度割り当てたい場合です。

mysql> SELECT * FROM renum;
+----+----------+
| id | name     |
+----+----------+
|  2 | 山下太郎 |
|  5 | 田中一郎 |
|  7 | 鈴木史郎 |
|  9 | 小林次郎 |
+----+----------+
4 rows in set (0.00 sec)

もちろん、通常の運用では、主キーのidを振り直すと問題になることもあります。しかし、テスト用など、さまざまな事情からidを振り直したいこともあるでしょう。そこで、連番を振り直す方法を紹介します。

それは、テーブルをコピーして新たな連番を持つテーブルを作成する方法です。MySQLでは、変数を利用することで規則的な値を生成することができるので、次のnew_id列のように連番を生成できます。

mysql> SET @a = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id, @a := @a + 1 AS new_id, name FROM renum;
+----+--------+----------+
| id | new_id | name     |
+----+--------+----------+
|  2 |      1 | 山下太郎 |
|  5 |      2 | 田中一郎 |
|  7 |      3 | 鈴木史郎 |
|  9 |      4 | 小林次郎 |
+----+--------+----------+
4 rows in set (0.00 sec)

　このnew_id列を利用すれば、idが1から始まるように整理できます。

　まず、この内容からnew_id列とname列を取り出して、一時作業用のrenum2テーブルを作成し、内容を確認します。

mysql> CREATE TABLE renum2
    ->   AS SELECT @a := @a + 1 AS new_id, name FROM renum;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM renum2;
+--------+----------+
| new_id | name     |
+--------+----------+
|      1 | 山下太郎 |
|      2 | 田中一郎 |
|      3 | 鈴木史郎 |
|      4 | 小林次郎 |
+--------+----------+
4 rows in set (0.00 sec)

　作成したいデータのイメージに合っていることを確認できたので、元のrenumテーブルを空にして、renum2テーブルのデータをコピーします。

mysql> TRUNCATE TABLE renum;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO renum SELECT * FROM renum2;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

　id列にすきまのあったrenumテーブルをもう一度参照すると、id列が1から振り直されています。

mysql> SELECT * FROM renum;
+----+----------+
| id | name     |
+----+----------+
|  1 | 山下太郎 |
|  2 | 田中一郎 |
|  3 | 鈴木史郎 |
|  4 | 小林次郎 |
+----+----------+
4 rows in set (0.00 sec)

（このWebサイトの作者、西沢直木著の『SQL逆引きレシピ (PROGRAMMER’S RECiPE)』より抜粋）