Use functions to query row numbers in MySQL

There are more than one implementation method for querying row numbers in MySQL. The following describes how to use functions to query row numbers in MySQL. If you are interested in this, take a look.

MySQL uses functions in MySQL 5 to query row numbers. The principle is similar to that of using variables.

The following MySQL row number query process is used to obtain the result set of the number of integer rows, similar to ROWNUM () in other DBMS (). We need a user variable to save the result after each call of rno (). Name it @ rno.

 
 

  
  
CREATE FUNCTION rno ()   
  
  
RETURNS INT   
  
  
BEGIN   
  
  
SET @rno = @rno + 1;   
  
  
RETURN @rno;   
  
  
END;   
  
  
 
 
 

Using the SELECT method of rno (), we obtain the number of rows. The following is the result of calling the program:

 
 

  
  
mysql> SET @rno = 0;//   
  
  
Query OK, 0 rows affected (0.00 sec)   
  
  
mysql> SELECT rno(),s1,s2 FROM t;//   
  
  
+-------+------+------+   
  
  
| rno() | s1 | s2 |   
  
  
+-------+------+------+   
  
  
| 1 | 1 | a |   
  
  
| 2 | 2 | b |   
  
  
| 3 | 3 | c |   
  
  
| 4 | 4 | d |   
  
  
| 5 | 5 | e |   
  
  
+-------+------+------+   
  
  
5 rows in set (0.00 sec)   
  
  
 
 
 

The trick of setting @ rno to zero in SELECT is to use the WHERE evaluate function, which may be lost in MySQL in the future.

 
 

  
  
CREATE FUNCTION rno_reset ()   
  
  
RETURNS INTEGER   
  
  
BEGIN   
  
  
SET @rno = 0;   
  
  
RETURN 1;   
  
  
END;   
  
  
SELECT rno(),s1,s2 FROM t WHERE rno_reset()=1;//   
  
  
 
 
 

Example:

 
 

  
  
mysql> use db5   
  
  
Database changed   
  
  
mysql> delimiter //   
  
  
mysql> CREATE FUNCTION rno ()   
  
  
-> RETURNS INT   
  
  
-> BEGIN   
  
  
-> SET @rno = @rno + 1;   
  
  
-> RETURN @rno;   
  
  
-> END;//   
  
  
Query OK, 0 rows affected (0.42 sec)   
  
  
 
  
  
mysql> set @rno = 0;   
  
  
-> //   
  
  
Query OK, 0 rows affected (0.00 sec)   
  
  
 
  
  
mysql> select rno(),s1 from t;//   
  
  
+-------+------+   
  
  
| rno() | 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.06 sec)   
  
  
 
  
  
mysql> CREATE FUNCTION rno_reset ()   
  
  
-> RETURNS INTEGER   
  
  
-> BEGIN   
  
  
-> SET @rno = 0;   
  
  
-> RETURN 1;   
  
  
-> END;//   
  
  
Query OK, 0 rows affected (0.01 sec)   
  
  
 
  
  
mysql> select rno(),s1 from t where rno_reset()=1;//   
  
  
+-------+------+   
  
  
| rno() | 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.13 sec)  
  
  
 
 
 

Non-empty question in MySQL Query

Troubleshooting of MySQL query timeout

How to adjust the MySQL Query Buffer

How MySQL query optimizer works

Explanation for querying cache variables in MySQL