移転しました。

MySQLでプライマリキーを変更して現在のデータに連番を与える

久し振りに長いタイトルになった。
例えば、もともとのテーブルがこんな感じだったとして、

mysql> desc foo;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| bar   | varchar(10) | NO   | PRI |         |       |
| baz   | varchar(10) | NO   | PRI |         |       |
+-------+-------------+------+-----+---------+-------+

あとから、barとbazの両方が重複するデータを入れたくなった場合など。
まずは、プライマリーキーを削除する。

mysql> alter table foo drop primary key;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

普通にプライマリーキーを貼る。

mysql> alter table foo add column id int unsigned not null auto_increment primary key first;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

これだけで連番を与えてくれることに、少し驚いた。良かれにやってくれるのね。定義とデータはこんな感じ。

mysql> desc foo;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| bar   | varchar(10)      | NO   |     |         |                |
| baz   | varchar(10)      | NO   |     |         |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from foo;
+----+-----+-----+
| id | bar | baz |
+----+-----+-----+
|  1 | abc | efg |
|  2 | hij | klm |
|  3 | nop | qrs |
+----+-----+-----+
3 rows in set (0.00 sec)

これができるとうれしい、という実際のタイミングが思いつかないけれども、auto_incrementにしつつ連番を与えつつ複数カラムのプライマリキーにもしたい場合は、カンマ区切りで同時に書ける。

mysql> alter table foo add column id int unsigned not null auto_increment first, add primary key (id, bar, baz);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

定義とデータはこんな感じ。

mysql> desc foo;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| bar   | varchar(10)      | NO   | PRI |         |                |
| baz   | varchar(10)      | NO   | PRI |         |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from foo;
+----+-----+-----+
| id | bar | baz |
+----+-----+-----+
|  1 | abc | efg |
|  2 | hij | klm |
|  3 | nop | qrs |
+----+-----+-----+
3 rows in set (0.00 sec)

参考: MySQL :: MySQL 5.1 リファレンスマニュアル :: 12.1.2 ALTER TABLE 構文