Как связать таблицы с идентификатором из списка, хранящегося в виде XML в столбце

Question

Надеюсь, вопрос хорошо это объясняет. У меня есть БД для библиотеки. Поскольку они могут использоваться много раз и содержат больше данных, чем просто имя, у меня есть таблица для авторов. Тогда есть стол для книг. У меня нет проблем с привязкой авторов к книгам через столбец с именем Author_id.

Я пытаюсь создать столбец Author_ID, содержащий список идентификаторов, поскольку книга может иметь несколько идентификаторов. В столбце Author_IDs у меня есть:

<id>3478</id>
<id>6456</id>

Используя функцию ExtractValue в MySQL, я могу связать таблицу с одним или другим идентификатором, используя: WHERE Author.id = ExtractValue(Book.Author_IDs,"/id[2]") //, чтобы получить второй идентификатор.

Мой вопрос заключается в том, что я хочу иметь возможность автоматически отображать всех авторов книги, но не знаю, как ссылаться на нее более одного раза, без циклов. Как я могу получить результаты, чтобы показать мне всех авторов?

(Или есть лучший способ сделать это?)

Answer 1

Во-первых, я должен проголосовать против вашего метода хранения. По возможности следует избегать хранения данных в виде xml внутри столбца mysql. Если вы используете нормальный подход, вы найдете эту проблему гораздо проще.

Создать таблицу:

book_authors

book_id     author_id
-------     ---------
      1             1
      1             2
      1             3
      2             2
      2             4

Затем, чтобы связать всех авторов с определенной книгой, это простой запрос.

Select
   b.book_id,
   b.book_name,
   GROUP_CONCAT(a.author_name) AS 'authors'
FROM
   book_authors ba LEFT JOIN
   books b ON ba.book_id = b.book_id LEFT JOIN
   authors a ON ba.author_id = a.author_id
GROUP BY
   ba.book_id

Answer 2

Я бы сделал это так

Состав

tblBooks
--------
book_id
book_name

tblAuthors
----------
author_id
author_name

tblBooksToAuthors
-----------------
id
book_id
author_id

запрос

select a.*
from tblAuthors a
left join tblBooksToAuthors b2a on (b2a.author_id = a.author_id)
where b2a.book_id = {your book id};

Answer 3

Не уверен, что я полностью понимаю. Может ли что-то подобное сделать свое дело?

выберите a.* из tblBooks b оставьте присоединиться к tblAuthors a on (b.authors = concat('%', a.id, '%') где b = 'идентификатор книги';