MySQL - (#1242) 子查询返回超过 1 行

Question

我有包含 2 列的 tbl:title 和 params，值如下所示:

• 标题:{“教学”}

• 参数:

{ "ufield926":"34", "ufield927":"南", "ufield928":"阿拉", "ufield929":"麦加", “ufield930”:“1/1/1983”， "ufield933":"011", "ufield934":"Mub", "ufield943":"SU/HI/14", "ufield944":"平均", "ufield946":"女"}我想提取“ufield943”之后的代码:它只是 SU/HI/14，并将它与标题列中的值连接起来，如下所示:

-->教学(SU/HI/14)

这是我试过的查询:

SELECT CONCAT(title, "(", (select 
       substring(
         params,
         locate('ufield943', params) + 12,
         locate('ufield944', params) - locate('ufield943', params) - 21
       ) FROM tbl), ")") AS title
FROM tbl;

每次运行查询都会出现以下错误

"#1242 - Subquery returns more than 1 row"

Answer 1

我用 SUBSTRING 从标题中删除 {"..."}和 CHAR_LENGTH一起发挥作用。

我将 - 21 更改为 - 16 并在 (

之前添加空格

SELECT CONCAT(substring(title, 3,CHAR_LENGTH(title) - 4 ), " (", 
   substring(
     params,
     locate('ufield943', params) + 12,
     locate('ufield944', params) - locate('ufield943', params) - 16
   ), ")") AS Title
FROM tbl;

DEMO