如何根据列顺序添加自动递增主键? [英] How to add auto increment primary key based on an order of column?

问题描述

我需要将一个自动增量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.

推荐答案

1. 添加新字段-ALTER TABLE table_name ADD column_name INT FIRTS;
2. 在新字段中填充数据.

3. 将此字段作为主键的一部分，并添加AUTO_INCREMENT选项-

1. Add a new field - ALTER TABLE table_name ADD column_name INT FIRTS;
2. Populate data in the new field.

3. 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);

尝试这个计划:

1. 创建另一个与table_name相同的表.

2. 使用来自table_name表的排序数据填充新表:

1. Create another table, the same as table_name.

2. 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;

这篇关于如何根据列顺序添加自动递增主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！