MySQL MySQL递增值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9631096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL incrementing value
提问by Bohemian
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|
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:
所以我有单独的产品 ID，每个产品都属于一个类别，并且在这个类别中具有不同的位置。我想做这样的事情：
INSERT INTO products
( SELECT id_product, id_category, name, MY_POSITION++
FROM db2.products WHERE id_category = xxx )
So there should be a variable MY_POSITION that starts with 1 and increments every insert.
所以应该有一个变量 MY_POSITION 从 1 开始，每次插入都递增。
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 :)
仅使用 php 或 python 等脚本语言来完成这一切真的很容易，但我想用 SQL 变得更好:)
回答by Bohemian
Yes: Use a user defined variable:
是：使用用户定义的变量：
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;
The result of increment to @positionis the value used for the insert.
增加到的结果@position是用于插入的值。
Edit:
编辑：
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).
这在使用不允许多个命令（用分号分隔）的驱动程序执行查询时特别方便。
回答by nnichols
You will need to ORDER BY id_category and use two user variables so you can track the previous category id -
您将需要按 id_category 排序并使用两个用户变量，以便您可以跟踪上一个类别 id -
SET @position := 0;
SET @prev_cat := 0;
INSERT INTO products
SELECT id_product, id_category, name, position
FROM (
SELECT
id_product,
id_category,
name,
IF(@prev_cat = id_category, @position := @position + 1, @position := 1) AS position,
@prev_cat := id_category
FROM db2.products
ORDER BY id_category ASC, id_product ASC
) AS tmp;
This will allow you to do all categories in one query instead of category by category.
这将允许您在一个查询中完成所有类别，而不是按类别进行分类。
回答by Antony
Purely to add to @Bohemians answer - I wanted the counter to reset every so often and this can be done like such:
纯粹是为了添加@Bohemians 的回答——我希望计数器经常重置，可以这样做：
SELECT *,(@position := IF (@position >= 15,1,@position + 1))
SELECT *,(@position := IF (@position >= 15,1,@position + 1))
Where 15 is obviously the maximum number before reset.
其中 15 显然是重置前的最大数字。
回答by Oriesok Vlassky
Try setting a value using a subquery like this
尝试使用这样的子查询设置值
(SELECT MAX(position) FROM products AS T2)+1
Or
或者
(SELECT MAX(position) FROM products AS T2 WHERE id_category = 'product category')+1
回答by Ameen Maheen
Hope this will work.
希望这会奏效。
update <tbl_name> set <column_name>=<column_name>+1 where <unique_column/s>='1'";
