MySQL rownumber SQL生成自增长序号

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

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

1. mysql>createtabletbl(
2. ->idintprimarykey,
3. ->colint
4. ->);
5. QueryOK,0rowsaffected(0.08sec)
7. mysql>insertintotblvalues
8. ->(1,26),
9. ->(2,46),
10. ->(3,35),
11. ->(4,68),
12. ->(5,93),
13. ->(6,92);
14. QueryOK,6rowsaffected(0.05sec)
15. Records:6Duplicates:0Warnings:0
17. mysql>
18. mysql>select*fromtblorderbycol;
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. 6rowsinset(0.00sec)

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)+1asrownum,id,col
2. ->fromtbl
3. ->orderbycol;
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. 6rowsinset(0.00sec)

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

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

1. mysql>selecta.id,a.col,count(*)asrownum
2. ->fromtbla,tblb
3. ->wherea.col>=b.col
4. ->groupbya.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. 6rowsinset(0.00sec)

4.子查询

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

1. mysql>selecta.*,
2. ->(selectcount(*)fromtblwherecol<=a.col)asrownum
3. ->fromtbla;--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. 6rowsinset(0.06sec)