MySQL - 在一行中获取与外键相关的所有数据

Question

我有两个表，说:

表_A:

id | name | student_id
1  | John | 01
2  | Jane | 02
...

表_B

id | student_id | book_name
1  | 01         | "SQL"
2  | 01         | "Learning SQL"
3  | 01         | "Mastering SQL"
4  | 02         | "SQL engines"
5  | 02         | "SQL and MySQL"
....

我的问题是:如果可能的话，我怎样才能获得与学生 John 相关的所有数据？我的意思是，在 select * from ... 之后是否有命令给我这个输出:

id  |  name  |  student_id  |  book_name
1   |  John  |  01          |  {"SQL","Learning SQL","Mastering SQL"}
2   |  Jane  |  02          |  {"SQL engines","SQL and MySQL"}

就像约翰和简的每本书的 book_name 列上都有一个 CONCAT() 命令？

我有一个与我的数据库通信的 php 后端，我可以在 TABLE_A 中获取 John 的所有信息，在 TABLE_B 中获取所有数据，然后对数组进行一些魔术等等。但是我的数据库很大，如果不是绝对必要，我不想这样做，我想知道是否可以使用 TABLE_A.student_id<->TABLE_B 这样的外键。 student_id，可以得到我需要的。

谢谢!

附注我使用 InnoDB
p.p.s ONLY_FULL_GROUP_BY 已启用

Answer 1

您可以使用 JSON_ARRAYAGG()生成有效的 JSON 数组:

select a.id, a.name, a.student_id,
  json_arrayagg(b.book_name) as book_names
from TABLE_A a
join TABLE_B b on b.student_id = a.student_id
group by a.id, a.name, a.student_id

结果:

| id  | name | student_id | book_names                               |
| --- | ---- | ---------- | ---------------------------------------- |
| 1   | John | 01         | ["SQL", "Learning SQL", "Mastering SQL"] |
| 2   | Jane | 02         | ["SQL and MySQL", "SQL engines"]         |

View on DB Fiddle

然后您可以使用 json_decode()用 PHP 解析它。

如果您需要与您的问题格式相同的结果，您可以试试这个:

select a.id, a.name, a.student_id,
  concat('{', group_concat(json_quote(b.book_name)), '}') as book_names
from TABLE_A a
join TABLE_B b on b.student_id = a.student_id
group by a.id, a.name, a.student_id

结果:

| id  | name | student_id | book_names                             |
| --- | ---- | ---------- | -------------------------------------- |
| 1   | John | 01         | {"Learning SQL","SQL","Mastering SQL"} |
| 2   | Jane | 02         | {"SQL engines","SQL and MySQL"}        |

View on DB Fiddle

这两个查询至少需要 MySQL 5.7。

在 MySQL 5.7 之前，您需要手动添加引号:

select a.id, a.name, a.student_id,
  concat('{', group_concat('"', b.book_name, '"'), '}') as book_names
from TABLE_A a
join TABLE_B b on b.student_id = a.student_id
group by a.id, a.name, a.student_id

但请确保书名不包含任何双引号。