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;