怎么重置mysql id从1开始排列？

能用 sql 写不？比如：

UPDATE test
  JOIN (SELECT id, ROW_NUMBER() OVER (ORDER BY id) row_num
          FROM test) tar USING(id)
   SET id = tar.row_num;

新增测试表：

CREATE TABLE IF NOT EXISTS test (id INT PRIMARY KEY, expect INT NOT NULL) AS
  WITH RECURSIVE
    gen(id, expect) AS (
      SELECT 1, 1
       UNION ALL
      -- 每一次的 id，随机比上一行的增加 1 ~ 3
      SELECT id + FLOOR(3 * RAND() + 1), expect + 1
        FROM gen
       WHERE expect < 10
    )
  SELECT * FROM gen;

test 表数据：

修改后：

@wxf666

UPDATE countries
  JOIN (SELECT id, ROW_NUMBER() OVER (ORDER BY id) row_num
          FROM countries) tar USING(id)
   SET id = tar.row_num;

遇到错误 1064 - 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 '(ORDER BY id) row_num FROM countries) tar USING(id) SET id = t' at line 2

保存数据，清空数据表，把保存的数据再次存入数据库

@tiantian10000 如果你的是旧版本 MySQL，那就用变量呗

SET @row_num = 0;
UPDATE test
   SET id = (@row_num := @row_num + 1);

执行前，最好开个事务。UPDATE后，SELECT * FROM test瞅瞅对了没，没问题再COMMIT

alter table users AUTO_INCREMENT=1; -- 1 必须大于 id，如果空表可以设置1
-- 既然 id 设置为 1了，数据表也就不能用数据了，直接清空表，也可以达到相同的效果
TRUNCATE TABLE `users `;

通过下面的sql 可以记录到原始id，所以如果你表结构有树形 或者上下级分类相关的数据 可以保持更新之后上下级仍然保持原有规则：

SELECT id,form_id ,CONCAT('UPDATE form_field set id = ',(@row_num := @row_num +1),' where id = ',id,';')FROM form_field as a,(select @row_num := 0) b ORDER BY form_id ;