Auto increment a value on the fly with MySQL

最新推荐文章于 2025-09-11 20:53:34 发布

weixin_33705053

最新推荐文章于 2025-09-11 20:53:34 发布

阅读量62

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/vincentcuhk/blog/620218

本文介绍了如何在MySQL中使用自增变量实现查询和更新操作。通过设置变量并结合SELECT和UPDATE语句，可以轻松地为查询结果添加自增序号或将表中的某列更新为自增数值。

2019独角兽企业重金招聘Python工程师标准>>>

SELECT with auto incrementing value

To have an auto incrementing value, first assign a variable like so:

SELECT @i:=0;

This can now be incremented in the SELECT query like so:

SELECT *, @i:=@i+1 AS i FROM fruit ORDER BY name;

This will return:

+----------+--------+-----------+------+
| fruit_id | name   | somevalue | i    |
+----------+--------+-----------+------+
|        4 | Apple  |         0 |    1 |
|        1 | Banana |         0 |    2 |
|        3 | Cherry |         0 |    3 |
|        2 | Orange |         0 |    4 |
+----------+--------+-----------+------+

Notice that for each record, i is one greater than the previous record.

UPDATE with auto incrementing value

The next example updates the "somevalue" column with an incrementing value. Note that @i needs to be reset otherwise it will continue in this example with 5 being the next number.

SELECT @i:=0;

As with the SELECT query above, we'll update ordering by name:

UPDATE fruit SET somevalue = @i:=@i+1 ORDER BY name;

And the result from "SELECT * FROM fruit"

+----------+--------+-----------+
| fruit_id | name   | somevalue |
+----------+--------+-----------+
|        1 | Banana |         2 |
|        2 | Orange |         4 |
|        3 | Cherry |         3 |
|        4 | Apple  |         1 |
+----------+--------+-----------+

or "SELECT * FROM fruit ORDER BY name"

+----------+--------+-----------+
| fruit_id | name   | somevalue |
+----------+--------+-----------+
|        4 | Apple  |         1 |
|        1 | Banana |         2 |
|        3 | Cherry |         3 |
|        2 | Orange |         4 |
+----------+--------+-----------+

转载于:https://my.oschina.net/vincentcuhk/blog/620218