MySQL 5.7 增加了JSON 資料類型的支持，在之前如果要儲存JSON 類型的資料的話我們只能自己做JSON.stringify( ) 和JSON.parse() 的操作，而且沒辦法針對JSON 內的資料進行查詢操作，所有的操作必須讀取出來parse 之後進行，非常的麻煩。原生的 JSON 資料類型支援之後，我們就可以直接對 JSON 進行資料查詢和修改等操作了，較之前會方便非常多。
為了方便示範我先建立一個 user 表，其中 info 欄位用來儲存使用者的基礎資訊。要將欄位定義成 JSON 類型資料非常簡單，直接欄位名稱後接 JSON 即可。
CREATE TABLE user ( id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, info JSON );复制代码
表創建成功之後我們就按照經典的 CRUD 資料操作來講講怎麼進行 JSON 資料類型的操作。
新增資料這塊是比較簡單，不過需要理解 MySQL 對 JSON 的儲存本質上還是字串的儲存操作。只是當定義為 JSON 類型之後內部會對資料再進行一些索引的建立方便後續的操作而已。所以在添加 JSON 資料的時候需要使用字串包裝。
mysql> INSERT INTO user (`name`, `info`) VALUES('lilei', '{"sex": "male", "age": 18, "hobby": ["basketball", "football"], "score": [85, 90, 100]}');
Query OK, 1 row affected (0.00 sec)复制代码除了自己拼 JSON 之外，你還可以呼叫 MySQL 的 JSON 建立函數來進行建立。
JSON_OBJECT：快速建立JSON 對象，奇數列為key，偶數列為value，使用方法JSON_OBJECT(key,value,key1,value1) JSON_ARRAY：快速建立JSON 數組，使用方法JSON_ARRAY(item0, item1, item2)
mysql> INSERT INTO user (`name`, `info`) VALUES('hanmeimei', JSON_OBJECT(
-> 'sex', 'female',
-> 'age', 18,
-> 'hobby', JSON_ARRAY('badminton', 'sing'),
-> 'score', JSON_ARRAY(90, 95, 100)
-> ));
Query OK, 1 row affected (0.00 sec)复制代码不過對於JavaScript 工程師來說不管是使用字串來寫還是使用自帶函數來建立JSON 都是非常麻煩的一件事，遠遠沒有JS 原生物件來的好用。所以在 think-model 模組中我們增加了 JSON 資料類型的資料自動進行 JSON.stringify() 的支持，所以直接傳入 JS 物件資料即可。
由於資料的自動序列化和解析是根據欄位類型來做的，為了不影響已執行的項目，需要在模組中配置 jsonFormat: true 才能開啟這項功能。
//adapter.jsconst MySQL = require('think-model-mysql');exports.model = { type: 'mysql', mysql: { handle: MySQL,
... jsonFormat: true
}
};复制代码//user.jsmodule.exports = class extends think.Controller { async indexAction() { const userId = await this.model('user').add({ name: 'lilei', info: { sex: 'male', age: 16, hobby: ['basketball', 'football'], score: [85, 90, 100]
}
}); return this.success(userId);
}
}复制代码下面讓我們來看看最終儲存到資料庫中的資料是什麼樣的
mysql> SELECT * FROM `user`;
+----+-----------+-----------------------------------------------------------------------------------------+
| id | name | info |
+----+-----------+-----------------------------------------------------------------------------------------+
| 1 | lilei | {"age": 18, "sex": "male", "hobby": ["basketball", "football"], "score": [85, 90, 100]} |
| 2 | hanmeimei | {"age": 18, "sex": "female", "hobby": ["badminton", "sing"], "score": [90, 95, 100]} |
+----+-----------+-----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)复制代码為了更好的支援JSON 資料的操作，MySQL提供了一些JSON 資料操作類別的方法。和查詢操作相關的方法主要如下：
JSON_EXTRACT()：根據Path 取得部分JSON 數據，使用方法JSON_EXTRACT(json_doc, path[, path] . ..)
->：JSON_EXTRACT() 的等價寫法->>：JSON_EXTRACT() 和JSON_UNQUOTE() 的等價寫法JSON_CONTAINS()：查詢JSON 資料是否在指定Path 包含指定的數據，包含則傳回1，否則回傳0。使用方法 JSON_CONTAINS(json_doc, val[, path])
JSON_CONTAINS_PATH()：查詢是否有指定路徑，存在則傳回1，否則回傳0。 one_or_all 只能取值 "one" 或 "all"，one 表示只要有一個存在即可，all 表示所有的都存在才行。使用方法JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
JSON_KEYS()：取得JSON 資料在指定路徑下的所有鍵值。使用方法 JSON_KEYS(json_doc[, path])，類似 JavaScript 中的 Object.keys() 方法。 JSON_SEARCH()：查詢包含指定字串的 Paths，並以一個 JSON Array 傳回。查詢的字串可以用 LIKE 裡的 '%' 或 '_' 來匹配。使用方法 JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])，類似 JavaScript 中的 findIndex() 操作。 我們在這裡不對每個方法進行逐一的舉例描述，僅提出一些場景舉例應該怎麼操作。
舉這個例子就是想告訴下大家怎麼取得 JSON 資料中的部分內容，並且依照正常的表格欄位進行回傳。這塊可以使用 JSON_EXTRACT 或等價的 -> 操作都可以。其中根據範例可以看到sex 傳回的資料都帶有引號，這個時候可以使用JSON_UNQUOTE() 或直接使用->> 就可以把引號去掉了。
mysql> SELECT `name`, JSON_EXTRACT(`info`, '$.age') as `age`, `info`->'$.sex' as sex FROM `user`; +-----------+------+----------+ | name | age | sex | +-----------+------+----------+ | lilei | 18 | "male" | | hanmeimei | 16 | "female" | +-----------+------+----------+ 2 rows in set (0.00 sec)复制代码
这里我们第一次接触到了 Path 的写法，MySQL 通过这种字符串的 Path 描述帮助我们映射到对应的数据。和 JavaScript 中对象的操作比较类似，通过 . 获取下一级的属性，通过 [] 获取数组元素。
不一样的地方在于需要通过 $ 表示本身，这个也比较好理解。另外就是可以使用 * 和 ** 两个通配符，比如 .* 表示当前层级的所有成员的值，[*] 则表示当前数组中所有成员值。** 类似 LIKE 一样可以接前缀和后缀，比如 a**b 表示的是以 a 开头，b结尾的路径。
路径的写法非常简单，后面的内容里也会出现。上面的这个查询对应在 think-model 的写法为
//user.jsmodule.exports = class extends think.Controller { async indexAction() { const userModel = this.model('user'); const field = "name, JSON_EXTRACT(info, '$.age') AS age, info->'$.sex' as sex"; const users = await userModel.field(field).where('1=1').select(); return this.success(users);
}
}复制代码mysql> SELECT `name` FROM `user` WHERE JSON_CONTAINS(`info`, '"male"', '$.sex') AND JSON_SEARCH(`info`, 'one', 'basketball', null, '$.hobby'); +-------+ | name | +-------+ | lilei | +-------+ 1 row in set, 1 warning (0.00 sec)复制代码
这个例子就是简单的告诉大家怎么对属性和数组进行查询搜索。其中需要注意的是 JSON_CONTAINS() 查询字符串由于不带类型转换的问题字符串需要使用加上 "" 包裹查询，或者使用 JSON_QUOTE('male') 也可以。
如果你使用的是 MySQL 8 的话，也可以使用新增的 JSON_VALUE() 来代替 JSON_CONTAINS()，新方法的好处是会带类型转换，避免刚才双引号的尴尬问题。不需要返回的路径的话，JSON_SEARCH() 在这里也可以使用新增的 MEMBER OF 或者 JSON_OVERLAPS() 方法替换。
mysql> SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, '$.sex') = 'male' AND 'basketball' MEMBER OF(JSON_VALUE(`info`, '$.hobby'));
+-------+
| name |
+-------+
| lilei |
+-------+
1 row in set (0.00 sec)
mysql> SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, '$.sex') = 'male' AND JSON_OVERLAPS(JSON_VALUE(`info`, '$.hobby'), JSON_QUOTE('basketball'));
+-------+
| name |
+-------+
| lilei |
+-------+
1 row in set (0.00 sec)复制代码上面的这个查询对应在 think-model 的写法为
//user.jsmodule.exports = class extends think.Controller { async indexAction() { const userModel = this.model('user'); const where = { _string: [ "JSON_CONTAINS(info, '\"male\"', '$.sex')", "JSON_SEARCH(info, 'one', 'basketball', null, '$.hobby')"
]
}; const where1 = { _string: [ "JSON_VALUE(`info`, '$.sex') = 'male'", "'basketball' MEMBER OF (JSON_VALUE(`info`, '$.hobby'))"
]
}; const where2 = { _string: [ "JSON_VALUE(`info`, '$.sex') = 'male'", "JSON_OVERLAPS(JSON_VALUE(`info`, '$.hobby'), JSON_QUOTE('basketball'))"
]
} const users = await userModel.field('name').where(where).select(); return this.success(users);
}
}复制代码MySQL 提供的 JSON 操作函数中，和修改操作相关的方法主要如下：
JSON_APPEND/JSON_ARRAY_APPEND：这两个名字是同一个功能的两种叫法，MySQL 5.7 的时候为 JSON_APPEND，MySQL 8 更新为 JSON_ARRAY_APPEND，并且之前的名字被废弃。该方法如同字面意思，给数组添加值。使用方法 JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)
JSON_ARRAY_INSERT：给数组添加值，区别于 JSON_ARRAY_APPEND() 它可以在指定位置插值。使用方法 JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)
JSON_INSERT/JSON_REPLACE/JSON_SET：以上三个方法都是对 JSON 插入数据的，他们的使用方法都为 JSON_[INSERT|REPLACE|SET](json_doc, path, val[, path, val] ...)，不过在插入原则上存在一些差别。JSON_INSERT：当路径不存在才插入JSON_REPLACE：当路径存在才替换JSON_SET：不管路径是否存在JSON_REMOVE：移除指定路径的数据。使用方法 JSON_REMOVE(json_doc, path[, path] ...)
由于 JSON_INSERT, JSON_REPLACE, JSON_SET 和 JSON_REMOVE 几个方法支持属性和数组的操作，所以前两个 JSON_ARRAY 方法用的会稍微少一点。下面我们根据之前的数据继续举几个实例看看。
mysql> UPDATE `user` SET `info` = JSON_REPLACE(`info`, '$.age', 20) WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT JSON_VALUE(`info`, '$.age') as age FROM `user` WHERE `name` = 'lilei'; +------+ | age | +------+ | 20 | +------+ 1 row in set (0.00 sec)复制代码
JSON_INSERT 和 JSON_SET 的例子也是类似，这里就不多做演示了。对应到 think-model 中的话，需要使用 EXP 条件表达式处理，对应的写法为
//user.jsmodule.exports = class extends think.Controller { async indexAction() { const userModel = this.model('user'); await userModel.where({name: 'lilei'}).update({ info: ['exp', "JSON_REPLACE(info, '$.age', 20)"]
}); return this.success();
}
}复制代码mysql> UPDATE `user` SET `info` = JSON_ARRAY_APPEND(`info`, '$.hobby', 'badminton') WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT JSON_VALUE(`info`, '$.hobby') as hobby FROM `user` WHERE `name` = 'lilei'; +-----------------------------------------+ | hobby | +-----------------------------------------+ | ["basketball", "football", "badminton"] | +-----------------------------------------+ 1 row in set (0.00 sec)复制代码
JSON_ARRAY_APPEND 在对数组进行操作的时候还是要比 JSON_INSERT 之类的方便的，起码你不需要知道数组的长度。对应到 think-model 的写法为
//user.jsmodule.exports = class extends think.Controller { async indexAction() { const userModel = this.model('user'); await userModel.where({name: 'lilei'}).update({ info: ['exp', "JSON_ARRAY_APPEND(info, '$.hobby', 'badminton')"]
}); return this.success();
}
}复制代码mysql> UPDATE `user` SET `info` = JSON_REMOVE(`info`, '$.score[0]') WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT `name`, JSON_VALUE(`info`, '$.score') as score FROM `user` WHERE `name` = 'lilei'; +-------+-----------+ | name | score | +-------+-----------+ | lilei | [90, 100] | +-------+-----------+ 1 row in set (0.00 sec)复制代码
删除这块和之前修改操作类似，没有什么太多需要说的。但是对数组进行操作很多时候我们可能就是想删值，但是却不知道这个值的 Path 是什么。这个时候就需要利用之前讲到的 JSON_SEARCH() 方法，它是根据值去查找路径的。比如说我们要删除 lilei 兴趣中的 badminton 选项可以这么写。
mysql> UPDATE `user` SET `info` = JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, 'one', 'badminton'))) WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT JSON_VALUE(`info`, '$.hobby') as hobby FROM `user` WHERE `name` = 'lilei'; +----------------------------+ | hobby | +----------------------------+ | ["basketball", "football"] | +----------------------------+ 1 row in set (0.00 sec)复制代码
这里需要注意由于 JSON_SEARCH 不会做类型转换，所以匹配出来的路径字符串需要进行 JSON_UNQUOTE() 操作。另外还有非常重要的一点是 JSON_SEARCH 无法对数值类型数据进行查找，也不知道这个是 Bug 还是 Feature。这也是为什么我没有使用 score 来进行举例而是换成了 hobby 的原因。如果数值类型的话目前只能取出来在代码中处理了。
mysql> SELECT JSON_VALUE(`info`, '$.score') FROM `user` WHERE `name` = 'lilei'; +-------------------------------+ | JSON_VALUE(`info`, '$.score') | +-------------------------------+ | [90, 100] | +-------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SEARCH(`info`, 'one', 90, null, '$.score') FROM `user` WHERE `name` = 'lilei'; +-------------------------------------------------+ | JSON_SEARCH(`info`, 'one', 90, null, '$.score') | +-------------------------------------------------+ | NULL | +-------------------------------------------------+ 1 row in set (0.00 sec)复制代码
以上对应到 think-model 的写法为
//user.jsmodule.exports = class extends think.Controller { async indexAction() { const userModel = this.model('user'); // 删除分数
await userModel.where({name: 'lilei'}).update({ info: ['exp', "JSON_REMOVE(info, '$.score[0]')"]
}); // 删除兴趣
await userModel.where({name: 'lilei'}).update({ info: ['exp', "JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, 'one', 'badminton')))"]
});
return this.success();
}
}复制代码由于最近有一个需求，有一堆数据，要记录这堆数据的排序情况，方便根据排序进行输出。一般情况下肯定是给每条数据增加一个 order 字段来记录该条数据的排序情况。但是由于有着批量操作，在这种时候使用单字段去存储会显得特别麻烦。在服务端同事的建议下，我采取了使用 JSON 字段存储数组的情况来解决这个问题。
也因为这样了解了一下 MySQL 对 JSON 的支持情况，同时将 think-model 做了一些优化，对 JSON 数据类型增加了支持。由于大部分 JSON 操作需要通过内置的函数来操作，这个本身是可以通过 EXP 条件表达式来完成的。所以只需要对 JSON 数据的添加和查询做好优化就可以了。
整体来看，配合提供的 JSON 操作函数，MySQL 对 JSON 的支持完成一些日常的需求还是没有问题的。除了作为 WHERE 条件以及查询字段之外，其它的 ORDER, GROUP, JOIN 等操作也都是支持 JSON 数据的。
不过对比 MongoDB 这种天生支持 JSON 的话，在操作性上还是要麻烦许多。特别是在类型转换这块，使用一段时间后发现非常容易掉坑。什么时候会带引号，什么时候会不带引号，什么时候需要引号，什么时候不需要引号，这些都容易让新手发憷。另外 JSON_SEARCH() 不支持数字查找这个也是一个不小的坑了。
相关免费学习推荐：mysql视频教程
以上是MySQL 的 JSON 資料類型的詳細內容。更多資訊請關注PHP中文網其他相關文章！
