Usage and note of using row_number () in Mysql, mysqlrow_number

Usage and note of using row_number () in Mysql, mysqlrow_number

Although it is not used much, it is also necessary to write the statement development function in mysql. In SQL server, I am used to using the row_number () function for sorting, but mysql does not have such a function. Then I found an article written by the po master. Query by assigning values to variables. (Ps I tested mysql 5.6)

Reference: http://www.cnblogs.com/advocate/archive/2012/03/02/2376900.html

Create a table first

CREATE TABLE `test` (  `ID` int(11) NOT NULL AUTO_INCREMENT,  `Col1` varchar(50) DEFAULT NULL,  `Col2` varchar(50) DEFAULT NULL,  `Col3` int(11) DEFAULT NULL,  `Col4` float DEFAULT NULL,  PRIMARY KEY (`ID`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

Insert the test data and use the method described by the po master for testing.

Insert into test (Col1, Col2, Col3, Col4) values (,), (1, "excitation ", 5, 7); delimiter // select @ row: = case when @ row is null then 1 else @ row + 1 end as RNr, ID, Col1, Col2, Col3, Col4 from test; // delimiter;

The result is

 

Why is Rnr 1? For this query, the value of the variable @ row is always null. Therefore, it can only be 1 based on the case discriminant. This problem occurs. But execute the script again.

Run the command again and check that it is 2345.

 

This is true for test verification. Variables always exist in this session and will be used each time, so the previous situation occurs.

In fact, the corresponding solution is also very simple. Since each session uses the same value, you only need to assign the initial value at the beginning to solve the problem, for example, if we add set @ row = 0 at the beginning of this example, the initial value will be assigned for each execution. Of course, the execution results will be consistent.

 

This test tells me that the initial value of variable hin is important in this database.