MySQL表数据导入与导出

LOAD DATA INFILE

LOAD DATA INFILE语句可以快速将文本记录导入到表中， SELECT ... INTO OUTFILE可以将表中数据导入到文件中，两者的FIELDS、LINES子句语法相同。
执行语句的用户需要有FILE权限，且mysql用户对数据文件可读。
mysqlimport也可以实现将文本文件导入到表中，mysqlimport也是向服务器调用LOAD DATA INFILE语句，--local选项可以读取客户端的文件。

LOAD DATA INFILE语法如下：

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

• LOCAL
  当指定local时，客户端将读取文件发送到服务器操作系统的临时目录(非MySQL临时目录)，如空间不如将导致执行失败。如未指定local，则会从服务器读取，相对路径是相对于data_dir的路径。

  LOAD DATA INFILE:当数据非法或数据唯一索引冲突将导致执行失败。
  LOAD DATA LOCAL INFILE：因为服务器端无法终止客户端文件发送，当数据非法或数据唯一索引冲突时将会警告忽略错误继续，类似IGNORE。

• REPLACE
  出现数据唯一索引冲突将会替代现有的记录

• IGNORE
  出现数据唯一索引冲突将会被忽略。如果IGNORE与REPLACE都没指定，则取决于LOCAL选项。如果local也没有指定，则重复数据之后的文件数据将被不会执行，如指定local，则会忽略冲突继续执行。

• IGNORE number LINES
  指定跳过文件开头的几行记录

LOAD DATA INFILE用法：

默认load data infile的格式(不指定FIELDS，LINES选项)
FIELDS TERMINATED BY '\t'
       ENCLOSED BY ''
       ESCAPED BY '\\'
LINES TERMINATED BY '\n'
STARTING BY ''

导入csv文件
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 LINES;

导入包含指定列的文件
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ','
  (column2,column3);

导入文件设定指定列的值
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ','
  (column1,column2)
  set column3 = CURRENT_TIMESTAMP;;

SELECT ... INTO OUTFILE

SELECT ... INTO OUTFILE会将查询结果写入到服务器，因此需要有FILE权限，同时需要对指定的路径有写入权限，且指定文件不能是服务器已有文件。
客户端生成指定结果文件可使用mysql -e "SELECT ..." > file_name

SELECT ... INTO OUTFILE与LOAD DATA INFILE的FIELDS、LINES子句语法相同。

OPTION选项含义如下：

• FIELDS TERMINATED BY 'value'
  设置字段之间的分隔字符，可以为单个或多个字符，默认为制表符'\t'

• FIELDS [OPTIONALLY] ENCLOSED BY 'value'
  设置字段的包围字符，只能为单个字符

• FIELDS ESCAPED BY 'value'
  设置转义字符，只能为单个字符，默认值为‘\’

• LINES STARTING BY 'value'
  设置每行数据开头的字符，可以为单个或多个字符，默认情况下不使用任何字符

• LINES TERMINATED BY 'value'
  设置每行数据结尾的字符，可以为单个或多个字符，默认值为'\n',WINDOWS回车换行符为'\r\n'

导出csv格式的文件
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

mysqldump -T path dbname tbl_name 也可以生成文本文件，在path目录生成一个tbl_name.sql表定义文件和tbl_name.txt文件表数据文件，可以指定fields与lines选项指定生成数据文件的格式。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29733787/viewspace-2015673/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29733787/viewspace-2015673/