mysql将查询结果导出

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

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)，防止数字过长时导出显示科学计数。

巴特西

mysql将查询结果导出

最新文章

热门文章