如何在MySQL中表示Cross Apply和Split String(How to represent Cross Apply and Split String in MySQL)

如何在MySQL中表示Cross Apply和Split String(How to represent Cross Apply and Split String in MySQL)

对于某些背景，我正在尝试创建一个包含多个食谱的数据库。 但是，必须将各种成分与最初来自的配方联系起来。

例如，我有包含所有单独成分的表。

还有一个储存食谱的表，减去食材。

现在，我发现这篇文章介绍了如何使用T-SQL XML命令拆分字符串，以及用于执行此操作的代码是：

SELECT Books.BookId, Books.Book, BookAuthors.AuthorId, BookAuthors.Author FROM Books CROSS APPLY dbo.split(Books.Authors,',') split INNER JOIN BookAuthors ON BookAuthors.AuthorId = split.val

我正在寻找的结果与此非常相似：

但是，CROSS APPLY等仅适用于MS SQL Server，我的问题是：

是否有可能使用MySQL实现相同或非常相似的效果？

谢谢你的帮助。

For some background, what i'm trying to do create a database that contains multiple recipes. However, it's necessary for the individual ingredients to be linked to the recipe they originally came from.

For instance, I have table containing all the individual ingredients.

And a table where the recipes are stored, minus the ingredients.

Now, i've found this article which covers how to split strings using T-SQL XML Commands, and the code that is used to do so is:

SELECT Books.BookId, Books.Book, BookAuthors.AuthorId, BookAuthors.Author FROM Books CROSS APPLY dbo.split(Books.Authors,',') split INNER JOIN BookAuthors ON BookAuthors.AuthorId = split.val

The result i'm looking for would be very similar to this:

However, CROSS APPLY etc only works on MS SQL Server and my question is:

Is it possible to achieve the same, or very similar effect using MySQL?

Thanks for any help.

最满意答案

这应该与你想要得到的完全匹配：

SELECT Books.BookId, Books.Book, BookAuthors.AuthorId, BookAuthors.Author FROM Books LEFT JOIN BookAuthors ON (find_in_set(BookAuthors.AuthorId, Books.Authors) <> 0)

发现这篇文章非常有帮助： MySQL查询以逗号分隔的字符串查找值

如果您需要进一步说明它是如何工作的，请发表评论。

This should quite match what you're trying to get:

SELECT Books.BookId, Books.Book, BookAuthors.AuthorId, BookAuthors.Author FROM Books LEFT JOIN BookAuthors ON (find_in_set(BookAuthors.AuthorId, Books.Authors) <> 0)

Found this article very helpful: MySQL query finding values in a comma separated string

Leave a comment if you need further explanation how it works.