表tab_rec : v_path
表tab_rec_bak : v_path
两表结构一样,表类型为myisam,现需要将表tab_rec的数据移到tab_rec_bak,移动过的数据在原表中删除,需要保证数据的一致性。
请问怎么写sql语句
表tab_rec_bak : v_path
两表结构一样,表类型为myisam,现需要将表tab_rec的数据移到tab_rec_bak,移动过的数据在原表中删除,需要保证数据的一致性。
请问怎么写sql语句
delete from tab_rec where v_path in (select v_path from tab_rec_bak);
insert into tab_rec_bak(v_path )
select v_path from tab_rec;删除:
delete a from tab_rec as a inner join tab_rec_bak as b on a.v_path=b.v_path;
Empty set (0.00 sec)mysql> select * from tab_rec;
+--------+
| v_path |
+--------+
| 1 |
| 2 |
+--------+
2 rows in set (0.00 sec)mysql> LOCK TABLES tab_rec WRITE,tab_rec_bak WRITE;
Query OK, 0 rows affected (0.00 sec)mysql> insert into tab_rec_bak(v_path) select v_path from tab_rec;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0此时,切换至MYSQL命令窗口二
mysql> insert into tab_rec values (4),(5);
由于锁有关系,这个命令会等待解锁中返回窗口一mysql> delete from tab_rec;
Query OK, 2 rows affected (0.06 sec)mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)mysql> select * from tab_rec; -- 这里看到窗口二的记录已插入。
+--------+
| v_path |
+--------+
| 4 |
| 5 |
+--------+
2 rows in set (0.00 sec)mysql> select * from tab_rec_bak;
+--------+
| v_path |
+--------+
| 1 |
| 2 |
+--------+
2 rows in set (0.00 sec)mysql>