Use variables to query row numbers in MySQL

MySQL can use variables to query the row number. Of course, if you use mysql5, you can write a function to implement it. Let's take a look at the implementation method of MySQL to query the row number.

 
 

  
  
mysql> select * from t;   
  
  
+------+   
  
  
| s1 |   
  
  
+------+   
  
  
| 6 |   
  
  
| 6 |   
  
  
| 0 |   
  
  
| 19 |   
  
  
| 19 |   
  
  
| 1 |   
  
  
| 2 |   
  
  
| 3 |   
  
  
| 4 |   
  
  
| 0 |   
  
  
| 1 |   
  
  
| 2 |   
  
  
| 4 |   
  
  
+------+   
  
  
13 rows in set (0.44 sec)   
  
  
 
  
  
mysql> set @rownum=0;   
  
  
Query OK, 0 rows affected (0.00 sec)   
  
  
 
  
  
mysql> select @rownum:=@rownum+1 as rownum,s1 from t;   
  
  
+--------+------+   
  
  
| rownum | s1 |   
  
  
+--------+------+   
  
  
| 1 | 6 |   
  
  
| 2 | 6 |   
  
  
| 3 | 0 |   
  
  
| 4 | 19 |   
  
  
| 5 | 19 |   
  
  
| 6 | 1 |   
  
  
| 7 | 2 |   
  
  
| 8 | 3 |   
  
  
| 9 | 4 |   
  
  
| 10 | 0 |   
  
  
| 11 | 1 |   
  
  
| 12 | 2 |   
  
  
| 13 | 4 |   
  
  
+--------+------+   
  
  
13 rows in set (0.04 sec)  
  
  
 
  
  
   
  
  
 
 
 

The preceding method uses variables to query row numbers in MySQL.

Troubleshooting of MySQL query timeout

MySQL Date and Time Functions

Transformed from the MySQL Query Class of discuz

Explanation for querying cache variables in MySQL

Usage of MySQL conditional query statements