MySQL rownumber SQL generation auto-increment sequence number usage

The following describes several specific implementation methods.

The following is the tutorial environment.
Mysql> create table tbl (
-> Id int primary key,
-> Col int
-> );
Query OK, 0 rows affected (0.08 sec)

Mysql> insert into tbl values
-> (1, 26 ),
-> (2, 46 ),
-> (3, 35 ),
-> (4,68 ),
-> (5, 93 ),
-> (6, 92 );
Query OK, 6 rows affected (0.05 sec)
Records: 6 Duplicates: 0 Warnings: 0

Mysql>
Mysql> select * from tbl order by col;
+ ---- + ------ +
| Id | col |
+ ---- + ------ +
| 1 | 26 |
| 3 | 35 |
| 2 | 46 |
| 4 | 68 |
| 6 | 92 |
| 5 | 93 |
+ ---- + ------ +
6 rows in set (0.00 sec)

1. directly implemented in the program;
This should be the most efficient and extremely convenient. Directly in your Development Program (PHP/ASP/C /...) etc, initialize a variable nRowNum = 0, then nRowNum ++ in the while record set, and then output.

2. Use MySQL variables. In some cases, you can consider this method if you cannot modify the program.
Disadvantage: The @ x variable is at the connection level and needs to be initialized during re-query. Generally, PHP and other B/S applications do not have this problem. But C/S, if the connection is maintained only, you need to consider set @ x = 0

Mysql> select @ x: = ifnull (@ x, 0) + 1 as rownum, id, col
-> From tbl
-> Order by col;
+ -------- + ---- + ------ +
| Rownum | id | col |
+ -------- + ---- + ------ +
| 1 | 1 | 26 |
| 1 | 3 | 35 |
| 1 | 2 | 46 |
| 1 | 4 | 68 |
| 1 | 6 | 92 |
| 1 | 5 | 93 |
+ -------- + ---- + ------ +
6 rows in set (0.00 sec)

3. Use join query (Cartesian Product)
Disadvantages: Obviously, the efficiency will be lower.

Use the table's self-join code as follows. You can try select a directly. *, B. * from tbl a, tbl B where. col> = B. col to understand the principle of this method.

Mysql> select a. id, a. col, count (*) as rownum
-> From tbl a, tbl B
-> Where a. col> = B. col
-> Group by a. id, a. col;
+ ---- + ------ + -------- +
| Id | col | rownum |
+ ---- + ------ + -------- +
| 1 | 26 | 1 |
| 2 | 46 | 3 |
| 3 | 35 | 2 |
| 4 | 68 | 4 |
| 5 | 93 | 6 |
| 6 | 92 | 5 |
+ ---- + ------ + -------- +
6 rows in set (0.00 sec)

4. subquery

Disadvantages: like join queries, the specific efficiency depends on the index configuration and MySQL optimization results.
Mysql> select .*,
-> (Select count (*) from tbl where col <= a. col) as rownum
-> From tbl;
+ ---- + ------ + -------- +
| Id | col | rownum |
+ ---- + ------ + -------- +
| 1 | 26 | 1 |
| 2 | 46 | 3 |
| 3 | 35 | 2 |
| 4 | 68 | 4 |
| 5 | 93 | 6 |
| 6 | 92 | 5 |
+ ---- + ------ + -------- +
6 rows in set (0.06 sec)

MySQL is undoubtedly an open-source database system. Its update speed and document maintenance are not inferior to those of several commercial database products. It is estimated that in the next version, we can see the ROWNUM implemented by MySQL itself.