mysql 指定一列自动递增,手动自动递增一列MySQL

I have a table with columns:

[id,name,public_id]

actually the table contains

[1,john,0]

[2,sara,0]

[3,jack,0]

.....

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

[1,john,1050]

[2,sara,1051]

[3,jack,1052]

.....

How can I make that update?

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]

Thanks in advance

解决方案

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

In plain English:

For each row, figure out its order when data is sorted by ID. Call this order "ROWNUM".

Put ROWNUM (+ starting offset) back to the table instead of the original PUBLIC_ID.

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.

(Not sure about SQLite, but I'm guessing you could employ a similar general idea.)