MySQL中等效的ROW_NUMBER()用于插入 [英] ROW_NUMBER() equivalent in MySQL for inserting

问题描述

我正在尝试将在Microsoft SQL Server中创建的，以链接服务器运行的SQL脚本转换为可以在SQL Procedures中使用的脚本，我正在使用的脚本

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

错误1064(42000):您的SQL语法有错误:请查看与您的MySQL服务器版本相对应的手册，以在[[LINENO]]行的'(ORDER BY [FIELDS])'附近使用正确的语法. /p>

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]

很明显，该错误表明ROW_NUMBER OVER不正确，原因是我删除了OVER位，并收到一条错误消息，指出ROW_NUMBER未定义

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

在任何地方搜索，我什么都没得到，只有人们问这个问题的SELECT语句，而不是INSERT语句，大多数时候，答案只是关于获取行数或插入最后一个ID，所以我可以使用创建与ROW_NUMBER()在Microsoft Server中相同的数据

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

推荐答案

不幸的是，MySQL中没有ROW_NUMBER()等效项，但是您仍然可以通过创建一个简单的变量来模拟它，该变量将值保留为每行递增.

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.

示例:

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

• SQLFiddle演示

这篇关于MySQL中等效的ROW_NUMBER()用于插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！