279_DBA_备份_单表select_into_load

select into  outfile方式导出文件 目录权限是mysql:mysql 

SELECT [column1], [column2]...
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']
]
from TABLE WHERE ..

备注
FIELDS 
[TERMINATED BY 'string'] 表示每个列的分隔符,
[[OPTIONALLY] ENCLOSED BY 'char'] 对于字符串的包含符
[ESCAPED BY 'char'] 表示转义符]

[LINES [STARTING BY 'string'] 表示每行的开始符号,  [TERMINATED BY 'string'] 表示每行的结束符号]

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;
  
 FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' 
  LINES TERMINATED BY '\n' STARTING BY ''

也可以免交互的使用

mysql -uroot -P3306 -pxxxx -h192.168.1.1 -e "SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;  "

LOAD DATA INFILE 进行导入

[REPLACE | IGNORE]
INTO TABLE  tb_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[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,...]

如果涉及外键检查 可以关闭相关设置 SET @@foreign_key_checks =0 

LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet LINES TERMINATED BY '\r\n';
LOAD DATA INTO TABLE a IGNORE 1 LINES INFILE '/tmp/result.txt' 

mysqlimport

mysqlimport [options] db_name textfile 
mysqlimport --use-thread=2 test /tmp/a.txt  /tmp/b.txt