有什么办法在SQL中使用JSON对象 [英] Is there any way to use json objects in SQL

问题描述

下面的问题：

我们有我们存储JSON字符串MySQL数据库。到现在为止，这是没有问题的，但突然间我们的客户要求我们做搜索/顺序/其它操作与这些对象。由于该项目是在一个相当晚的状态，去蒙戈是不是球队的领袖要做出选择，所以我问的是有没有使用这些JSON字符串的快捷方式？

We have a MySQL database in which we store JSON strings. Until now this was no problem, but suddenly our client requested we do search/order/other operations with these objects. Since the projects is at a fairly late state, going to Mongo is not a choice the team leader wants to make, so I'm asking is there a quick way to use these JSON strings?

推荐答案

JSON作为意向

有没有办法在MySQL中使用JSON工作。有些DBMS可支持JSON，但是这是不可能的，而且，除此之外，任何形式的这种支持只是有关执行一些特定的JSON-操作，但不是建模您的架构（而这两件事情是完全不同的）更多在感十足，MySQL的模型概念（即关系）比JSON不同的是：作为一个关系数据库管理系统，它遵循的关系数据模型和JSON完全是不同的格式。你将它保存为纯字符串值，因此，不可能用它做任何事情在一些其他的方式，如果不使用字符串函数。因此，即使JSON的工作，你会做，不是里面的关系模型，因此它不会是可能维持关系的功能，如参照完整性，例如。

There is no way to work with JSON in MySQL. Some DBMS may support JSON, but that's out of the question, and, besides, any sort of such "support" is only about performing some JSON-specific operations, but not about modelling your architecture (and those two things are completely different) More, in full sense, model concept (i.e. relations) for MySQL is different than JSON is: as a Relational DBMS, it follows relational data model, and JSON is completely different format. You will store it as plain string value, thus, impossible to do anything with it in some other way, if not use string functions. So even working with JSON you will do that not inside relational model, thus, it won't be possible to maintain relational features, such as referential integrity, for instance.

选项来解决

您有几种选择：

• 迁移到SQL Postgree，因为它延长了JSON支持，因为9.4版是的 jsonb ，它的速度更快。因为它是RDBMS，因此迁移将不会太难像真正的面向文档的数据库管理系统，这可能是最好的选择。


• 或迁移到现在蒙戈（如果那是你的意图），这是为时已晚。采取帐户，蒙戈完全不是RDBMS不同的事情，它是面向文档的。我想这是最好的选择，既适合你的项目，并为您的客户（和你的任务是解释）


• 改变整个体系结构，因此您不会存储JSON对象，将与标准化工作（与DB而言）的实体。这意味着 - 是的，所有的code的整个重构，改变所有关系等。在实际情况下，它只是理论上的选项，你将不会被给予既没有时间也没有钱为


• 实施自己的JSON库为MySQL。难吗？你将与​​你的JSON做什么，而是要看 JSON 是公开的格式，所以你会知道该怎么做，至少。你可以做到这一点无论是作为 UDF 或在用户自土地（因此与 CREATE FUNCTION 语句）。这将需要特定的技能和时间，当然。坏的东西：错误。即使你将能够更快地创建这些功能不是重新结构体系结构或迁移到Mongo的，你永远不会成为某些这些功能品质。有没有办法进行本地测试code。不过，我可以给提示为用户功能用地的情况下 - 你可以使用 MySQL的单元 来测试你的存储code，如果你的MySQL 5.6或更高版本（当然，我已经写了这个工具，但..它可能包含bug太）

• Migrate to Postgree SQL as it has extended support for json, since version 9.4 it's jsonb and it's even faster. This might be the best option since it's RDBMS and therefore the migration won't be that hard like for truly document-oriented DBMS.
• Or migrate to Mongo now (if that's your intention), before it's too late. Take in account, that Mongo is completely different thing than RDBMS, it's document-oriented. I guess this is the best option both for your project and for your client (and your task would be to explain that)
• Change entire architecture so you won't store JSON objects and will work with normalized (in terms of relation DB) entities. This means - yes, entire refactoring of all code, changing all relations etc. In real situation it's just theoretical option, you won't be given neither time nor money for that.
• Implement your own JSON library for MySQL. Is it hard? Depends of what you will do with your JSON, but JSON is public format, so you will know what to do, at least. You may do it either as UDF or at user-land (so with CREATE FUNCTION statement). This will require specific skills and time, of course. The bad things: bugs. Even if you'll be able to create those functions faster than re-structure your architecture or migrate to Mongo, you will never be certain of quality of those functions. There's no way to test that code natively. However, I may give hint for the case of user-land functions - you may use mysql-unit to test your stored code, if your MySQL is 5.6 or higher (well, I've written this tool, but.. it may contain bugs too)

标准的功能

最后，如果你正在运行MySQL 5.7，那么有可能是 pre释放了希望的曙光JSON功能 - 这样，你可能的尝试的使用JSON的功能，目前存在的MySQL 5.7的阿尔法版本。但我不会（强烈）建议使用，在实际的项目，因为这些功能既不充分测试，也没有完整的。但是，安装这些功能，你需要下载相应的包，然后将它们插入到你的服务器，如：

Finally, if you're running MySQL 5.7, then there may be a ray of hope with pre-release JSON functions - so, you may try to use alfa-version of JSON functionality, which currently exists for MySQL 5.7. But I would not (strongly) recommend to use that in real project since those functions are neither well-tested nor complete at all. But, to install those function, you'll need to download corresponding package, then plug them into your server, like:

CREATE FUNCTION json_append       RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_valid        RETURNS integer SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_extract      RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_replace      RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_remove       RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_set          RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_merge        RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_search       RETURNS string  SONAME 'libmy_json_udf.so';
CREATE FUNCTION json_contains_key RETURNS integer SONAME 'libmy_json_udf.so';

在这之后，你就可以尝试一下。

And after that you'll be able to try them.

这篇关于有什么办法在SQL中使用JSON对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！