問題描述

問題來了:

我們有一個 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.

這篇關于有沒有辦法在SQL中使用json對象的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！