mysql - 外键分隔在一个字段中

Question

我有两张 table :

表位置

ID      |      Name      |  Type
1       | Location1      |  {"0":"23","1":"27","2":"24","3":"22"}
2       | Location2      |  {"0":"22","1":"25"}

tbllocationtypes

ID      |      title      
22      |    Wines
23      |    Milk
24      |    Cereals
25      |    Drinks
26      |    Beef
27      |    Chicken

在 Type 字段(具有这种精确格式)中，类型的外键被特殊分隔

"0":"tbllocationtype 的默认外键",
"1","tbllocationtype 的第一个附加外键",
"2","tbllocaitontype 的第二个附加外键"

我需要一个包含所有位置的列表，其中包含一个包含所有类型的字段，如下所示:

查询结果:

IDLocation     |   Name       |     Types
1                Location1       Milk,Chicken,Cereals,Wine
2                Location2       Wines,Drinks

你能帮我一下吗？我对 MySQL 的了解太差，无法发现这一点。

非常感谢

Answer 1

在现代版本的 MySQL (>= 8.0.4) 中，查询相对简单:

SELECT
  `l`.`id` `IDLocation`,
  `l`.`name` `Name`,
  GROUP_CONCAT(`lt`.`title`) `Types`
FROM
  `tbllocations` `l`,
  JSON_TABLE(`l`.`type`,
    '$.*' COLUMNS(
      `id` BIGINT UNSIGNED PATH '$'
    )
  ) `der`
  INNER JOIN `tbllocationtypes` `lt` ON
    `lt`.`id` = `der`.`id`
GROUP BY
  `l`.`id`,
  `l`.`name`;

参见db-fiddle .

但是，在旧版本中，事情没那么简单，只有一个选项:

SELECT
  `l`.`id` `IDLocation`,
  `l`.`name` `Name`,
  GROUP_CONCAT(`lt`.`title`) `Types`
FROM
  `tbllocationtypes` `lt`
  LEFT JOIN
    `tbllocations` `l` ON
      JSON_CONTAINS(
        JSON_EXTRACT(`l`.`type`, '$.*'),
        JSON_QUOTE(CAST(`lt`.`id` AS CHAR))
      )
WHERE
  `l`.`id` IS NOT NULL
GROUP BY
  `l`.`id`,
  `l`.`name`;

参见db-fiddle .

无论如何，请记住5.1.7 Server System Variables::group_concat_max_len .