MySQL vs PostgreSQL JSON搜索功能 [英] MySQL vs PostgreSQL JSON search features

问题描述

我一直在寻找一篇博客文章或一个功能矩阵，它通过JSON功能比较MySQL和PostgreSQL。我发现了一个很好的 Postgres功能矩阵，但不适用于MySQL。还有一些帖子，如这比较两者，但过时和/或不是很详细。
到目前为止，我只发现两者都可以使用JSONB数据类型保存数据，能够在JSON中的字段上进行索引以提高查询性能，并且可以在这些字段上进行全文本搜索。但不是那么好，有什么缺点。
什么是支持MySQL而不是Postgres的决定性因素，反之亦然，因为他们的JSON搜索功能？

（这个问题是在MySQL 5.7和PostgreSQL 9.6时发布的当前版本;这类事情变化很快，所以后面的读者应该牢记这一点）。

解决方案

在Postgres上支持MySQL的决定因素是什么，反之亦然$ J $ B为他们的JSON搜索功能？

好吧，事实上，postgresql JSONB有几个函数可以返回 rows ，而mysql最多只能返回数组，这足以让我们坚持使用postgresql而不是mysql。

如果您认为mysql可以从JSON返回行，请从10月份回答这个问题，但仍然没有答案：将MySQL中的JSON数组转换为行

Mysql不是像postgresql那样擅长子查询，但JSON功能限制了您对其他表执行连接的能力。相比之下，postgresql有一个整个堆的功能，可以返回行。

I have been searching for a blog post or a feature matrix which compares MySQL and PostgreSQL by their JSON capabilities. I found a good feature matrix for Postgres but not for MySQL. Also there are a bunch posts like this which compare the two but are outdated and/or not really detailed. So far I only found out that both can save data using the JSONB data type, are able to index on fields within the JSON for improved query performances and that they can full text search on those fields. But not how well and what drawbacks there are. What is a deciding factor to favour MySQL over Postgres and vice versa for their JSON search features?

(This question was posted when MySQL 5.7 and PostgreSQL 9.6 were the current versions; this sort of thing changes quickly so later readers should keep that in mind.)

解决方案

What is a deciding factor to favour MySQL over Postgres and vice versa for their JSON search features?

Well, the fact that postgresql JSONB has several functions that can return rows while mysql at best can only return arrays is reason enough to stick to postgresql rather than mysql.

If you think, mysql can return rows from JSON please answer this question from October that's still unanswered: Convert JSON array in MySQL to rows

Mysql isn't so good at subqueries as postgresql, but the JSON functionality limit your ability to perform joins against other tables. In contrast, postgresql has a whole heap of functions that can return rows.

这篇关于MySQL vs PostgreSQL JSON搜索功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！