有没有办法在SQL中使用json对象

2021-11-17 00:00:00 json mysql architecture database-design

问题来了:

我们有一个 MySQL 数据库，我们在其中存储 JSON 字符串.直到现在这都没问题，但突然我们的客户要求我们对这些对象进行搜索/排序/其他操作.由于项目处于相当晚的状态，去 Mongo 不是团队负责人想要做出的选择，所以我问有没有一种快速的方法来使用这些 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 as Intention

在 MySQL 中无法使用 JSON.一些 DBMS 可能支持 JSON，但这是不可能的，此外，任何类型的支持"都只是关于执行一些特定于 JSON 的操作，而不是关于对您的架构进行建模(这两件事完全不同)更多，从完整意义上讲，MySQL 的模型概念(即关系)与 JSON 不同的是:作为关系 DBMS，它遵循 关系数据模型，和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.

解决方案

您有多种选择:

  • 迁移到 Postgree SQL，因为它扩展了对 json 的支持，从 9.4 版开始，它是 jsonb 甚至更快.这可能是最好的选择，因为它是 RDBMS，因此迁移不会像真正面向文档的 DBMS 那样困难.
  • 或者现在迁移到 Mongo(如果这是您的意图)，以免为时已晚.考虑到，Mongo 与 RDBMS 完全不同，它是面向文档的.我想这对您的项目和您的客户来说都是最好的选择(而您的任务是解释这一点)
  • 更改整个架构，这样您就不会存储 JSON 对象，而是使用规范化(就关系数据库而言)实体.这意味着 - 是的，对所有代码进行完整重构，更改所有关系等.在实际情况下，这只是理论上的选择，您不会为此获得时间和金钱.
  • 为 MySQL 实现您自己的 JSON 库.难吗?取决于你将用你的 JSON 做什么，但 JSON 是公共格式，所以你至少知道该怎么做.您可以作为 UDF 或在用户处执行此操作土地(所以与 CREATE FUNCTION 声明).当然，这将需要特定的技能和时间.坏事:错误.即使您能够比重构架构或迁移到 Mongo 更快地创建这些功能，您也永远无法确定这些功能的质量.没有办法在本地测试该代码.但是，我可能会提示用户级功能的情况 - 您可以使用 mysql-unit 测试您存储的代码，如果您的 MySQL 是 5.6 或更高版本(好吧，我已经编写了这个工具，但是..它也可能包含错误)
  • 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，那么预发布 JSON 函数可能会带来一线希望 - 因此，您可以尝试使用 JSON 功能的 alfa 版本，该功能目前存在于 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.

相关文章