mysql 中序号要怎么写_如何在mysql的字段ID中插入自动编号？

如何在mysql的字段ID中插入自动编号？我已经有idmember作为主键

这是我尝试过的代码,请更正它

UPDATE member SET id = Row_number()over ORDER BY idmember

You have an error in your SQL syntax; check the manual that corresponds to your MySQL

server version for the right syntax to use near 'over order by idmember' at line 1

解决方法:

CREATE TABLE member (id INT PRIMARY KEY, idmember INT);

INSERT INTO member (id, idmember) VALUES (10, 10), (11, 20), (12, 42);

SELECT * FROM member;

+----+----------+

| id | idmember |

+----+----------+

| 10 | 10 |

| 11 | 20 |

| 12 | 42 |

+----+----------+

SET @i := 0;

UPDATE member SET id = @i:=@i+1 ORDER BY idmember;

SELECT * FROM member;

+----+----------+

| id | idmember |

+----+----------+

| 1 | 10 |

| 2 | 20 |

| 3 | 42 |

+----+----------+

标签：mysql

来源： https://codeday.me/bug/20191202/2085348.html