MySQL递增值 [英] MySQL incrementing value

问题描述

如果有多个插入，是否可以通过每个插入使值增加? (我没有说自动递增的主键)

Is there a way to make a value increment with every insert if having multiple inserts? (I dont speak of the primary key that autoincrements)

让我们说我有一个这样的结构:

Lets say I have a structure like this:

|ID_PRODUCT|ID_CATEGORY|NAME|POSITION|

因此，我有单独的产品ID，每个产品都属于一个类别，并且在该类别中具有不同的位置.我想做这样的事情:

So I have individual product ids, each produt belongs to a category and has a different position in this category. I want to do something like this:

INSERT INTO products
( SELECT id_product, id_category, name, MY_POSITION++
  FROM db2.products WHERE id_category = xxx )

因此，应该有一个变量MY_POSITION，该变量以1开头，并且每次插入都会递增.

So there should be a variable MY_POSITION that starts with 1 and increments every insert.

仅使用像php或python这样的脚本语言来做到这一切真的很容易，但是我想使用SQL变得更好:)

It would be really easy to do this all just with a scripting-language like php or python, but I want to get better with SQL :)

推荐答案

是:使用用户定义的变量:

SET @position := 0; -- Define a variable
INSERT INTO products
SELECT id_product, id_category, name, (@position := @position + 1)
FROM db2.products
WHERE id_category = xxx;

增加到@position的结果是用于插入的值.

The result of increment to @position is the value used for the insert.

您可以通过内联处理初始值来跳过变量的声明:

You can skip the declaration of the variable by handling the initial value in-line:

...
SELECT ..., (@position := ifnull(@position, 0) + 1)
...

当使用不允许多个命令(用分号分隔)的驱动程序执行查询时，这特别方便.

This can be particularly handy when executing the query using a driver that does not allow multiple commands (separated by semicolons).

这篇关于MySQL递增值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！