如何在MySQL中向字段ID插入自动编号？

Question

如何在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

- user1040364

---

MySQL 中没有可用的分析函数。 - ypercubeᵀᴹ

Answer 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 |
+----+----------+

- Bill Karwin

Answer 2

将表结构中的ID字段设置为自动增量，这样它就会自动递增。

- Sudhir Bastakoti

---

我尝试了，但它不允许，因为我已经有主键了。 - user1040364