Usage and attention of Mysql row number() sorting function_MySQL

Although it is not used much, there are situations where it is necessary to write statements in mysql to develop functions. 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 owner. Query through variable assignment. (PS The version I tested is mysql 5.6)

Create the 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; 

Then insert the test data and use the method introduced by the po master to test

insert into test(Col1,Col2,Col3,Col4) values (1,2,3,4),(1,2,6,5),(1,2,7,8),(1,"激发",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 of the query is

Why is Rnr always 1 when checking the demerits? Because for this query, the value of the @row variable has always been null, so based on the discriminant of the case, it can only be 1. caused this phenomenon. But if you execute it again, it will be normal. We will use the same script

Execute it again and it should be 2345

The test verified that this is indeed the case, so it can be seen. The variable always exists in this session and will be used every time, so the previous situation occurs.

In fact, the corresponding solution is also very simple. Since each session will use the same value, you only need to assign an initial value at the beginning to solve the problem. For example, in our example, set @row = at the beginning. 0; Add this sentence, then every time it is executed, the initial value will be assigned first, and of course, you can get consistent execution results.

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

The above is the usage and attention of the Mysql row_number() sorting function introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support of the website!