mysql中查询出来的数据放哪_mysql将查询结果导出

最新推荐文章于 2022-01-22 11:32:26 发布

原创最新推荐文章于 2022-01-22 11:32:26 发布 · 328 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql中查询出来的数据放哪

本文介绍了如何使用MySQL的SELECT...INTO OUTFILE语句来导出数据到CSV文件的方法，并展示了如何通过设置来允许导出操作及如何正确处理特殊字符以避免科学计数法的出现。

一、首先确认当前数据库设置导出是允许的

show variables like '%secure%';

如果是null则代表不允许，这时可以用命令修改，或者直接修改数据库配置文件my.ini，在其中添加secure-file-priv=‘C:/var/Mysql/data’，最后记得要重启mysql服务

select a.id,a.user_name,b.id,b.tran_id,b.money,b.status,b.PayType,from_unixtime(b.add_time,'%Y-%m-%d %H:%i:%S') from lzh_members a,lzh_member_payonline b where a.orgCode = 'bb2222' and a.id = b.uid and b.status = 1

into outfile '2018122001.csv'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY ‘ ” ’

LINES TERMINATED BY '\n';

e.g:

select a.id,CONCAT("'",a.user_name),b.id,CONCAT("'",b.tran_id),b.money,b.status,b.PayType,from_unixtime(b.add_time,'%Y-%m-%d %H:%i:%S') from lzh_members a,lzh_member_payonline b where a.orgCode = 'bb2222' and a.id = b.uid and b.status = 1 into outfile '2018122001.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

select a.id,CONCAT("'",a.user_name),b.id,b.withdraw_money,b.success_money,from_unixtime(b.deal_time,'%Y-%m-%d %H:%i:%S') from lzh_members a,lzh_member_withdraw b where a.orgCode = 'bb2222' and a.id = b.uid and b.withdraw_

status = 2 into outfile '2018122002.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

使用CONCAT("'",str)或者CONCAT("\'",str)，防止数字过长时导出显示科学计数。