新建表create table 新表(id int auto_increment primary key,col2 int,col3 int)insert into 新表(col2,col3) select col2,col3 from 老表rename table 老表 to temp,新表 to 老表
mysql> create table tt (id int auto_increment primary key,c int); Query OK, 0 rows affected (0.08 sec)mysql> insert into tt (c) values (10),(20),(30),(40),(50); Query OK, 5 rows affected (0.05 sec) Records: 5 Duplicates: 0 Warnings: 0mysql> select * from tt; +----+------+ | id | c | +----+------+ | 1 | 10 | | 2 | 20 | | 3 | 30 | | 4 | 40 | | 5 | 50 | +----+------+ 5 rows in set (0.00 sec)mysql> delete from tt where id =3; Query OK, 1 row affected (0.00 sec)mysql> select * from tt; +----+------+ | id | c | +----+------+ | 1 | 10 | | 2 | 20 | | 4 | 40 | | 5 | 50 | +----+------+ 4 rows in set (0.00 sec)mysql> update tt a ,(select b.id,count(*) as cid from tt b,tt c where b.id>=c.id group by b.id) d -> set a.id=d.cid -> where a.id=d.id; Query OK, 2 rows affected (0.00 sec) Rows matched: 4 Changed: 2 Warnings: 0mysql> select * from tt; +----+------+ | id | c | +----+------+ | 1 | 10 | | 2 | 20 | | 3 | 40 | | 4 | 50 | +----+------+ 4 rows in set (0.00 sec)mysql>
select col2,col3
from 老表rename table 老表 to temp,新表 to 老表
Query OK, 0 rows affected (0.08 sec)mysql> insert into tt (c) values (10),(20),(30),(40),(50);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0mysql> select * from tt;
+----+------+
| id | c |
+----+------+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
| 5 | 50 |
+----+------+
5 rows in set (0.00 sec)mysql> delete from tt where id =3;
Query OK, 1 row affected (0.00 sec)mysql> select * from tt;
+----+------+
| id | c |
+----+------+
| 1 | 10 |
| 2 | 20 |
| 4 | 40 |
| 5 | 50 |
+----+------+
4 rows in set (0.00 sec)mysql> update tt a ,(select b.id,count(*) as cid from tt b,tt c where b.id>=c.id
group by b.id) d
-> set a.id=d.cid
-> where a.id=d.id;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 4 Changed: 2 Warnings: 0mysql> select * from tt;
+----+------+
| id | c |
+----+------+
| 1 | 10 |
| 2 | 20 |
| 3 | 40 |
| 4 | 50 |
+----+------+
4 rows in set (0.00 sec)mysql>