MySQL uses pseudo-columns

When querying the database, we sometimes need to add a sequence to the queried data, 1,2,3,......N

For example, we sort the data according to a field of the table, we want to add a sequence to this, when the serial number is often not the self-increment primary key ID that we set up when we build the table, how to do????

Maybe we'll use variables to solve them, as follows

SET @rownum:=0; SELECT @rownum:=@rownum+1 as rownum,a.*from T_s_base_user A;

If you want to use a SQL statement in the database to achieve this, then what to do????

The Oracle database has a dedicated rownum to solve the pseudo-sequence problem, but MySQL has so far not built-in functions to solve this problem

This problem also bothered me for a while, to a variety of posts have not been able to answer, and finally found this solution by accident

SELECT @rownum:=@rownum+1 as RowNum, T_s_base_user. *  from (SELECT@rownum:=0) R, T_s_base_user

Put the initial value of the rownum in front of the From and the table that needs to be queried

Maybe someone will use the stored procedure to solve, that is also a method, I have used, but later can not use stored procedures, it is tragic

But the program will error "Parameter ' @rownum ' must be defined."

Then add "Allow User variables=true" after the configuration file database link string.

<!-- MySQL Connection string  -         <  key= "Mysqlconn"  value= "SERVER=192.168.1.238;DATABASE=GM; Uid=root; pwd=000000; Allow User variables=true; " />

MySQL uses pseudo-columns