MySQL中含INNER JOIN的SELECT语句里REPLACE函数失效问题

你的SQL存在两个核心问题：

1. REPLACE不支持正则替换：REPLACE()只能替换固定字符串，你写的REPLACE(Contact.phones, "[^a-zA-Z0-9]", "")是在找整个字符串"[^a-zA-Z0-9]"去替换，而非匹配所有非字母数字字符，完全没起到清理字符的作用。
2. 未正确解析JSON数组字段：Contact.phones是JSON数组类型，直接对整个JSON字符串处理会包含[]、"、:等无关符号，无法精准提取里面的手机号value值。

修正后的SQL（MySQL 8.0+/MariaDB适用） #

SELECT 
  `Customers`.`id` AS `Customers__id`,
  `Contact`.`id` AS `Contact__id`, 
  `Contact`.`customer_id` AS `Contact__customer_id`, 
  `Contact`.`full_name` AS `Contact__full_name`, 
  `Contact`.`phones` AS `Contact__phones`
FROM 
  `customers` `Customers` 
  INNER JOIN `contacts` `Contact` ON `Contact`.`id` = `Customers`.`contact_id`
  -- 展开JSON数组，提取每个手机号的value值
  JOIN JSON_TABLE(
    `Contact`.`phones`,
    '$[*]' COLUMNS (phone_value VARCHAR(255) PATH '$.value')
  ) AS phones
WHERE 
  -- 移除所有非字母数字字符后匹配目标串
  REGEXP_REPLACE(phones.phone_value, '[^a-zA-Z0-9]', '') LIKE '%55512%'
LIMIT 20 OFFSET 0

关键说明 #

• JSON_TABLE：将JSON数组展开为临时表，提取数组中每个元素的value字段，实现对单个手机号的精准处理。
• REGEXP_REPLACE：支持正则匹配替换，'[^a-zA-Z0-9]'匹配所有非字母数字字符，替换为空后即可完成字符清理。

兼容低版本MySQL（无JSON_TABLE时） #

如果你的MySQL版本低于8.0，可使用以下方式（仅适合数组内只有一个元素的场景）：

SELECT 
  `Customers`.`id` AS `Customers__id`,
  `Contact`.`id` AS `Contact__id`, 
  `Contact`.`customer_id` AS `Contact__customer_id`, 
  `Contact`.`full_name` AS `Contact__full_name`, 
  `Contact`.`phones` AS `Contact__phones`
FROM 
  `customers` `Customers` 
  INNER JOIN `contacts` `Contact` ON `Contact`.`id` = `Customers`.`contact_id`
WHERE 
  REGEXP_REPLACE(
    JSON_UNQUOTE(JSON_EXTRACT(`Contact`.`phones`, '$[0].value')),
    '[^a-zA-Z0-9]',
    ''
  ) LIKE '%55512%'
LIMIT 20 OFFSET 0

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