mysql 序号字段_MySQL快速生成带序号的sql及字段

一、第一种方式：

set @rowNO = 0;

set @rowNO2 = 0;

set @rowNO3 = 0;

set @rowNO4 = 0;

select CONCAT("INSERT INTO `user` VALUES (","'",100000000+@rowNO:=@rowNO+1,"'",",","'", 20000000000+@rowNO2:=@rowNO4+1,"@test-d",

"'",",","'", "s",@rowNO3:=@rowNO3+1,"'",",","'", 20000000000+@rowNO4:=@rowNO4+1,"'",",","'", "e10adc3949ba59abbe56e057f20f883e",

"'", ",","'", "t","'",",","'", "2016-09-08 17:07:52","'", ",","'", "2016-09-08 17:07:52","'",",","'", "1","'", ",","'", "token",

"'", ",","'", "0","'",",","'", "52","'",",","'", "0","'",",", "null",",","'", "0","'",");") from tig_users where @rowNO <=10000

set @rowNO = 0;

set @rowNO2 = 0;

set @rowNO3 = 0;

set @rowNO4 = 0;

set @rowNO5 = 0;

select CONCAT("INSERT INTO `cssdb`.`b_person_info` (`BUID`, `BOID`, `NAME`, `IDCODE`, `GENDER`, `PHONE`, `EMAIL`, `PASSWD`, `QQ`,

`CREATE_TIME`, `UPDATE_TIME`, `IMUSER`, `FAILNUM`, `LOCKSTATUS`, `LOCKTIME`) VALUES (","'",2000000000+@rowNO:=@rowNO+1,"'",",","NULL",

",","'", 20000000000+@rowNO2:=@rowNO2+1,"@test-d","'",",","NULL",",","'", "0","'",",", "'", 20000000000+@rowNO4:=@rowNO4+1,"'",",",

"NULL",",", "'", "670b14728ad9902aecba32e22fa4f6bd","'", ",","NULL",",","'", "2016-09-08 17:07:52","'", ",","'", "2016-09-08 17:07:52",

"'",",","'", 20000000000+@rowNO5:=@rowNO5+1,"@test-d","'",",","'", "0","'", ",","'", "0","'", ",", "NULL",");") from b_certification_log

where @rowNO <=10000

二、第二种方式：

select CONCAT(substring_index(jid,'@test-d',1),'##',substring_index(jid,'@test-d',1)+1,'##',substring_index(room_jid,'@muc.test-d',1)) from tig_muc_members where id BETWEEN 389 and 494 GROUP BY room_jid order by jid;

三、第三种方式：

select CONCAT(substring_index(jid,'@test-d',1),'##',substring_index(room_jid,'@muc.test-d',1)) from tig_muc_members where id BETWEEN 798 and 9138 order by jid;