MySQL如何从text类型存储的字典字符串中提取指定key的值

你的user_log字段为TEXT类型、存储JSON格式字符串，可根据你的MySQL版本选择对应方案提取"last_login"的取值：

方案1：原生JSON函数提取（推荐，适用于MySQL 5.7及以上版本） #

MySQL 5.7版本后内置了完整的JSON处理能力，只要字段内存储的是合法JSON格式，即使字段类型是TEXT，也可以先转换为JSON类型后直接取值，写法简洁且准确率高：

-- 标准写法
SELECT 
  user_id,
  JSON_UNQUOTE(JSON_EXTRACT(CAST(user_log AS JSON), '$.last_login')) AS last_login
FROM 你的业务表名;

也可以用->>语法糖简化写法，效果和上面完全一致：

-- 简化写法
SELECT 
  user_id,
  CAST(user_log AS JSON)->>'$.last_login' AS last_login
FROM 你的业务表名;

如果执行时报错，说明表内存在不合法的JSON脏数据，可以先执行以下语句排查出脏数据修复后再查询：

-- 排查非合法JSON格式的记录
SELECT user_id, user_log FROM 你的业务表名 WHERE JSON_VALID(user_log) = 0;

方案2：字符串截取（适用于MySQL版本低于5.7、存在少量不规则脏数据的场景） #

如果无法使用JSON函数，可以通过字符串定位+截取的方式提取值，不需要依赖JSON格式校验：
如果你的last_login固定为yyyy-MM-dd HH:mm:ss的19位时间格式，可以直接用固定长度截取，性能最好：

SELECT
  user_id,
  SUBSTRING(
    user_log,
    LOCATE('"last_login":"', user_log) + 14, -- 14是固定字符串"last_login":"的长度
    19
  ) AS last_login
FROM 你的业务表名;

如果last_login的取值长度不固定，可以自动定位到值后的第一个双引号作为截取终点，适配任意长度的值：

SELECT
  user_id,
  SUBSTRING(
    user_log,
    LOCATE('"last_login":"', user_log) + 14,
    LOCATE('"', user_log, LOCATE('"last_login":"', user_log) + 14) - (LOCATE('"last_login":"', user_log) + 14)
  ) AS last_login
FROM 你的业务表名;

优化建议 #

长期存储这类结构化操作日志，建议将user_log字段类型从TEXT修改为MySQL原生JSON类型，既可以在写入时自动校验JSON合法性避免脏数据，也可以对JSON字段建索引提升查询效率。

内容的提问来源于stack exchange，提问作者nawab