56,677
社区成员
发帖
与我相关
我的任务
分享
mysql> create table table1(
-> id int AUTO_INCREMENT primary key,
-> col1 int
-> );
Query OK, 0 rows affected (0.16 sec)
mysql>
mysql> insert into table1(col1) values
-> (1),(2),(3),(4),(5);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from table1;
+----+------+
| id | col1 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
+----+------+
5 rows in set (0.00 sec)
mysql>
mysql> update table1 set id=id+1;
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> update table1 set id=id+1 order by id desc;
Query OK, 5 rows affected (0.06 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from table1;
+----+------+
| id | col1 |
+----+------+
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
| 5 | 4 |
| 6 | 5 |
+----+------+
5 rows in set (0.00 sec)
mysql>
-- 先将数据放到一个临时表中
CREATE TEMPORARY TABLE tmp_name
AS
SELECT xx,xxx,xxxx FROM table_name;
TRUNCATE TABLE table_name;
INSERT tmp_name(xx,xxx,xxx)
SELECT xx,xxx,xxxx FROM tmp_name;
DROP TABLE tmp_name;