Mysql database implements a pseudo-column like a serial number _ MySQL

Mysql database implements pseudo-column bitsCN.com similar to serial numbers

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

For example, after sorting a table field, we need to add a sequence to the data. at this time, the sequence number is often not the auto-incrementing primary key id we set during table creation, what should we do ????

We may use variables to solve the problem, as shown below:

Set @ rownum = 0;

SELECT @ rownum: = @ rownum + 1 AS rownum, table_name .*
FROM table_name

What should I do if I use an SQL statement in the database ????

The oracle database has a dedicated rownum to solve the pseudo sequence problem, but mysql has no built-in functions so far to solve this problem.

This problem has also plagued me for a while, and I have not been able to answer any questions posted on various forums. Finally, I accidentally found this solution.

SELECT @ rownum: = @ rownum + 1 AS rownum, table_name .*
FROM (SELECT @ rownum: = 0) r, table_name

Place the initial value of rownum in front of the from and the table to be queried.

The original article is from "the heart of laruence". thank you here.

Hope to help more people who need help ......

I also hope that more experts will come up with more beautiful methods ......

Some people may also use stored procedures to solve the problem. this is also a method. I have used it before, but later I cannot use stored procedures. this is a tragedy.

From ystyaoshengting's column

BitsCN.com