[MYSQL/日期/时间] MYSQL 函数篇

概述：MYSQL 函数篇

1 字符串处理函数

replace({sourceColumn} ,{oldValue}, {newValue}) : 字符串替换

  • 字符串替换 : replace({sourceColumn} ,{oldValue}, {newValue})
-- demo 1
select 
  replace('hello {name}!', '{name}', 'google') -- hello google!
  , replace('VA2.14', 'VA', '') as r1 -- '2.14'

-- demo 2
update bdp.config_datasource_info 
set datasource_instance = replace(datasource_instance, 'test', 'uat')
where 1 = 1 
	and `key` in ( 
 'KAFKA_HOST_BIGDATA' ,'KAFKA_HOST_COMMON' 
 ,'MYSQL_HOST_BIGDATA','MYSQL_HOST_COMMON','MYSQL_PASSWORD_BIGDATA','MYSQL_PASSWORD_COMMON_CDC'    
 ,'MYSQL_PORT_BIGDATA','MYSQL_PORT_COMMON','MYSQL_USERNAME_BIGDATA','MYSQL_USERNAME_COMMON_CDC'
 ,'OBS_AK_DLIFLINK','OBS_BUCKET_DLIFLINK','OBS_ENDPOINT_DLIFLINK','OBS_SK_DLIFLINK'
 ,'REDIS_HOST_BIGDATA','REDIS_PASSWORD_BIGDATA','REDIS_PORT_BIGDATA' 
);

JSON 函数

  • MySQL 提供了丰富的 JSON 函数，用于操作和处理 JSON 数据类型。

这些函数覆盖了 JSON 数据的创建、查询、修改、删除和格式化等常见操作，可根据具体需求选择合适的函数进行数据处理。

如下示例中，如无特殊说明，则支持 MYSQL 5.7+

  • 推荐文献

创建 JSON 数据

  • JSON_ARRAY()：创建一个 JSON 数组，参数为数组元素。
SELECT JSON_ARRAY(1, 'a', TRUE);  -- 返回 [1, "a", true]
  • JSON_OBJECT()：创建一个 JSON 对象，参数为键值对。
SELECT JSON_OBJECT('name', 'John', 'age', 30);  -- 返回 {"name": "John", "age": 30}

提取 JSON 数据

  • JSON_EXTRACT()：从 JSON 文档中提取指定路径的值。
SELECT JSON_EXTRACT('{"a": 1, "b": {"c": 2}}', '$.b.c');  -- 返回 2
  • -> 和 ->> 操作符：
    • 语法糖，等价于 JSON_EXTRACT() 和 JSON_UNQUOTE(JSON_EXTRACT())。
SELECT dataColumn->'$.name' FROM tableName;  -- 提取 JSON 字段中的 "name" 值（带引号）

SELECT dataColumn->>'$.name' FROM tableName;  -- 提取并去除引号

未亲测

修改 JSON 数据

  • JSON_SET()：更新或插入 JSON 文档中的指定路径的值。
SELECT JSON_SET('{"a": 1}', '$.b', 2);  -- 返回 {"a": 1, "b": 2}
  • JSON_REPLACE()：仅替换已存在的值，不存在则不操作。
SELECT JSON_REPLACE('{"a": 1}', '$.a', 3);  -- 返回 {"a": 3}
  • JSON_ARRAY_APPEND()：在数组末尾追加元素。
SELECT JSON_ARRAY_APPEND('[1, 2]', '$', 3);  -- 返回 [1, 2, 3]
  • JSON_ARRAY_INSERT()：在数组指定位置插入元素。
SELECT JSON_ARRAY_INSERT('[1, 3]', '$[1]', 2);  -- 返回 [1, 2, 3]

删除 JSON 数据

  • JSON_REMOVE()：删除指定路径的值。
SELECT JSON_REMOVE('{"a": 1, "b": 2}', '$.b');  -- 返回 {"a": 1}

检查 JSON 数据

  • JSON_CONTAINS(target, candidate [, path])：检查 JSON 文档是否包含指定值或子文档。
  • target: 待搜索的目标 JSON 文档。
  • candidate: 在目标 JSON 文档中要搜索的值。
  • path（可选）： 路径表达式，指示在哪里搜索候选值。
select 
  JSON_CONTAINS('[1, 2, 3]', '2') as j0  -- 返回 1:包含
  , JSON_CONTAINS('[ "key" , "world" ]', '\"world\"') as j1 -- 1 : 包含
  , JSON_CONTAINS('[ "key" , "world" ]', '\"world1\"') as j2 -- 0 : 不包含
  , JSON_CONTAINS('{"key" : "world" }', '\"world\"', '$.key') as j3 -- 1 : 包含
  , JSON_CONTAINS('{"key" : "world" }', '\"world\"', '$.key2') as j3 -- NULL : 不包含
  • JSON_OVERLAPS()：检查两个 JSON 文档是否有交集。

overlaps: n/vt. 重叠

MySQL 8.0.17+ 才支持此函数。

SELECT JSON_OVERLAPS('[1, 2]', '[2, 3]');  -- 返回 1（有交集）

获取 JSON 信息

  • JSON_KEYS()：获取 JSON 对象的所有键。
SELECT JSON_KEYS('{"a": 1, "b": 2}');  -- 返回 ["a", "b"]
  • JSON_LENGTH()：计算 JSON 对象或数组的长度。
SELECT JSON_LENGTH('[1, 2, 3]');  -- 返回 3

格式化 JSON 数据

  • JSON_PRETTY()：将 JSON 数据格式化为更易读的形式。
SELECT JSON_PRETTY('{"a": 1, "b": {"c": 2}}');
-- 输出：
-- {
--   "a": 1,
--   "b": {
--     "c": 2
--   }
-- }

未亲测

案例: 字符串转浮点数

select 
  replace('V1.25', 'V', '') as r1 -- '1.25'
  , CAST( replace('V1.25', 'V', '') AS DECIMAL(10, 2)) as r2 -- 1.25

2 日期时间函数

STR_TO_DATE(datetimeStr, format) : 日期时间字符串转为**日期时间*

  • 在MySQL中，可以使用STR_TO_DATE()函数将日期字符串转换为日期时间格式。
  • 假设您有一个形式为yyyyMMdd的日期字符串，您可以使用以下SQL语句进行转换：
SELECT STR_TO_DATE('20230325', '%Y%m%d') AS formatted_date;

这里的%Y%m%d是格式化字符串，%Y代表4位数的年份，%m代表月份，%d代表日。

  • 如果您想要包含时间，并且假设您的字符串格式是yyyyMMddHHmmss，您可以这样做：
SELECT STR_TO_DATE('20230325142345', '%Y%m%d%H%i%s') AS formatted_datetime;

这里%H代表小时，%i代表分钟，%s代表秒。这将返回一个日期时间值。

UNIX_TIMESTAMP(datetime) : 日期时间转秒级时间戳

  • 日期时间字符串转时间戳 (10位、秒级)
-- 日期转时间戳 (10位、秒级)
SELECT UNIX_TIMESTAMP( STR_TO_DATE('20230325', '%Y%m%d') ) AS formatted_date;
-- 1679673600

FROM_UNIXTIME(unix_timestamp, format) : 时间戳转指定格式的日期时间字符串

-- FROM_UNIXTIME(unix_timestamp, format)
SELECT FROM_UNIXTIME(1679673600,'%Y-%m-%d %H:%i:%S')

案例: DateTime 转 毫秒级时间戳

  • 在MySQL中，将DATETIME类型转换为13位毫秒级时间戳，可以通过以下方法实现：

  • NOW(x) 函数的计算结果为 DateTime 类型，故如下示例中以该函数作为 datetime 的 demo 列

NOW(3) : 秒级精度 / NOW(3) : 毫秒级精度 / NOW(6) : 微秒级精度

方法1：利用UNIX_TIMESTAMP()函数结合毫秒计算

UNIX_TIMESTAMP(datetime)会返回datetime对应的秒级时间戳（10位），乘以1000后得到毫秒级（13位）。如果需要更精确的毫秒（比如DATETIME包含微秒），可以结合MICROSECOND()函数：

-- 基础转换（无毫秒精度时）
SELECT UNIX_TIMESTAMP(NOW(3)) * 1000 AS millisecond_timestamp;
-- 1764730465990.000

-- 包含微秒精度时（MySQL 5.6.4+支持DATETIME(fsp)）
SELECT UNIX_TIMESTAMP( NOW(3) ) * 1000 + MICROSECOND( NOW(3) ) / 1000 AS millisecond_timestamp
-- 1764730484998.0000

方法2：使用TO_SECONDS()函数（MySQL 5.5+）

TO_SECONDS(datetime)返回从公元0年到datetime的总秒数，减去TO_SECONDS('1970-01-01 00:00:00')得到UNIX秒级时间戳，再乘以1000：

SELECT (TO_SECONDS( NOW(3) ) - TO_SECONDS('1970-01-01 00:00:00')) * 1000 + MICROSECOND( NOW(3) ) / 1000
-- 1764759294973.0000

注意事项

  1. MySQL的DATETIME默认精度是秒，若需毫秒/微秒，需显式指定精度（如DATETIME(3)表示毫秒，DATETIME(6)表示微秒）。

NOW(3) : 秒级精度 / NOW(3) : 毫秒级精度 / NOW(6) : 微秒级精度
以上方法可根据MySQL版本和时间精度需求选择使用。

3 条件函数

  • 推荐/参考文献

IF(expr1, trueResultExpr, falseResultExpr)

  • 若expr1 == TRUE， 则：返回值为 trueResultExpr；

  • 若expr1 == FALSE，则：返回值为 falseResultExpr；

  • 示例：

select if(sva=1,"男","女") as ssva from taname where id = '111';

CASE WHEN语句

  • 作为表达式的 IF ，也可使用 CASE WHEN 来实现
SELECT
CASE sva WHEN 1 THEN "男" ELSE "女" END 
AS ssva FROM taname WHERE id = '1';

Y 推荐文献

X 参考文献

posted @ 2025-04-07 20:53  千千寰宇  阅读(64)  评论(0)    收藏  举报