MySQLrownumberSQL generates auto-increment serial number

This article briefly introduces how to use MySQLrownumberSQL to generate auto-increment sequence numbers.

This article briefly introduces how to test the auto-increment sequence number generated by MySQL rownumber SQL.

MySQL almost simulates most commercial functions and functions such as Oracle and SQL Server. It is a pity that the ROWNUM function has not been implemented until the current version (5.1.33.

The following describes several specific implementation methods.

The following is the tutorial environment.

The Code is as follows:

> 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> * 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

The Code is as follows:
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.

The Code is as follows:
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.

The Code is as follows:
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)