MySQL RowNumber SQL Build self-growth serial number use introduction _mysql

Here are some specific implementation methods.

The establishment of the experimental environment is as follows
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 in the process of implementation;
This should be regarded as one of the most efficient and extremely convenient. Directly in your development program (php/asp/c/...). And so on, directly initializes a variable nrownum=0, and then in the while Recordset, nrownum++; Then the output can be.

2. Use MySQL variables; in some cases, you can consider this method when you cannot modify the program to implement it.
Disadvantage, the @x variable is connection level and needs to be initialized when querying again. Generally speaking, PHP and other B/s applications do not have this problem. However, C/s If the connection one to keep the set @x=0 to consider

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)
Shortcomings, it is obvious that the efficiency will be worse.

Using the table's self-join, the code is as follows, you can try the select a.*,b.* from tbl A,tbl b where a.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. Sub-query

Disadvantages, as with join queries, the specific efficiency depends on the configuration of the index and the results of the MySQL optimization.
Mysql> Select A.*,
-> (SELECT COUNT (*) from TBL where Col<=a.col) as RowNum
-> from TBL A;
+----+------+--------+
| 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)

As an open source database system, MySQL is undoubtedly a product not to do. Its update speed, document maintenance is not inferior to several major commercial database products. It is estimated that in the next release, we can see the rownum implemented by MySQL itself.