MySQL中的一些特殊函数

FIELD

FIELD(s,s1,s2...)

SELECT FIELD("c", "a", "b", "c", "d", "e"); -- 返回字符串 c 在列表值中的位置，索引位置从 1 开始，没有返回 0

FIND_IN_SET

FIND_IN_SET(s1,s2)

SELECT FIND_IN_SET("c", "a,b,c,d,e"); -- 使用,分割为列表，返回字符串 c 在列表中的位置，索引位置从 1 开始，没有返回 0

对于一对多的情况，可能还是left join的效率更高。

LEAD

LEAD(<expression>[,offset[, default_value]]) OVER (
    PARTITION BY (expr)
    ORDER BY (expr)
) 

select 
dt,
lead(1) over(order by dt desc) as num_1
from test;

dt为创建时间，按照dt倒序并取第二条，就是前一天数据

JSON_OBJECT

select JSON_OBJECT('userId',id,'userName',user_name) as user_json from `t_user`;

将每行数据转换为json对象字符串

select
  JSON_OBJECT(
    'userId',
    id,
    'userName',
    user_name,
    'roleInfo',
    SELECT
      JSON_OBJECT('roleId', r.id, 'roleName', r.role_name)
    FROM
      `t_role` r
    WHERE
      r.`id` = u.role_id
  ) as user_json
from
  `t_user` u
WHERE
  u.`id` = 1;

输出结果为

{
  "userId" : 1,
  "roleInfo" : {
    "roleId" : 1,
    "roleName" : "admin"
  },
  "userName" : "admin"
}

JSON_OBJECT 函数返回的值是 JSON 类型，不是普通字符串类型，使用 CAST 函数可以转为字符串类型

select
  JSON_OBJECT(
    'userId',
    id,
    'userName',
    user_name,
    'roleInfo',
    SELECT
      CAST(JSON_OBJECT('roleId', r.id, 'roleName', r.role_name) AS CHAR) -- 转为字符串
    FROM
      `t_role` r
    WHERE
      r.`id` = u.role_id
  ) as user_json
from
  `t_user` u
WHERE
  u.`id` = 1;

输出结果为

{
  "userId" : 1,
  "roleInfo" : "{\"roleId\": 1, \"roleName\": \"admin\"}", // 类似 JAVA 中的 JSON.toJSONString() 效果
  "userName" : "admin"
}

JSON_OBJECTAGG

select JSON_OBJECTAGG(id,`user_name`) as user_json from `t_user`;

将多行数据转换为一个json对象字符串

{
  "1": "admin",
  "2": "admin1"
}

JSON_ARRAYAGG

select JSON_ARRAYAGG(JSON_OBJECT("userId",id,"userName",`user_name`)) as user_json from `t_user`;

将多行数据转换为一个json数组字符串

[
  {
    "userId": 1,
    "userName": "admin"
  },
  {
    "userId": 2,
    "userName": "admin1"
  }
]

参考

MySQL 中 FIND_IN_SET 使用和性能
关于 find_in_set 的性能问题
mysql数据查询转换成json mysql转json函数