MySQL存储和搜索字符串
我目前正在开发自己的博客系统。目前,当您创建新帖子时,您可以选择将其存档为您自己选择的类别。
目前,我将类别作为 VARCHAR 值存储在 mysql 数据库中。例如,如果用户选择了 ID 为 2、4 和 8 的类别,则该字段将包含 2、4、8。
要检索 ID 为 4 的类别的博客文章,我将使用:
SELECT col FROM table WHERE LOCATE(',4,', CONCAT(',',col,','))
我被告知值当涉及到良好的数据库结构时,用小数点逗号分隔是不行的(非常糟糕)!
谁能为我提供一种好方法/技术,使其成为最有效的方法?
提前致谢
I’m currently in the process of developing my own blogging system. Currently when you create a new post, you get the option to archive it categories of your own choise.
Currently I’m storing the categories as a VARCHAR value in a mysql database. As an example the field will contain 2,4,8 if the user has chosen the categories with ID: 2, 4 and 8.
To retrieve the blog posts for the category with ID 4 I then use:
SELECT col FROM table WHERE LOCATE(',4,', CONCAT(',',col,','))
I’ve been told that values seperated with a decimal comma is a no-go (very bad) when it comes to good database structure!
Can anyone provide me with a good way/technique to make this the most effective way?
Thanks in advance
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
灵活的& 中多次发布的那样:
强大的设置,正如在 SO:当前查询所在的位置
A flexible & robust setup, as posted so many times in SO:
Where the current query would be:
研究关系数据库规范化。对于您的具体情况,除了您的博客内容表之外,请考虑创建 2 个附加表:类别和博客类别。类别包含所有标签/类别的定义,仅此而已。 BlogCategories 表是一个多对多交叉引用表,在您的情况下可能只包含对 Blog 表的外键引用和对 Category 表的外键引用。这允许 1 个博客条目与多个类别关联,并且 1 个类别与多个博客条目关联。
最坏的情况下,取出数据不会比 3 个表连接更困难,而且您将不再需要处理子字符串来确定您的业务逻辑。
Look into relational database normalization. For your specific case consider creating 2 additional tables, Categories and BlogCategories in addition to your Blog content table. Categories contain the definition of all tags/categories and nothing else. The BlogCategories table is a many-to-many cross reference table that probably in your case just contains the foreign key reference to the Blog table and the foreign key reference to the Categories table. This allows 1 Blog entry to be associated with multiple categories and 1 Category to be associated with multiple Blog entries.
Getting the data out won't be any more difficult than a 3 table join at worst and you'll be out of the substring business to figure our your business logic.