【Mysql】业务视角下，SQL字段处理专题

日期字段格式转化

DATE_FORMAT(violation_date, '%Y-%m-%d') AS formatted_date

多条结果字段合并成一条数组

select id,group_concat(price separator '##') from goods group by id;

获取多个字段中，非空字段的值

COALESCE(column1,column2,column3)

多个字段字符串拼接

CONCAT(column1,column2,column3)

字段过滤html（mysql8以下）

REGEXP_REPLACE(answer, '<[^>]*>+', '')

提取Json串里的值

-- 例如：字段值为{"lng":"111.722685","lat":"40.797585"}
-- 提取方法为：
json_extract(coordinate_info,'$.lng'),json_extract(coordinate_info,'$.lat')

-- 同时去掉双引号的方法：
JSON_UNQUOTE(json_extract(coordinate_info,'$.lng'))

将多字段合并为Json串

-- 拼接一个json，比如：{'id':'123','name','张三'}
SeLeCt JSON_ARRAYAG(JSON_OBJECT('id',Id,'name',Name))

获取未来7天的日期

/**
 获取未来7天日期和星期x
**/
select *,CONCAT(day_name,'(',week_name,')') day_week_name
FROM
(
select DATE_FORMAT(weekday.day1,'%Y-%m-%d') day_name,
(
CASE DAYOFWEEK(weekday.day1)
	WHEN 1 THEN '星期日'
	WHEN 2 THEN '星期一'
	WHEN 3 THEN '星期二'
	WHEN 4 THEN '星期三'
	WHEN 5 THEN '星期四'
	WHEN 6 THEN '星期五'
	WHEN 7 THEN '星期六'
	ELSE '未知'
END
) week_name 
FROM
(
select DATE_SUB(NOW(),INTERVAL -1 DAY) day1 union
select DATE_SUB(NOW(),INTERVAL -2 DAY) day2 union
select DATE_SUB(NOW(),INTERVAL -3 DAY) day3 UNION
select DATE_SUB(NOW(),INTERVAL -4 DAY) day4 UNION
select DATE_SUB(NOW(),INTERVAL -5 DAY) day5 UNION
select DATE_SUB(NOW(),INTERVAL -6 DAY) day6 UNION
select DATE_SUB(NOW(),INTERVAL -7 DAY) day7
) weekday
) table1

坐标类，查询位置范围信息

-- 查询xx商场（坐标为：121.75977,60.819568）周边500m内的饭店记录

SELECT l.*, ST_Distance_Sphere(l.coordinates, POINT(121.75977,60.819568)) AS distance
FROM demo_locations l
HAVING distance <= 500

-- distance <= 500 代表：500米半径范围内.

坐标类，point类型提取经纬度数值

SELECT ST_X(location) AS longitude, ST_Y(location) AS latitude; 
-- 其中，ST_X函数返回POINT类型的坐标经度，ST_Y函数返回坐标纬度

用心沉淀，持续完善更新，有好的写法和需求，欢迎留言交流 ~