Mysql Load Data

SELECT INTO OUTFILE 导出

#导出数据，select...into outfile 的结果只包含了表数据，默认以 Tab 分隔，也可指定分隔符：
select * from evaluate_data into outfile "/Users/zhuanghd/Downloads/evaluate.sql";

#or
select * into outfile "/Users/zhuanghd/Downloads/evaluate.sql" from evaluate_data;

#指定分割符
select * from evaluate_data into outfile "/Users/zhuanghd/Downloads/evaluate.sql" fields terminated by ',';

#outfile '/path/file'，中的 path 需要有mysql的权限，否则会报错：

LOAD DATA INFILE 导入

#导入数据
load data infile "/Users/zhuanghd/Downloads/evaluate.sql" into table evaluate_data;

#指定分割符,前面指定的分隔符是 ','，load data 时也要指定该分隔符，否则也会报错：
load data infile "/Users/zhuanghd/Downloads/evaluate.sql" into table evaluate_data fields terminated by ',';
#导入完成如下提示：

遇到的错误

#导入导出的目录没有权限
select * from t into outfile "/root/backup/mysql/t.sql";
ERROR 1 (HY000): Can't create/write to file '/root/backup/mysql/t.sql' (Errcode: 13 - Permission denied)

#执行的时候提示错误，是因为没有配置secure-file-priv 导入导出的目录 或者是 导入导出的文件不在你所配置的secure-file-priv目录中，报错如下
MYSQL导出数据出现The MySQL server is running with the --secure-file-priv option



#操作：
找到mysql 的配置文件 my.cnf 增加一下配置（目录可以自己定义，但是需要有mysql的读写权限）
secure_file_priv = /Users/zhuanghd/Downloads


#如果数据被某种符号封闭着，需要指定 ‘ fields enclosed by ’,举例以及相应的操作如下 ：
cat e.sql 
"1669"  "Jim"   "Smith"

load data infile "/data/mysql/e.sql" into table e;
ERROR 1366 (HY000): Incorrect integer value: '"1669"' for column 'id' at row 1

load data infile "/data/mysql/e.sql" into table e fields enclosed by '"'; 
Query OK, 1 row affected (0.01 sec)
Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

mysqlimport

mysql 命令中有 load data ，mysql还有一个可执行文件 mysqlimport，这两个命令可以从文件中把格式化的数据导入数据库，如果有一个包含大数据量的文件，可以实现快速的数据导入。

mysqlimport 可以看做是为 LOAD DATA INFILE SQL语句提供了一个命令行界面，他们大部分的参数选项是一致的，由 SELECT into outfile 导出的文件，也可以由 mysqlimport 导入。

mysqlimport 需要的文件格式与 load data 差不多，有兴趣的同学可以去研究一下。这里给出它的基本语法：

mysqlimport -u root -pPassword [--local] dbname filename.txt [OPTION]
  • “Password”参数是root用户的密码，必须与-p选项紧挨着；
  • “--local”是在本地计算机中查找文本文件时使用的（指定 --local 后，文本文件可以放在任何地方进行导入，否则只能放在mysql的data目录下）；
  • “dbname”参数表示数据库的名称；
  • “filename.txt”参数指定了文本文件的路径和称，文件里的数据插入到文件名去掉后缀后剩余名字对应的表中；
“OPTION”为可选参数选项，其常见的取值有：

  • --fields-terminated-by=字符串：设置字符串为字段之间的分隔符，可以为单个或多个字符。默认值为制表符“\t”。

  • --fields-enclosed-by=字符：设置字符来括住字段的值，只能为单个字符。

  • --fields-optionally-enclosed-by=字符：设置字符括住CHAR、VARCHAR和TEXT等字符型字段，只能为单个字符。

  • --fields-escaped-by=字符：设置转义字符，默认值为反斜线“\”。

  • --lines-terminated-by=字符串：设置每行数据结尾的字符，可以为单个或多个字符，默认值为“\n”。

  • --ignore-lines=n：表示可以忽略前n行。