MySQL 脚本执行SQL语句：导出数据

查询MySQL指定导出路径

SHOW VARIABLES LIKE 'secure_file_priv';

修改--secure-file-priv （谨慎操作）

修改配置文件my.cnf，重启服务

secure_file_priv值	说明
NULL	禁止文件的导入导出
''	（空字符串）允许所有文件的导入导出
一个特定的路径地址	只有该路径地址下的文件可以导入导出到mysql

 

编辑可执行脚本 query_script.sql

保存为csv文件

SELECT
	'车牌号',
	'设备号',
	'sim卡号',
	'组织',
	'安装时间',
	'已维保时间',
	'剩余维保时间',
	'设备状态',
	'在线/离线时间' UNION ALL # 添加csv列名
SELECT
	c.car_num "车牌号",
	d.dev_no "设备号",
	t_sim.sim_no "sim卡号",
	o.dept_name "组织",
	d.use_date "安装时间",
	TIMESTAMPDIFF(
		DAY,
		d.use_date,
	now()) "已维保时间",
	TIMESTAMPDIFF(
		DAY,
		now(),
	DATE_ADD( d.use_date, INTERVAL 3 YEAR )) "剩余维保时间",
	ds.message_type "设备状态",
	CONCAT(
		FLOOR(
		ds.duration / ( 24 * 60 * 60 * 1000 )),
		'天 ',
		FLOOR((
			ds.duration % ( 24 * 60 * 60 * 1000 )) / ( 60 * 60 * 1000 )),
		'时 ',
		FLOOR((
			ds.duration % ( 60 * 60 * 1000 )) / ( 60 * 1000 )),
		'分 ',
		FLOOR(( ds.duration % ( 60 * 1000 )) / 1000 ),
		'秒' 
	) AS "在线/离线时间" 
FROM
	t_device_manage d
	INNER JOIN t_car c ON c.device_id = d.id
	LEFT JOIN sys_dept o ON o.dept_id = c.dept_id
	LEFT JOIN (
	SELECT
		s1.device_no,
		s1.duration,
		s.message_type 
	FROM
		t_device_state s
		INNER JOIN (
		SELECT
			s.device_no,
			CAST(( unix_timestamp( NOW( 3 ))* 1000 - max( s.`timestamp` )) AS UNSIGNED ) AS duration,
			max( s.`timestamp` ) AS `timestamp` 
		FROM
			t_device_state s 
		GROUP BY
			s.device_no 
		) s1 ON s1.device_no = s.device_no 
		AND s1.`timestamp` = s.`timestamp` 
	) ds ON ds.device_no = d.dev_no
	LEFT JOIN t_sim ON d.sim_id = t_sim.id 
WHERE
	find_in_set( 1, o.ancestors ) 
	OR o.dept_id = 1 INTO OUTFILE '/var/lib/mysql-files/carInfo.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

 

保存为txt文件

SELECT
	'车牌号',
	'设备号',
	'sim卡号',
	'组织',
	'安装时间',
	'已维保时间',
	'剩余维保时间',
	'设备状态',
	'在线/离线时间' UNION ALL # 添加csv列名
SELECT
	c.car_num "车牌号",
	d.dev_no "设备号",
	t_sim.sim_no "sim卡号",
	o.dept_name "组织",
	d.use_date "安装时间",
	TIMESTAMPDIFF(
		DAY,
		d.use_date,
	now()) "已维保时间",
	TIMESTAMPDIFF(
		DAY,
		now(),
	DATE_ADD( d.use_date, INTERVAL 3 YEAR )) "剩余维保时间",
	ds.message_type "设备状态",
	CONCAT(
		FLOOR(
		ds.duration / ( 24 * 60 * 60 * 1000 )),
		'天 ',
		FLOOR((
			ds.duration % ( 24 * 60 * 60 * 1000 )) / ( 60 * 60 * 1000 )),
		'时 ',
		FLOOR((
			ds.duration % ( 60 * 60 * 1000 )) / ( 60 * 1000 )),
		'分 ',
		FLOOR(( ds.duration % ( 60 * 1000 )) / 1000 ),
		'秒' 
	) AS "在线/离线时间" 
FROM
	t_device_manage d
	INNER JOIN t_car c ON c.device_id = d.id
	LEFT JOIN sys_dept o ON o.dept_id = c.dept_id
	LEFT JOIN (
	SELECT
		s1.device_no,
		s1.duration,
		s.message_type 
	FROM
		t_device_state s
		INNER JOIN (
		SELECT
			s.device_no,
			CAST(( unix_timestamp( NOW( 3 ))* 1000 - max( s.`timestamp` )) AS UNSIGNED ) AS duration,
			max( s.`timestamp` ) AS `timestamp` 
		FROM
			t_device_state s 
		GROUP BY
			s.device_no 
		) s1 ON s1.device_no = s.device_no 
		AND s1.`timestamp` = s.`timestamp` 
	) ds ON ds.device_no = d.dev_no
	LEFT JOIN t_sim ON d.sim_id = t_sim.id 
WHERE
	find_in_set( 1, o.ancestors ) 
	OR o.dept_id = 1 INTO OUTFILE '/var/lib/mysql-files/carInfo.txt';

 

执行sql脚本

mysql -u 《用户名》 -p 《数据库名》< query_script.sql

示例：mysql -u root -p BDCloud < query_script.sql

 

参考文章

【1】https://www.cnblogs.com/syw20170419/p/16783573.html