19 lines
647 B
SQL
19 lines
647 B
SQL
# 第一步 新增new_id字段
|
||
ALTER TABLE channel_data ADD COLUMN new_id INT NOT NULL FIRST;
|
||
|
||
# 第二步 给new_id赋值
|
||
SET @row_num = 0;
|
||
UPDATE channel_data SET new_id = (@row_num := @row_num + 1);
|
||
|
||
# 第三步 删除原主键，将new_id设为主键
|
||
ALTER TABLE channel_data DROP PRIMARY KEY, ADD PRIMARY KEY (new_id);
|
||
|
||
# 第四步 将new_id设置为自增
|
||
ALTER TABLE channel_data MODIFY COLUMN new_id INT NOT NULL AUTO_INCREMENT;
|
||
|
||
# 第五步 删除原data_id字段
|
||
ALTER TABLE channel_data DROP COLUMN data_id;
|
||
|
||
# 第六步 重命名new_id字段为data_id
|
||
ALTER TABLE channel_data CHANGE COLUMN new_id data_id INT NOT NULL AUTO_INCREMENT;
|