我需要将一个自动增量ID添加到一个已经存在的表中.我做到了:
I need to add an auto increment id to an already existing table. I did:
ALTER TABLE table_name ADD column_name INT NOT NULL AUTO_INCREMENT FIRST , ADD PRIMARY KEY (column_name)但是,没有根据任何特定的列顺序进行自动编号.我希望mysql根据某些列的顺序巧妙地放置自动编号.有可能吗?
However, the auto numbering wasnt based on any particular column order. I want mysql to smartly put auto numbers based on the order of certain column. Is it possible?
那里的大多数答案都告诉您如何添加自动递增字段,而不是如何控制已经存在的表中的数字.
Most of the answers out there tell you how to add auto increment fields, not how to control those numbers in already existing table.
推荐答案
将此字段作为主键的一部分,并添加AUTO_INCREMENT选项-
Make this field as part of primary key and add AUTO_INCREMENT option -
ALTER TABLE表名称 CHANGE COLUMN ID ID INT(11)NOT NULL AUTO_INCREMENT, 添加主键(id);
ALTER TABLE table_name CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (id);
尝试这个计划:
使用来自table_name表的排序数据填充新表:
Populate new table with sorted data from table_name table:
INSERT INTO temp_table_name SELECT * FROM table_name ORDER BY名称;
INSERT INTO temp_table_name SELECT * FROM table_name ORDER BY name;
空table_name表:
TRUNCATE TABLE table_name;
TRUNCATE TABLE table_name;
更改table_name表,添加新的自动递增字段并将其设置为主要字段(您的代码).
Alter table_name table, add new auto increment field and make it primary (your code).
从临时复制数据.表到table_name:
Copy data from temp. table to the table_name:
INSERT INTO table_name SELECT NULL,t.* FROM temp_table_name t;
INSERT INTO table_name SELECT NULL, t.* FROM temp_table_name t;
更多推荐
如何根据列顺序添加自动递增主键?
发布评论