MySQL 5.7.12中JSON_EXTRACT结果去除引号的问题

首先咱们拆解下你遇到的核心问题：用JSON_EXTRACT取出的mail字段带双引号，尝试REPLACE却得到了十六进制字符串——这是因为**JSON_EXTRACT返回的是JSON数据类型，而非普通字符串**，当你直接对JSON类型应用字符串函数时，MySQL会将其转换为二进制格式，所以显示成了十六进制编码。

针对MySQL 5.7.12无法使用JSON_UNQUOTE的限制，这里有两种可靠的解决方法：

方法1：先转字符串再替换引号 #

用CAST（或CONVERT）把JSON_EXTRACT的结果转为字符串类型，再用REPLACE去掉首尾的双引号：

SELECT 
  objectid,
  REPLACE(CAST(JSON_EXTRACT(fullobject, '$.mail') AS CHAR(255)), '"', '') AS mail
FROM openidm.managedobjects 
WHERE objecttypes_id = 5 
  AND CAST(JSON_EXTRACT(fullobject, '$.mail') AS CHAR(255)) = 'fake@email.com';

方法2：截取字符串去掉首尾引号 #

既然JSON_EXTRACT返回的结果固定是"xxx"格式，我们可以直接用SUBSTRING截取掉第1位和最后1位的引号：

SELECT 
  objectid,
  SUBSTRING(
    JSON_EXTRACT(fullobject, '$.mail'), 
    2, 
    CHAR_LENGTH(JSON_EXTRACT(fullobject, '$.mail')) - 2
  ) AS mail
FROM openidm.managedobjects 
WHERE objecttypes_id = 5 
  AND SUBSTRING(
    JSON_EXTRACT(fullobject, '$.mail'), 
    2, 
    CHAR_LENGTH(JSON_EXTRACT(fullobject, '$.mail')) - 2
  ) = 'fake@email.com';

为什么之前的REPLACE会失败？ #

在MySQL 5.7中，JSON类型的数据在参与字符串操作时会被自动转换为二进制字符串（以十六进制形式显示），你看到的6C75632E6170617369383840676D61696C2E636F6D其实就是fake@email.com的十六进制编码。通过显式转换为CHAR类型，就能彻底避免这个问题。

额外提示 #

在WHERE条件中，最好也对JSON_EXTRACT的结果做类型转换后再比较，这样能避免隐式转换可能带来的意外问题，确保匹配逻辑准确。

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