工作中 MySQL 相关功能的使用

整理工作中使用 MySQL 解决需求过程中用到的相关功能。
目前包括内置函数和自定义函数的使用。
持续更新整理

内置函数

MySQL 已经内置了很多常用函数，下面列举了在工作中经常使用的几个函数。

通用函数

• REPLACE()：用新的字符串替换原来的字符串，区分大小写

  语法：REPLACE(string, from_string, new_string)

  实例：SELECT REPLACE("SQL Tutorial", "SQL", "HTML")

• DATE_FORMAT()：日期格式化

  语法：DATE_FORMAT(date,format)

  实例：SELECT DATE_FORMAT("2023-05-16", '%Y-%m-%d %H:%i:%s')

• CONCAT()：将多个表达式拼接。表达式可以是字符串、字段值等

  语法：CONCAT(expression1,expression2,…)

  实例：SELECT CONCAT("SQL ", "Tutorial ", "is ", "fun!")

• IFNULL()：在表达式为 NULL 时返回指定值

  语法：IFNULL(expression, alt_value)

  实例：SELECT IFNULL(NULL, "W3Schools.cn")

特殊需求函数

需求：获取 json 字段中的某个键的值，并且去除结果中的双引号（json 储存的键值是带双引号的，查询的结果也是带双引号的）。

• JSON_EXTRACT()： 读取 json 字段中指定 key 的值

  语法：JSON_EXTRACT(json_doc, path)

  实例：查询 json_info 字段中 key 为 name 的值： SELECT JSON_EXTRACT ( json_info, '$.name' ) FROM table

• JSON_UNQUOTE()：去掉 json 字段查询结果的双引号。

  语法： JSON_QUOTE(string)

  实例：SELECT JSON_UNQUOTE(JSON_EXTRACT ( json_info, '$.name' )) FROM table

需求：统计前七日的数据。使用 CURDATE 函数获取前七日的日期，再 join 其他表获取数据

• CURDATE()：返回当前日期，以 “YYYY-MM-DD” （字符串）或 YYYYMMDD（数字）形式返回。

  语法：CURDATE()

  实例：SELECT CURDATE()

• DATE_SUB()：从日期中减去时间/日期间隔，然后返回日期。

  语法：DATE_SUB(date, INTERVAL value interval)

  实例：`SELECT DATE_SUB(“2017-06-15”, INTERVAL 10 DAY)

// 获取前七日的日期
SELECT CURDATE() - INTERVAL 0 DAY AS date
  UNION SELECT CURDATE() - INTERVAL 1 DAY AS date
  UNION SELECT CURDATE() - INTERVAL 2 DAY AS date
  UNION SELECT CURDATE() - INTERVAL 3 DAY AS date
  UNION SELECT CURDATE() - INTERVAL 4 DAY AS date
  UNION SELECT CURDATE() - INTERVAL 5 DAY AS date
  UNION SELECT CURDATE() - INTERVAL 6 DAY AS date

需求：计算两个日期相差的分钟数，分钟要精确到小数，不需要四舍五入。使用 TIMESTAMPDIFF 函数计算两个日期相差的秒数，然后除以 60，再使用 TRUNCATE 函数对结果截取 3 位小数

• TIMESTAMPDIFF()：计算两个日期之间的差值。

  语法：TIMESTAMPDIFF(unit,begin,end)

  实例：SELECT TIMESTAMPDIFF(second, '2018-01-01', '2018-06-01')

• TRUNCATE()：数据截取指定位数。

  语法：TRUNCATE(number, decimals)

  实例：SELECT TRUNCATE(135.375, 2);

自定义函数

需求：将 Excel 中的数据导入到数据库表的 json 字段中。大概思路先将 Excel 中需要的数据导入到数据库中的一张临时表中，然后编写自定义函数将数据全部更新到目标表的 json 字段中。

# 自定义函数实现json字段数据填充
CREATE DEFINER=`root`@`%` FUNCTION `buildJson`() RETURNS int(20)
BEGIN
DECLARE i int(20) default 0;
DECLARE totalCount int(20) default (select count(*) from temp_table); # temp_table 对应Excel中的数据

# 循环更新目标表数据
WHILE i < totalCount do
	set i = i + 1;
	select sn into @val from temp_table where id = i;
	UPDATE  target_table
	SET JSON_INFO = JSON_SET(
		JSON_INFO,
		'$.name',
		 (select name from temp_table where sn = @val),
		'$.age',
		( select age from temp_table where sn = @val)
	) 
	where sn = @val;
END WHILE;
return i;
END