MySQL-将查询结果导出到文件

LIfe0115

已于 2024-09-02 17:32:44 修改

阅读量3.3k

点赞数

文章标签： mysql 数据库

于 2022-07-25 19:50:42 首次发布

本文链接：https://blog.csdn.net/lif29103/article/details/125981921

版权

首先使用下面的命令查看数据库的存储路径。如果查出的 secure_file_priv 是 null 的时候就证明在 my.ini 文件里面没有配置写出路径。

show variables like '%secure%';

secure_file_priv 参数是一个只读参数，需要修改my.cnf文件（在[mysqld]下面）:

Windows：C:\ProgramData\MySQL\MySQL Server 5.6
Linux：/etc/my.cnf

这时候就可以在 mysql.ini 文件的 [mysqld] 代码下增加

secure_file_priv= ”C:/ProgramData/MySQL/MySQL Server 5.6/Uploads/”

再重启 mysql 就可以了。然后在导出的地址下面写上刚才配置的这个地址,执行

select realName,count(1) from t_s_log 
where date_format(createtime,'%Y-%m-%d') >= '2021-06-01' and date_format(createtime,'%Y-%m-%d') <= '2021-06-30'
and operatetype = 1 and logcontent like '%登录成功'
group by userid
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.6/Uploads/userlogin.csv' #指定导出路径一定要和上面配置的一致
CHARACTER SET gbk 	#将查询结果转换为GB2312格式
FIELDS TERMINATED BY ','	#指定列之间分隔符
LINES TERMINATED BY '\r\n'	#'\r'换行符, '\n'新行符

就可以了。

ok,导出成功。

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

每使用一次该功能将做一次记录，便于后续参考

2024-08-22

select tst_status.typename as '状态' , tst_handover.typename as '交接状态' , tc.cino as 'BSS No' , tc.ctgCircuitNo as 'CTG Circuit No' , tc.orderNo as 'Order No',
tc._endcustomer as 'End Customer' , tc.installCity as 'Install City' , tc._serviceDeliveryAddress as 'Service Delivery Address' , tc.contractTerm as 'Contract Term',
tc.productType as 'Product Type' , tc.pm as 'PM' , tc.pmphone as 'PM Phone' , tc.pmemail as 'PM Email' , tcp._projectname as 'Project Name' , 
concat(if(instr(tcp._projecttype,'data0') > 0 ,'综合及其它',''),',',if(instr(tcp._projecttype,'data1') > 0 ,'硬件维保项目',''),',',
if(instr(tcp._projecttype,'data2') > 0 ,'软件维护项目',''),',',if(instr(tcp._projecttype,'data3') > 0 ,'维保服务项目',''),',',
if(instr(tcp._projecttype,'data4') > 0 ,'License服务项目','')) as 'Project Type',
tcp.servicetype as 'Service Type' , tcu.customername as 'Customer Name' , tcu.customermanager as 'Customer Manager' , tcu.customermanagerphone as 'Customer Manager Phone',
tcu.customermanageremail as 'Customer Manager E-Mail'
from t_cim_pi tcp
left join t_cim tc on tcp.id = tc.id
left join t_customer tcu on tc.customerEntity_ID = tcu.id
--  交接完成
left join t_s_type tst_handover on tc.handoverStatus_ID = tst_handover.id
left join t_s_type tst_status on tc.ciStatus_ID = tst_status.id
where tst_handover.typecode = 'finished'

2024-09-02

select tc.id as '配置项id',tc.createTime as '交接时间' , tc.cino as 'BSS订单号' , tc.orderNo as '订单流水号', tc.ctgCircuitNo as '线路编码' , tcp._projectName as '项目名称' ,
--  拼接项目类型 start
concat(if(instr(tcp._projecttype,'data0') > 0 ,'综合及其它',''),',',if(instr(tcp._projecttype,'data1') > 0 ,'硬件维保项目',''),',',
if(instr(tcp._projecttype,'data2') > 0 ,'软件维护项目',''),',',if(instr(tcp._projecttype,'data3') > 0 ,'维保服务项目',''),',',
if(instr(tcp._projecttype,'data4') > 0 ,'License服务项目','')) as '项目类型' ,
--  拼接项目类型 end
tcu.customername as '客户名称' , tc.productType as '产品类型' , tc._endcustomer as '终端客户' , tc._serviceDeliveryAddress as '客户地址' ,
 tc.pm as '项目经理'  ,tc.pmphone as '项目经理电话' , tc.pmemail as '项目经理邮件' , tcu.customermanager as '客户经理' ,
 tcu.customermanagerphone as '客户经理电话',tcu.customermanageremail as '客户经理邮箱',
tc.contractTerm as '合同到期', tc.installCity as '安装城市',tcp.servicetype as '服务类型' , 
--  项目信息字段
--  项目类型判断 start
(case 
	when tpps.projecttype = 'data0' then '综合及其它'
	when tpps.projecttype = 'data1' then '硬件维保项目'
	when tpps.projecttype = 'data2' then '软件维护项目'
	when tpps.projecttype = 'data3' then '维保服务项目'
	when tpps.projecttype = 'data4' then 'License服务项目'
	else tpps.projecttype
end) as '项目信息-项目类型',
--  项目类型判断 end
	tpp.equipmentModel as '设备型号',
--  设备条码判断 start
(case 
	when tpps.projecttype = 'data0' then ifnull(tpp.bussinessDescription,'/')
	when tpps.projecttype = 'data1' then ifnull(tpp.serialNumber,'/')
	when tpps.projecttype = 'data2' then ifnull(tpp.softwareType,'/')
	when tpps.projecttype = 'data3' then ifnull(tpp.serviceDescription,'/')
	when tpps.projecttype = 'data4' then ifnull(tpp.licenseDescription,'/')
	else '/'
end ) as '设备条码',
--  设备条码判断 end
tpp.manufacturers as '原厂',tpp.serviceProvider as '供应商名称',tpp.startTime as '维保起始时间',tpp.endTime as '维保到期时间'
from t_cim_pi tcp
left join t_cim tc on tcp.id = tc.id
left join t_customer tcu on tc.customerEntity_ID = tcu.id
-- 连接项目表查询
left join t_pi_project_supplier tpps on tc.id = tpps.cimid
left join t_pi_project_supplier_t_pi_project tppstpp on tpps.id = tppstpp.t_pi_project_supplier_id
left join t_pi_project tpp on tppstpp.piProjectEntityList_ID = tpp.id
--  交接完成
left join t_s_type tst_handover on tc.handoverStatus_ID = tst_handover.id
left join t_s_type tst_status on tc.ciStatus_ID = tst_status.id
where tst_handover.typecode = 'finished'
order by tc.id,tpps.projecttype,tc.createTime desc

select tc.createTime as 'createTime' , tc.cino as 'cino' , tc.orderNo as 'orderNo', tc.ctgCircuitNo as 'ctgCircuitNo' , tcp._projectName as '_projectName',
tcu.customername as 'customername' , tc.productType as 'productType' , tc._endcustomer as '_endcustomer' ,
 tc.pm as 'pm'  ,tc.pmphone as 'pmphone' , tc.pmemail as 'pmemail' , tcu.customermanager as 'customermanager' ,
 tcu.customermanagerphone as 'customermanagerphone',tcu.customermanageremail as 'customermanageremail',
tc.contractTerm as 'contractTerm', tc.installCity as 'installCity',tcp.servicetype as 'servicetype' , 
--  项目信息字段
--  项目类型判断 start
(case 
	when tpps.projecttype = 'data0' then 'data0'
	when tpps.projecttype = 'data1' then 'data1'
	when tpps.projecttype = 'data2' then 'data2'
	when tpps.projecttype = 'data3' then 'data3'
	when tpps.projecttype = 'data4' then 'data4'
	else tpps.projecttype
end) as 'projecttype',
--  项目类型判断 end
	tpp.equipmentModel as 'equipmentModel',
--  设备条码判断 start
(case 
	when tpps.projecttype = 'data0' then ifnull(tpp.bussinessDescription,'/')
	when tpps.projecttype = 'data1' then ifnull(tpp.serialNumber,'/')
	when tpps.projecttype = 'data2' then ifnull(tpp.softwareType,'/')
	when tpps.projecttype = 'data3' then ifnull(tpp.serviceDescription,'/')
	when tpps.projecttype = 'data4' then ifnull(tpp.licenseDescription,'/')
	else '/'
end ) as 'serialNumber',
--  设备条码判断 end
tpp.manufacturers as 'manufacturers',tpp.serviceProvider as 'serviceProvider',tpp.startTime as 'startTime',tpp.endTime as 'endTime'
from t_cim_pi tcp
left join t_cim tc on tcp.id = tc.id
left join t_customer tcu on tc.customerEntity_ID = tcu.id
-- 连接项目表查询
left join t_pi_project_supplier tpps on tc.id = tpps.cimid
left join t_pi_project_supplier_t_pi_project tppstpp on tpps.id = tppstpp.t_pi_project_supplier_id
left join t_pi_project tpp on tppstpp.piProjectEntityList_ID = tpp.id
--  交接完成
left join t_s_type tst_handover on tc.handoverStatus_ID = tst_handover.id
left join t_s_type tst_status on tc.ciStatus_ID = tst_status.id
where tst_handover.typecode = 'finished'
order by tc.id,tpps.projecttype,tc.createTime desc


INTO OUTFILE '/usr/local/mysql//pi_cp03.csv' 
CHARACTER SET gbk 	
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';