mysql 忘记添加主键_mysql-给没有主键的表添加主键步逐

一、知识点，类似rownum的查询语句

SELECT @rownum:=@rownum+1 rownum,s.* FROM (SELECT @rownum:=0) r,t_deal_sum s

1、创建表

CREATE TABLE `t_deal_sum` (

`sp_id` varchar(20) DEFAULT NULL,

`sp_code` varchar(20) DEFAULT NULL,

`sp_name` varchar(100) DEFAULT NULL,

`user_id` varchar(20) DEFAULT NULL,

`user_name` varchar(100) DEFAULT NULL,

`create_time` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、插入数据(略)

3、添加字段：Fid

ALTER TABLE t_deal_sum ADD Fid BIGINT;

4、插入数据

INSERT INTO t_deal_sum (

Fid,

sp_id,

sp_code,

sp_name,

user_id,

user_name,

create_time

)

SELECT @rownum:=@rownum+1 Fid,

sp_id,

sp_code,

sp_name,

user_id,

user_name,

create_time

FROM (SELECT @rownum:=0) r,t_deal_sum s

5、删除Fid为空的数据

DELETE FROM t_deal_sum WHERE Fid IS NULL

6、将Fid字段设置为主键

ALTER TABLE `t_deal_sum` ADD PRIMARY KEY(`Fid`);

7、将Fid转化为自增列

ALTER TABLE `t_deal_sum` CHANGE `Fid` `Fid` BIGINT NOT NULL AUTO_INCREMENT;