ROW_NUMBER() equivalent in MySQL for inserting [du

问题:

This question already has an answer here:

• ROW_NUMBER() in MySQL 21 answers

i'm trying to convert SQL scripts that was created in Microsoft SQL Server to run with a link sever to scripts that can be used in SQL Procedures, the script i'm on uses

ROW_NUMBER() OVER(ORDER BY [FIELDS])

to create a primary key that isn't dependent on Auto Increment, when i try and save the code as a Procedure i get this error

ERROR 1064 (42000): You have an error in your SQL syntax: check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY [FIELDS])' at line [LINENO]

obviously the error is saying that ROW_NUMBER OVER is not right cause i removed the OVER bit and got an error saying that ROW_NUMBER was undefined

everywhere i search i get nothing but people asking this question for SELECT statement, not INSERT statements and the answers most of the time are just about either getting the number of rows or getting the last id inserted, so what i can i use to create the same data that ROW_NUMBER() would in Microsoft Server

回答1:

Unfortunately, there is no ROW_NUMBER() equivalent in MySQL but you can still simulate it by creating a simple variable which holds a value an increment it every row.

Example:

SET @rank=0;
SELECT   @rank := @rank+1 AS rank, fruit, amount
FROM     sales
ORDER BY amount DESC;

• SQLFiddle Demo