mysql 查询导出(txt,csv,xls)
１ 简介
工作中产品经常会临时找我导出一些数据，导出mysql查询结果数据有几种方法，下面介绍３种．
① mysql -u -p -e "sql" db > filepath
② echo "sql" | login > filepath
③ mysql login; use db; select * into outfile "filepath" from tb condition;
２ 实例讲解
① mysql -uroot -p123456 -e "select * from tb_user where id = 1" testdb > ~/wbwcachedata/tb_user.txt
mysql -uroot -p123456 -e "select * from tb_user where id = 1" testdb > ~/wbwcachedata/tb_user.csv
mysql -uroot -p123456 -e "select * from tb_user where id = 1" testdb > ~/wbwcachedata/tb_user.xls
个人比较喜欢导出为csv，因为xls经常会出现几个字段合到了一列的情况．导出csv之后，以制表符tab为分隔点即可转成excel．
另外如果出现中文乱码，可直接用命令转码 iconv -futf8 -tgb2312 -otb_user.xls tb_user1.xls．或者直接用文件软件转码即可（excel转ANSI码）．
② echo "select * from tb_user where id = 1" | mysql -uroot -p123456 > ~/wbwcachedata/mysqlexport0327-01.csv
错误提示： ERROR 1046 (3D000) at line 1: No database selected
没关系，我们在sql中加上use db即可:
echo "use testdb; select * from tb_user where id = 1;" | mysql -uroot -p123456 > ~/wbwcachedata/mysqlexport0327-01.csv
③ 登录mysql
mysql -uroot -p
执行sql
select * into outfile '~/wbwcachedata/mysqlexport0327-02.csv' from tb_user where id = 1;
错误提示： ERROR 1046 (3D000): No database selected
记得选择数据库
use testdb;
select * into outfile '~/wbwcachedata/mysqlexport0327-02.csv' from tb_user where id = 1;
错误提示： ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
此时有两种解决办法，第一种是改mysql的my.ini的secure-file-priv路径重启mysql．
第二种是
执行 show variables like '%secure%';
返回
+--------------------------+-----------------------+
| Variable_name | Value |
+--------------------------+-----------------------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /var/lib/mysql-files/ |
+--------------------------+-----------------------+
发现mysql允许导出路径为