MySQL 表备份

er232112

                      

• 没有事先建表，直接备份，但是MySQL不支持select into来备份表，但是可以使用如下建表语句来备份：
  
  

1 2	create table user_backup (select * from user); select * from user_backup;

1 2 3 4 5 6 7 8

[SQL]create table user_backup (select * from user); Affected rows: 22 Time: 0.129s [SQL] select * from user_backup; Affected rows: 0 Time: 0.001s

• 事先已有表，从数据源的表里copy数据出来插入目标表里
  
  

1 2 3 4

mysql> insert into user_temp (select * from user); Query OK, 22 rows affected Records: 22  Duplicates: 0  Warnings: 0 mysql> select * from user_temp;

• 清空表数据，保留表结构
  
  

1 2 3 4

mysql> truncate table user_temp; Query OK, 0 rows affected mysql> select * from user_temp; Empty set

• 另外，SQL Server支持 select into，但是不支持 select into in
  
  

1 2 3

use hunlian; select * into member_backup in hunlian-test.mdb from hunlian.dbo.member;

1 2 3 4 5 6 7 8 9 10 11

[SQL]use hunlian Affected rows: 0 Time: 0.007s [SQL] select * into member_backup in hunlian-test.mdb from hunlian.dbo.member [Err] 42000 - [SQL Server]关键字 'in' 附近有语法错误。 Affected rows: 0

1 2 3 4 5 6 7 8 9

use hunlian; /*因两表含有相同的id字段，直接select * from会冲突*/ select member.name,member.mail,userinfo.city,userinfo.birthday into member_backup from member inner join userinfo on member.id = userinfo.id; select * from member_backup; select * from member inner join userinfo on member.id = userinfo.id;