MariaDB如何从{"user":"xxx"}格式字符串中提取数字匹配userid

方法1：使用JSON内置函数（推荐，MySQL 5.7及以上版本支持） #

你存储在code字段的内容是标准JSON格式，直接用JSON解析函数提取对应字段值是最稳妥的方案，不会出现匹配偏差。
查询语句如下：

SELECT sub.userid, rev.code
FROM test1 sub
JOIN test2 rev ON sub.userid = JSON_UNQUOTE(JSON_EXTRACT(rev.code, '$.user'));

如果你使用的是MySQL 8.0及以上版本，可以用更简洁的JSON_VALUE函数简化写法：

SELECT sub.userid, rev.code
FROM test1 sub
JOIN test2 rev ON sub.userid = JSON_VALUE(rev.code, '$.user');

执行上述语句可以正确匹配到userid为3的记录，符合预期结果。

方法2：正则提取（兼容低版本MySQL） #

如果你的MySQL版本低于5.7，不支持JSON相关函数，可以用正则替换的方式提取数字部分：

SELECT sub.userid, rev.code
FROM test1 sub
JOIN test2 rev ON sub.userid = REGEXP_REPLACE(rev.code, '[^0-9]', '');

REGEXP_REPLACE(rev.code, '[^0-9]', '')的作用是将code字段中所有非数字的字符全部替换为空，最终得到的就是你需要的纯数字字符串，再和test1的userid做等值匹配即可。

原写法失效原因说明 #

你之前的写法仅用REGEXP '[0-9]'做判断，只会返回代表是否存在数字的布尔值（1/0），并没有将数字从完整的JSON字符串中提取出来，关联条件是拿数字类型的userid和完整的JSON字符串做等值匹配，自然无法得到正确结果。

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