MySQL rownumber SQL生成自增长序号

MySQL 几乎模拟了 Oracle,SQL Server等商业数据库的大部分功能,函数,但很可惜,到目前的版本(5.1.33)为止,仍没有实现ROWNUM这个功能.

下面介绍几种具体的实现方法.建立实验环境如下:

1. mysql> create table tbl ( 
2.     ->  id      int primary key, 
3.     ->  col     int 
4.     -> ); 
5. Query OK, 0 rows affected (0.08 sec) 
6.  
7. mysql> insert into tbl values 
8.     -> (1,26), 
9.     -> (2,46), 
10.     -> (3,35), 
11.     -> (4,68), 
12.     -> (5,93), 
13.     -> (6,92); 
14. Query OK, 6 rows affected (0.05 sec) 
15. Records: 6  Duplicates: 0  Warnings: 0 
16.  
17. mysql> 
18. mysql> select * from tbl order by col; 
19. +----+------+ 
20. | id | col  | 
21. +----+------+ 
22. |  1 |   26 | 
23. |  3 |   35 | 
24. |  2 |   46 | 
25. |  4 |   68 | 
26. |  6 |   92 | 
27. |  5 |   93 | 
28. +----+------+ 
29. 6 rows in set (0.00 sec) 

1. 直接在程序中实现:

这应该算是效率最高的一种,也极为方便,直接在你的开发程序中（PHP/ASP/C/...）等中,直接初始化一个变量nRowNum=0,然后在while 记录集时，nRowNum++; 然后输出即可.

2. 使用MySQL变量；在某些情况下,无法通过修改程序来实现时,可以考虑这种方法,缺点,@x 变量是 connection 级的,再次查询的时候需要初始化,一般来说PHP等B/S应用没有这个问题,但C/S如果connection一只保持则要考虑 set @x=0,代码如下:

1. mysql> select @x:=ifnull(@x,0)+1 as rownum,id,col 
2.     -> from tbl 
3.     -> order by col; 
4. +--------+----+------+ 
5. | rownum | id | col  | 
6. +--------+----+------+ 
7. |      1 |  1 |   26 | 
8. |      1 |  3 |   35 | 
9. |      1 |  2 |   46 | 
10. |      1 |  4 |   68 | 
11. |      1 |  6 |   92 | 
12. |      1 |  5 |   93 | 
13. +--------+----+------+ 
14. 6 rows in set (0.00 sec) 

3.使用联接查询,笛卡尔积.

缺点,显然效率会差一些,利用表的自联接,代码如下,你可以直接试一下 select a.*,b.* from tbl a,tbl b where a.col>=b.col 以理解这个方法原理,代码如下:

1. mysql> select a.id,a.col,count(*) as rownum 
2.     -> from tbl a,tbl b 
3.     -> where a.col>=b.col 
4.     -> group by a.id,a.col; 
5. +----+------+--------+ 
6. | id | col  | rownum | 
7. +----+------+--------+ 
8. |  1 |   26 |      1 | 
9. |  2 |   46 |      3 | 
10. |  3 |   35 |      2 | 
11. |  4 |   68 |      4 | 
12. |  5 |   93 |      6 | 
13. |  6 |   92 |      5 | 
14. +----+------+--------+ 
15. 6 rows in set (0.00 sec) 

4.子查询

缺点:和联接查询一样,具体的效率要看索引的配置和MySQL的优化结果,代码如下:

1. mysql> select a.*, 
2.     ->  (select count(*) from tbl where col<=a.col) as rownum 
3.     -> from tbl a;  --phpfensi.com 
4. +----+------+--------+ 
5. | id | col  | rownum | 
6. +----+------+--------+ 
7. |  1 |   26 |      1 | 
8. |  2 |   46 |      3 | 
9. |  3 |   35 |      2 | 
10. |  4 |   68 |      4 | 
11. |  5 |   93 |      6 | 
12. |  6 |   92 |      5 | 
13. +----+------+--------+ 
14. 6 rows in set (0.06 sec)