MySQL表数据的导入与导出方法

本篇内容介绍了“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选项指定生成数据文件的格式。

“MySQL表数据的导入与导出方法”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！