1.select into outfile 导出 LOAD DATA INFILE into 导入
语法:
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']
]
例:
SELECT b.* from table1 a left join table2 b on a.id= b.id INTO OUTFILE '/tmp/test.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
导入:
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[FIELDS
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char' ]
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...)]
例子:
LOAD DATA INFILE '/tmp/test.txt' IGNORE INTO TABLE table3
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (a,b,c)
mysqldump 导出
mysqldump --complete-insert --skip-comments --skip-add-drop-table --skip-add-locks --skip-disable-keys --insert-ignore --single-transaction -t -u root -p -P 3306 -h 127.0.0.1 database1 table1>table1.sql
-t 不导出表结构
-d 只导出表结构
-F 导出后刷新生成新的binlog文件
--complete-insert 会把insert全部的列写出来
--skip-comments 去除注释信息
--skip-add-drop-table 不创建删除表语句
--skip-add-locks 不加锁
--insert-ignore 忽略插入 对应--replace 替换插入
--single-transaction 保证导出的一致性状态 不锁表 innodb多版本
--where=“id>1” 添加where条件
--master-data 记录主从信息
--all-databases 导出所有库
--triggers --routines 导出触发器存储过程自定义函数
--no-create-info 导出的sql中不包含drop table,create table
--delayed-insert 采用延时插入方式(INSERT DELAYED)导出数据。
--force 在导出过程中忽略出现的SQL错误。
--hex-blob 使用十六进制格式导出二进制字符串字段。如果有二进制数据最好使用该选项。