mysql之随记sql

set names utf8;

mysqldump附带where条件

mysqldump -uroot -p --default-character-set=utf8 testdb testtab --where=" cstnet_id  like 'test2019%' " > test2019.sql

查询语句输出到文件

mysql  -uroot -p123456 
select * from test into outfile "test.txt" fields terminated by '\t' lines terminated by '\r\n' 

使用 mysql -e执行导出到文件的sql语句
mysql -uxx -pxx -e “query statement” db
例如:

mysql -uroot -p123456 -e "select * from test into outfile 'test.txt' fields terminated by ',' lines terminated by '\r\n'" testdb 

默认情况下， mysql -e导出的文件，列是用"\t"分隔，行是用"\r\n"分隔（dos），行是用"\n"分隔（unix）

SELECT DISTINCT r.rid,r.title,r.creator,r.last_editor,r.last_edit_time,t.creator as teamCreator,t.id,t.`name`,t.display_name,t.type from a1_resource r INNER JOIN vwb_team t on r.tid=t.id
 where r.last_edit_time<str_to_date('2019-4-30 0:0:0','%Y-%m-%d %H:%i:%s') 
			and r.last_edit_time>str_to_date('2019-04-19 0:0:0','%Y-%m-%d %H:%i:%s');
	into outfile "/home/user_edit10.xls" character set gbk;

如果导出报错：ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement，可以查询secure_file_priv 的值。

mysql> show global variables like '%secure_file_priv%';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.01 sec)

默认为NULL，表示限制不能导入导出，secure_file_priv 参数是只读参数，不能使用set global命令修改。可以打开my.cnf 或 my.ini，加入想要导出的目录，重启mysql，修改后再次执行，成功导出。