mysql导入导出文件

mysql导入文件

向mysql 导入csv文件时报错：

Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement	0.000 sec

mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+

只有在/var/lib/mysql-files/目录下的文件才可以将csv文件导入数据库中
于是将文件移到该目录下

导入文件后发现中文乱码

CREATE TABLE `transaction` (  
  `card` varchar(40) , 
  `mid` varchar(255) ,  
  `posEntryType` varchar(255) ,  
  `quickPayProdName` varchar(255) ,  
  `priAcctNoPart` varchar(255) COLLATE utf8_bin  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

讲默认字符集设为utf8 也没用;

解决方法： 在导入语句中加入 ：CHARACTER SET utf8

LOAD DATA INFILE '/var/lib/mysql-files/2018-06-06_new.csv'
INTO TABLE transaction CHARACTER SET utf8
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'

mysql导出文件

mysql> select qq from givin where createtime>'2018-10-09 00:00:00' and createtime<'2018-10-09 10:23:54' into outfile './apps.csv';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

向上面这样会报错；
应该放在上文提示的 /var/lib/mysql-files/目录下

mysql> select qq from givin where createtime>'2018-10-09 00:00:00' and createtime<'2018-10-09 10:23:54' into outfile '/var/lib/mysql-files/apps.csv';

如果不想放到规定的/var/lib/mysql-files/目录下，可以使用mysql -e ，之后随便指定一个路径就可以啦

upsmart@upsmart-12:~$ mysql -uroot -proot yyy -e"select qq from givin where createtime>'2018-10-09 00:00:00' and createtime<'2018-10-09 10:23:54';" > ./zjlo.csv