mysql数据导入和导出

发表于 | 分类于 ，
字数统计: 617 字

secure_file_priv对读写文件的影响

在对mysql数据进行导入和导出之前需要查看secure_file_priv(文件安全权限)参数的设置是否允许我们进行数据导入导出处理，可以通过

1
2
3
4
5
6
7
mysql> show global variables like '%secure_file_priv%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.00 sec)

来进行从查看，不同的查询结果value值有不同的含义:

  • 如果查询出的value=NULL,则说明当前mysql不允许进行数据导入导出
  • 如果查询出的value是一个路径值,比如D:/mysqlUpload/，说明导入导出行为只能在该文件夹下进行，子文件都不允许
  • 如果查询出的value是一个空串，说明导入导出动作可以在任何文件夹下进行

由于我没有设置过这个值，所以去查询该值的时候显示为NULL，应该是默认值。如果在value=NULL的状态下去执行LOAD DATA INFILE…INTO TABLE 和 SELECT … FROM … INTO OUTFILE的话，会出现报错：

1
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

设置secure_file_priv

  • windows下：修改my.ini 在[mysqld]内加入secure_file_priv并设置想要进行导入导出的工作路径或者使用空串来取消限制
  • linux下：修改my.cnf 在[mysqld]内加入secure_file_priv，赋值方式和在windows相同

由于我本地环境为windows且没有找到my.ini文件，所以就重新创建了一个，从网上找到默认配置后再进行修改的，也是可行的。修改完成后重启mysql服务就生效了。

进行数据的导入

1
2
mysql > LOAD DATA LOCAL INFILE 'D:/mysqlUpload/pet.txt' INTO TABLE pet FIELDS TERMINATED BY ','
      > LINES TERMINATED BY '\r\n';

参数说明：

  • LOAD DATA LOCAL INFILE [filepath] INTO TABLE [table]：将某个路径下的数据导入到某张表中
  • FIELDS TERMINATED BY：文件中每一行数据的各个字段的数据是由什么分隔符分割的,如果不写默认为TAB
  • LINES TERMINATED BY：每行之间的换行符，不同系统之间换行符是不同的，上述为windows下的

进行数据的导出

1
2
mysql > SELECT * FROM pet INTO OUTFILE  "D:/mysqlUpload/pet.txt"
      > FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

参数说明：

  • INTO OUTFILE [filepath]：将查询的数据导出到某个路径的文件中
  • FIELDS TERMINATED BY： 同数据的导入一样
  • OPTIONALLY ENCLOSED BY：输出的字段数据用什么包围，这里定义的是使用引号包围