1、select into outfile可以导出数据到指定目录下的文件中
语法:
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT ... INTO OUTFILE 'file_name' [ CHARACTER SET charset_name] [export_options] export_options: [{FIELDS | COLUMNS} [TERMINATED BY 'string' ] [[OPTIONALLY] ENCLOSED BY 'char' ] [ESCAPED BY 'char' ] ] [LINES [STARTING BY 'string' ] [TERMINATED BY 'string' ] |
2、示例如下:
1)查看要导出数据的表t1
1 2 3 4 5 6 7 8 9 10 | mysql> select * from t1; + ------+--------+ | id | name | + ------+--------+ | 1 | wang | | 2 | steven | | 3 | tiger | | 4 | lilu | + ------+--------+ 4 rows in set (0.00 sec) |
2)查看导出的文件
1 2 3 4 5 | [mysql@oneproxy outfile]$ more t1. file 1, "wang" 2, "steven" 3, "tiger" 4, "lilu" |
3)创建要导入的表t2;
1 2 | mysql> create table t2 as select * from t1 where 1=2; Query OK, 0 rows affected (0.03 sec) |
4)将导出的数据导入t2
1 2 3 | mysql> LOAD DATA INFILE '/mysql/outfile/t1.file' INTO TABLE t2 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' ; Query OK, 4 rows affected (0.01 sec) Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 |
5)查看导入的数据
1 2 3 4 5 6 7 8 9 10 | mysql> select * from t2; + ------+--------+ | id | name | + ------+--------+ | 1 | wang | | 2 | steven | | 3 | tiger | | 4 | lilu | + ------+--------+ 4 rows in set (0.00 sec) |