如何在唯一价值上自动增值

Question

假设我在mysql工作台和Innodb引擎中拥有这个表，其中两个列都是主键：

+--------+---------+
| grp    | name    |
+--------+---------+
| fish   | lax     |
| mammal | dog     |
| mammal | bat     |
| mammal | whale   |
| bird   | bat     |
| bird   | ostrich |
+--------+---------+

我怎样才能在grp列中添加这样的列：

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  2 | dog     |
| mammal |  2 | cat     |
| mammal |  2 | whale   |
| bird   |  3 | penguin |
| bird   |  3 | ostrich |
+--------+----+---------+

请注意，我要修改的实际表要大得多。另外，请注意，这不是使用MyISAM自动递增的功能，也不是这里询问的内容(但答案可能会有所帮助)：How to auto increment on different foreign keys?

Answer 1

CREATE TABLE Groups (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    grp VARCHAR...
    PRIMARY KEY(id),
    UNIQUE(grp)
        ) ENGINE=InnoDB
    SELECT DISTINCT grp FROM YourTable;

这将为您提供grp和id之间的映射。

此时，您不应该生成一个包含3列(grp+id+name)的表，而是使用上面的Groups作为“规范化”，这是您似乎正在做的事情。那就去做

CREATE TABLE New LIKE YourTable;
ALTER TABLE New
    ADD  COLUMN id INT UNSIGNED NOT NULL,
    DROP COLUMN grp;
# Note: if you have an index on grp, something else may need to be done.
INSERT INTO New
    SELECT g.id, y.name
        FROM Groups g
        JOIN YourTable y  ON y.grp = g.grp;
# At this point, verify that `New` has what you want.  Then switch over:
RENAME TABLE YourTable TO Old, New TO YourTable;
DROP TABLE Old;

编辑 --我选择创建一个新表，而不是更新现有表--因为它会(我认为)要快得多。使用UPDATE，所有行都需要被锁定。使用INSERT..SELECT，可以简单地创建它们。