MySql JSON 数据类型的用例 [英] A use case for MySql JSON datatype

问题描述

我正在为一个网站创建一个数据库模式，用户可以在该网站上编写文章.我几乎完成了设计，突然我读了几篇关于 MySQL 中的 JSON 数据类型 的博客.
根据博客，在某些用例中可以使用 JSON:

I am creating a DB-schema for a website on which users can write the Articles. I was almost done with the design and suddenly I read few blogs on JSON datatype in MySQL.
As per blogs, there are certain use cases where JSON can be used:

1. 用于存储元数据.例如具有高度、宽度、颜色存储为 JSON.
2. 用于存储非标准模式类型数据
3. 用于将标签存储为 JSON.例如这个问题可能有标签 -mysql，JSON.所以博客推荐使用 JSON 结构保存所有标签.

最后一个对我来说是怀疑的.为什么?
好的，我已经在 J​​SON 中将标签值存储为 {"tags": ["mysql", "JSON", "mysql-datatype"]}.我同意这有助于轻松维护文章标签.
但是假设一个用户想要阅读所有与 mysql 标签相关的文章！！如果我为 article_id - tags_id 维护了一个单独的表，我可以很容易地获得基于 tags<的所有 Articles/代码>.但是对于 JSON 这可能是一个非常繁忙的要求，虽然这可以解决但需要付出代价.当然，查询速度较慢.
这是我的文章架构:我的思维方式是正确的还是我在这里遗漏了什么?很想听听一些建议.

The last one is doubtful to me. Why?
Ok I have stored the tag value in JSON as {"tags": ["mysql", "JSON", "mysql-datatype"]}. I agree this helps in easily maintaining the tags with the Article.
But suppose a user wants to read all the article related to mysql tags!! If I have been maintained a separate table for article_id - tags_id, I could have easily get all the Articles based on the tags. But with JSON this could be a very hectic requirement, though this can be solved but with a cost. Slower queries ofcourse.
This is my schema for Article: Is my way of thinking correct or am I missing something here? Love to hear some suggestions.

推荐答案

您尝试执行的任务，将文章与标签相关联，最好作为多对多关系处理.为此，您需要另一个表，我认为这是图表中的 article_tags 表.

The task you're trying to do, to associate articles with tags, is better handled as a many-to-many relationship. For this you need another table, which I believe is the article_tags table in your diagram.

这样可以轻松查询具有给定标签的所有文章.

This makes it easy to query for all articles with a given tag.

SELECT ...
FROM article AS a
JOIN article_tags AS t USING (article_id)
WHERE t.topic_id = 1234 -- whatever is the id for the topic you want to read

如果使用 JSON 在文章表中存储标签，则做同样的事情是不同的:

Doing the same thing if you use JSON to store tags in the article table is different:

SELECT ...
FROM article AS a
WHERE JSON_CONTAINS(a.article_tags, '1234')

这可能看起来更简单，因为它不需要 JOIN.

This might seem simpler, since it does not require a JOIN.

但是任何将您需要搜索的列放在函数调用中的搜索都将无法使用索引.这将导致表扫描，因此查询将始终搜索表中的每一行.随着表的增长，艰难地"进行此搜索将变得越来越慢.

But any search that puts the column you need to search inside a function call will not be able to use an index. This will result in a table-scan, so the query will always search every row in the table. As your table grows, it will become slower and slower to do this search "the hard way."

article_tags 表的第一种方法使用索引有两种方式:

The first method with the article_tags table uses an index two ways:

1. 在article_tags中快速查找与所需标签匹配的条目
2. 通过主键快速查找对应文章

1. Look up the entries in article_tags matching the desired tag quickly
2. Look up the corresponding articles by their primary key quickly

不需要表扫描.查询仅读取将要出现在查询结果中的行.

No table-scan needed. The query reads only the rows that are going to be in the query result.

我对 JSON 数据类型和 JSON 函数的看法遵循以下一般规则:

My take on the JSON data type and JSON functions follows this general rule:

在选择列表中引用 JSON 列，但不在 WHERE 子句中.

也就是说，如果您可以使用非 JSON 列在 WHERE 子句中执行搜索条件，则可以利用索引使查询尽可能高效.

That is, if you can do your search conditions in the WHERE clause using non-JSON columns, you can take advantage of indexes to make the query as efficient as possible.

以这种方式找到相关行后，您可以提取部分 JSON 数据以返回结果.与在表扫描中搜索 JSON 文档的成本相比，从与搜索匹配的行上的 JSON 文档中提取字段的成本相对较小.

Once the relevant rows have been found that way, then you may extract parts of your JSON data to return in the result. Compared to the cost of searching JSON documents in a table-scan, the cost of extracting a field from the JSON documents on the rows matching your search is relatively small.

除非行与搜索条件匹配，否则不会对选择列表进行评估.

The select-list is not evaluated for rows unless they match the search conditions.

这篇关于MySql JSON 数据类型的用例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！