手动自动递增一列MySQL [英] Autoincrement manually a column MySQL

问题描述

我有一个带有列的表:

[id,name,public_id]

该表实际上包含

[1,john,0]
[2,sara,0]
[3,jack,0]
.....

我想将第三列更改为1050,1051,1052 ....

I want to change the third column to 1050,1051,1052....

[1,john,1050]
[2,sara,1051]
[3,jack,1052]
.....

我该如何进行更新?

一些注意事项:公共ID必须超过1049，并且必须是连续的.例如，对于100行，public_id必须为[1050 .... 1149]

Some considerations: The public id must be over 1049 and must be consecutive. For example for 100 rows the public_id must be [1050....1149]

预先感谢

推荐答案

假定表的名称为TEST_TABLE，此MySQL语法将使用从1500开始的连续值(和ID顺序)更新PUBLIC_ID:

Assuming your table's name is TEST_TABLE, this MySQL syntax will update PUBLIC_ID with consecutive values starting from 1500 (and in order of ID):

REPLACE INTO TEST_TABLE
SELECT TEST_TABLE.ID, TEST_TABLE.NAME, @ROWNUM := @ROWNUM + 1
FROM
    TEST_TABLE,
    (SELECT @rownum := 1499) R
ORDER BY ID

简而言之:

• 对于每一行，请找出按ID对数据进行排序时的顺序.将此订单称为"ROWNUM".
• 将ROWNUM(+起始偏移量)放回表中，而不是原始PUBLIC_ID.

警告:根据 MySQL文档，REPLACE实际上会删除重复的行，然后再次插入(而不是仅更新修改的字段)，这在存在外键的情况下是个问题.

WARNING: According to MySQL documentation, REPLACE will actually delete a duplicated row before inserting it again (instead of just updating modified fields), which my be an issue in the presence of foreign keys.

(不确定SQLite，但我猜您可以采用类似的一般思路.)

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