合并 3NF mysql 表中多行的内容
在尽职尽责地规范化我的所有数据后，我在将 3NF 行合并为单行以进行输出时遇到了问题。 到目前为止，我一直在使用服务器端编码来完成此操作，但由于各种原因，我现在需要选择与另一行相关的所有行，并将它们组合在一行中，全部在 MySQL 中......
所以尝试解释一下:
我有三张桌子。
- 类别
- 文章
- CategoryArticles_3NF
类别包含 CategoryID + 标题、描述等。它可以在文章表中包含任意数量的文章，由 ArticleID + 一个用于容纳内容的文本字段组成。 CategoryArticles 表用于链接两者，因此包含 CategoryID 和 ArticleID。
现在，如果我选择类别记录，并通过链接 CategoryArticles_3NF 表加入文章表，则结果是该类别中包含的每篇文章的单独行。 问题是我想为每个类别输出一行，其中包含其中所有文章的内容。
如果这听起来像是一个荒谬的要求，那是因为事实确实如此。我只是使用文章作为描述问题的好方法。我的数据实际上有些不同。
无论如何 - 我能看到实现此目的的唯一方法是使用“GROUP_CONCAT”语句将内容字段分组在一起 - 问题在于可以返回的数据量是有限的，我需要它能够处理更多的事情。
谁能告诉我该怎么做？
谢谢。
Having dutifully normalised all my data, I'm having a problem combining 3NF rows into a single row for output.
Up until now I've been doing this with server-side coding, but for various reasons I now need to select all rows related to another row, and combine them in a single row, all in MySQL...
So to try and explain:
I have three tables.
- Categories
- Articles
- CategoryArticles_3NF
A category contains CategoryID + titles, descriptions etc. It can contain any number of articles in the Articles table, consisting of ArticleID + a text field to house the content.
The CategoryArticles table is used to link the two, so contains both the CategoryID and the ArticleID.
Now, if I select a Category record, and I JOIN the Articles table via the linking CategoryArticles_3NF table, the result is a separate row for each article contained within that category.
The issue is that I want to output one single row for each category, containing content from all articles within.
If that sounds like a ridiculous request, it's because it is. I'm just using articles as a good way to describe the problem. My data is actually somewhat different.
Anyway - the only way I can see to achieve this is to use a 'GROUP_CONCAT' statement to group the content fields together - the problem with this is that there is a limit to how much data this can return, and I need it to be able to handle significantly more.
Can anyone tell me how to do this?
Thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
这听起来像是应该在前端完成的事情，而无需更多信息。
如果需要，可以通过设置系统变量group_concat_max_len来增加GROUP_CONCAT的大小限制。它有一个基于 max_allowed_packet 的限制，您也可以增加该限制。我认为数据包的最大大小是 1GB。如果您需要比这个更高，那么您的设计中存在一些严重的缺陷。
编辑：所以这是在答案中，而不仅仅是埋在评论中...
如果您不想全局更改group_concat_max_len，那么您可以仅针对您的会话更改它：
This sounds like something that should be done in the front end without more information.
If you need to, you can increase the size limit of GROUP_CONCAT by setting the system variable group_concat_max_len. It has a limit based on max_allowed_packet, which you can also increase. I think that the max size for a packet is 1GB. If you need to go higher than that then there are some serious flaws in your design.
EDIT: So that this is in the answer and not just buried in the comments...
If you don't want to change the group_concat_max_len globally then you can change it for just your session with: