mysql添加序号

一.mysql查询结果添加序列号(转)

第一种方法：

select   (@i:=@i+1)   as   i,table_name.*   from   table_name,(select   @i:=0)   as   it 

第二种方法：

set @rownum=0;
select @rownum:=@rownum+1 as rownum, t.username from auth_user t limit 1,5;

二.mysql为表添加序列号字段(个人实验)

如为publish表中publishid大于40的行重新设置序号(order字段)

set @rownum=0;

update publish set `order`= @rownum:=@rownum+1 where `publishid`>40

 

测试会不会影响sql查询性能

explain select (@i:=@i+1) as i, tblMybook.id from tblMybook,(select @i:=0) as it where subject=1 having i%40000=0;
分析结果可以用到索引subject

——实验1 只添加序号—————结果:成功———————
select (@i:=@i+1) as i,tblMybook.id from tblMybook,(select @i:=0) as it where 1 ;
i id
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
——实验2 在having中对序号进行过滤——————结果:好像在having的时候,@i又参与计算了,如下—————
select (@i:=@i+1) as i,tblMybook.id from tblMybook,(select @i:=0) as it where 1 having i%2=0;
i id
3 2
5 3
7 4
9 5
11 6
13 7
15 8
—实验3 直接在having中对序号进行计算和过滤——————结果:完美—————
select @i as i,tblMybook.id from tblMybook,(select @i:=0) as it where 1 having (@i:=@i+1) %2=0;
i id
2 2
4 4
6 6
8 8

select tblMybook.id from tblMybook,(select @i:=0) as it where 1 having (@i:=@i+1) %2=0;
id
2
4
6
8

select @i as i,(@j:=@j+1) as j,tblMybook.id from tblMybook,(select @i:=0) as it,(select @j:=0) as it2 where 1 having (@i:=@i+1) %2=0;
i j id
2 1 2
4 2 4
6 3 6
8 4 8

 

—实验4 直接在where中过滤——————结果:完美—————
select @i as i,tblActBroadcastGroupInfo.id from tblActBroadcastGroupInfo,(select @i:=0) as it where 1 and (@i:=@i+1) %2=0;
i id
2 2
4 4
6 6
8 8