MySQL导入导出数据

导入数据

LOAD DATA IN FILE

LOAD DATA IN FILE可以快速把数据从文件导入数据库中
- 语法：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name [, partition_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 | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]
    [SET col_name={expr | DEFAULT},
        [, col_name={expr | DEFAULT}] ...]

• 如果指定了LOCAL，会从客户端查找文件并发送到服务器，否则，文件必须存放在服务器上；
• 当遇到数据解释或键重复的错误时，LOAD DATA INFILE会直接退出，LOAD DATA LOCAL INFILE会警告但不会退出
• REPLACE和IGNORE关键字控制遇到键重复情况时采取的措施，REPLACE情况下当前记录会替换已存在的记录，IGNORE情况下会忽略当前记录。

LOAD DATA LOCAL INFILE 'D:/data/ABC_2.csv' INTO TABLE withholding_account_protocol
FIELDS TERMINATED BY ',' (bank_card_no,channel,user_name,protocol_id,real_name,mobile,bank_code,status,ip,create_date);

mysqlimport

mysqlimport和LOAD DATA IN FILE类似，不同的是它使用命令行形式

导出数据

SELECT … INTO

SELECT … INTO是LOAD DATA IN FILE的逆操作，用于从数据库中导出数据

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