没有事先建表,直接备份,但是MySQL不支持select into来备份表,但是可以使用如下建表语句来备份:
create table user_backup (select * from user);
select * from user_backup;
[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数据出来插入目标表里
mysql> insert into user_temp (select * from user);
Query OK, 22 rows affected
Records: 22 Duplicates: 0 Warnings: 0
mysql> select * from user_temp;
清空表数据,保留表结构
mysql> truncate table user_temp;
Query OK, 0 rows affected
mysql> select * from user_temp;
Empty set
另外,SQL Server支持 select into,但是不支持 select into in
use hunlian;
select * into member_backup in hunlian-test.mdb from hunlian.dbo.member;
[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
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;
转载于:https://blog.51cto.com/zhkpsty/1674083