标题：MySQL 5.7原生JSON格式支持，对于JSON格式堪称完美，修改某个字段的json。 出处：向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除 时间：Fri, 25 Nov 2016 10:09:16 +0000 作者：jackxiang 地址：http://jackxiang.com/post/9071/ 内容： 背景：在MySQL与PostgreSQL的对比中，PG的JSON格式支持优势总是不断被拿来比较。最主要是json这种东西在做一些物联网时用php结合很有用处。 ========================真实实践成功如下所示AddTime：2016-12-4================================== CREATE TABLE json_test( id INT, person_desc TEXT )ENGINE INNODB; INSERT INTO json_test VALUES (1,' { "programmers": [{ "firstName": "Brett", "lastName": "McLaughlin", "email": "aaaa" }, { "firstName": "Jason", "lastName": "Hunter", "email": "bbbb" }, { "firstName": "Elliotte", "lastName": "Harold", "email": "cccc" }], "authors": [{ "firstName": "Isaac", "lastName": "Asimov", "genre": "sciencefiction" }, { "firstName": "Tad", "lastName": "Williams", "genre": "fantasy" }, { "firstName": "Frank", "lastName": "Peretti", "genre": "christianfiction" }], "musicians": [{ "firstName": "Eric", "lastName": "Clapton", "instrument": "guitar" }, { "firstName": "Sergei", "lastName": "Rachmaninoff", "instrument": "piano" }] }'); ALTER TABLE json_test MODIFY person_desc json; { "programmers":[ { "firstName":"Brett", "lastName":"McLaughlin", "email":"aaaa" }, { "firstName":"Jason", "lastName":"Hunter", "email":"bbbb" }, { "firstName":"Elliotte", "lastName":"Harold", "email":"cccc" } ], "authors":[ { "firstName":"Isaac", "lastName":"Asimov", "genre":"sciencefiction" }, { "firstName":"Tad", "lastName":"Williams", "genre":"fantasy" }, { "firstName":"Frank", "lastName":"Peretti", "genre":"christianfiction" } ], "musicians":[ { "firstName":"Eric", "lastName":"Clapton", "instrument":"guitar" }, { "firstName":"Sergei", "lastName":"Rachmaninoff", "instrument":"piano" } ] } mysql> SELECT id,json_keys(person_desc) as "keys" FROM json_test\G *************************** 1. row *************************** id: 1 keys: ["authors", "musicians", "programmers"] 1 row in set (0.00 sec) mysql> SELECT json_extract(AUTHORS,'$.lastName[0]') AS 'name', AUTHORS FROM -> ( -> SELECT id,json_extract(person_desc,'$.authors[0][0]') AS "authors" FROM json_test -> UNION ALL -> SELECT id,json_extract(person_desc,'$.authors[1][0]') AS "authors" FROM json_test -> UNION ALL -> SELECT id,json_extract(person_desc,'$.authors[2][0]') AS "authors" FROM json_test -> ) AS T1 -> ORDER BY NAME DESC; +------------+----------------------------------------------------------------------------+ | name | AUTHORS | +------------+----------------------------------------------------------------------------+ | "Williams" | {"genre": "fantasy", "lastName": "Williams", "firstName": "Tad"} | | "Peretti" | {"genre": "christianfiction", "lastName": "Peretti", "firstName": "Frank"} | | "Asimov" | {"genre": "sciencefiction", "lastName": "Asimov", "firstName": "Isaac"} | +------------+----------------------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> SELECT -> json_extract(AUTHORS,'$.firstName[0]') AS "firstname", -> json_extract(AUTHORS,'$.lastName[0]') AS "lastname", -> json_extract(AUTHORS,'$.genre[0]') AS "genre" -> FROM -> ( -> SELECT id,json_extract(person_desc,'$.authors[0]') AS "authors" FROM json_test -> ) AS T\G ; *************************** 1. row *************************** firstname: "Isaac" lastname: "Asimov" genre: "sciencefiction" 1 row in set (0.00 sec) 修改的方法，注意加双引号： mysql> update json_test set person_desc=json_set(person_desc,"$.authors[2].firstName",'dong'); Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT id,json_extract(person_desc,'$.authors[2].firstName') AS "authors" FROM json_test; +------+---------+ | id | authors | +------+---------+ | 1 | "dong" | +------+---------+ 1 row in set (0.00 sec) 自己设计一个Json串的字段： { "deviceinfo": [{ "chineseName": "蛋壳孵化I型", "EnglishName": "LevooAllCanBeHatch", "deviceMacAdd": "00-50-56-C0-00-08" }], "tcpserverinfo": [{ "fd": "Isaac", "connTime": "2014-11-11 23:45:21", "connIp": "127.0.0.1" }], "websocketinfo": [{ "fd": "1", "connTime": "2014-11-11 23:45:21", "connIp": "127.0.0.1" }, { "fd": "21", "connTime": "2014-11-11 23:45:21", "connIp": "127.0.0.2" }] } mysql> update `json_test` set person_desc=json_set(person_desc, "$.deviceinfo[0].chineseName", '蛋壳108') where id=20; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM json_test where id=20\G; *************************** 1. row *************************** id: 20 person_desc: {"deviceinfo": [{"EnglishName": "LevooAllCanBeHatch", "chineseName": "蛋壳108", "deviceMacAdd": "00-50-56-C0-00-08"}], "tcpserverinfo": [{"fd": "Isaac", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}], "websocketinfo": [{"fd": "1", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, {"fd": "21", "connIp": "127.0.0.2", "connTime": "2014-11-11 23:45:21"}]} 1 row in set (0.00 sec) Json的格式更简单一些： { "deviceinfo": { "chineseName": "蛋壳孵化I型", "EnglishName": "LevooAllCanBeHatch", "deviceMacAdd": "00-50-56-C0-00-08" }, "tcpserverinfo": { "fd": "Isaac", "connTime": "2014-11-11 23:45:21", "connIp": "127.0.0.1" }, "websocketinfo": [{ "fd": "1", "connTime": "2014-11-11 23:45:21", "connIp": "127.0.0.1" }, { "fd": "21", "connTime": "2014-11-11 23:45:21", "connIp": "127.0.0.2" }] } mysql> update `json_test` set person_desc=json_set(person_desc, "$.deviceinf.chineseName", '蛋壳108') where id=30; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 ================================================================================================================= mysql> SELECT * FROM json_test where id=30\G; *************************** 1. row *************************** id: 30 person_desc: {"deviceinfo": {"EnglishName": "LevooAllCanBeHatch", "chineseName": "蛋壳孵化I型", "deviceMacAdd": "00-50-56-C0-00-08"}, "tcpserverinfo": {"fd": "Isaac", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, "websocketinfo": [{"fd": "1", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, {"fd": "21", "connIp": "127.0.0.2", "connTime": "2014-11-11 23:45:21"}]} 1 row in set (0.00 sec) mysql> update `json_test` set person_desc=json_set(person_desc, "$.deviceinfo.chineseName", '蛋壳1081') where id=30; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM json_test where id=30\G; *************************** 1. row *************************** id: 30 person_desc: {"deviceinfo": {"EnglishName": "LevooAllCanBeHatch", "chineseName": "蛋壳1081", "deviceMacAdd": "00-50-56-C0-00-08"}, "tcpserverinfo": {"fd": "Isaac", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, "websocketinfo": [{"fd": "1", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, {"fd": "21", "connIp": "127.0.0.2", "connTime": "2014-11-11 23:45:21"}]} 1 row in set (0.00 sec) ================================================================================================================= mysql> update `json_test` set person_desc=json_set(person_desc, "$.deviceinfo.chineseName", '蛋壳108109') where id=30; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM json_test where id=30\G; *************************** 1. row *************************** id: 30 person_desc: {"deviceinfo": {"EnglishName": "LevooAllCanBeHatch", "chineseName": "蛋壳108109", "deviceMacAdd": "00-50-56-C0-00-08"}, "tcpserverinfo": {"fd": "Isaac", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, "websocketinfo": [{"fd": "1", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, {"fd": "21", "connIp": "127.0.0.2", "connTime": "2014-11-11 23:45:21"}]} 1 row in set (0.00 sec) =================================================================================================================== mysql> update `json_test` set person_desc=json_set(person_desc, "$.tcpserverinfo.fd", '蛋壳108109') where id=30; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 ======================================================修改二级===================================================== mysql> update `json_test` set person_desc=json_set(person_desc, "$.websocketinfo[1].fd", '22') where id=30; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM json_test where id=30\G; *************************** 1. row *************************** id: 30 person_desc: {"deviceinfo": {"EnglishName": "LevooAllCanBeHatch", "chineseName": "蛋壳108109", "deviceMacAdd": "00-50-56-C0-00-08"}, "tcpserverinfo": {"fd": "蛋壳108109", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, "websocketinfo": [{"fd": "1", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, {"fd": "22", "connIp": "127.0.0.2", "connTime": "2014-11-11 23:45:21"}]} 1 row in set (0.00 sec) =================================插入新的数组属性=================================== mysql> update `json_test` set person_desc=json_insert(person_desc, "$.websocketinfo[1].fdfd", '2222') where id=30; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM json_test where id=30\G; *************************** 1. row *************************** id: 30 person_desc: {"deviceinfo": {"EnglishName": "LevooAllCanBeHatch", "chineseName": "蛋壳108109", "deviceMacAdd": "00-50-56-C0-00-08"}, "tcpserverinfo": {"fd": "蛋壳108109", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, "websocketinfo": [{"fd": "1", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, {"fd": "22", "fdfd": "2222", "connIp": "127.0.0.2", "connTime": "2014-11-11 23:45:21"}]} 最后想了一下，那个websocket是没有啥用的，直接简单化即可： { "deviceinfo":{ "EnglishName":"LevooAllCanBeHatch", "chineseName":"蛋壳108109", "deviceMacAdd":"00-50-56-C0-00-08" }, "tcpserverinfo":{ "fd":"蛋壳108109", "connIp":"127.0.0.1", "connTime":"2014-11-11 23:45:21" }, "websocketinfo":[ { "fd":"1", "connIp":"127.0.0.1", "connTime":"2014-11-11 23:45:21" } ] } josn类不能有如下默认值： { "deviceinfo":{ "EnglishName":"N/A", "chineseName":"N/A", "deviceMacAdd":"N/A" }, "tcpserverinfo":{ "fd":"N/A", "connIp":"N/A", "connTime":"N/A" }, "websocketinfo":[ { "fd":"N/A", "connIp":"N/A", "connTime":"N/A" } ] } Array ( [EnglishName] => N/A [chineseName] => N/A [deviceMacAdd] => N/A ) [tcpserverinfo] => Array ( [fd] => N/A [connIp] => N/A [connTime] => N/A ) [websocketinfo] => Array ( [0] => Array ( [fd] => N/A [connIp] => N/A [connTime] => N/A ) ) ) mysql> select json_extract(data,'$.tcpserverinfo.fd') as fd from hatch_dev_temp where hatchdevid=1; +----------------+ | fd | +----------------+ | "蛋壳108109" | +----------------+ 1 row in set (0.00 sec) ============================================================================================= mysql> SELECT * FROM json_test where id=30\G; *************************** 1. row *************************** id: 30 person_desc: {"deviceinfo": {"EnglishName": "LevooAllCanBeHatch", "chineseName": "蛋壳孵化I型", "deviceMacAdd": "00-50-56-C0-00-08"}, "tcpserverinfo": {"fd": "Isaac", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, "websocketinfo": [{"fd": "1", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, {"fd": "21", "connIp": "127.0.0.2", "connTime": "2014-11-11 23:45:21"}]} 1 row in set (0.00 sec) mysql> update `json_test` set person_desc=json_set(person_desc, "$.deviceinfo.chineseName", '蛋壳1081') where id=30; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM json_test where id=30\G; *************************** 1. row *************************** id: 30 person_desc: {"deviceinfo": {"EnglishName": "LevooAllCanBeHatch", "chineseName": "蛋壳1081", "deviceMacAdd": "00-50-56-C0-00-08"}, "tcpserverinfo": {"fd": "Isaac", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, "websocketinfo": [{"fd": "1", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, {"fd": "21", "connIp": "127.0.0.2", "connTime": "2014-11-11 23:45:21"}]} 1 row in set (0.00 sec) ============================================================================================= mysql> update `json_test` set person_desc=json_set(person_desc, "$.deviceinfo.chineseName", '蛋壳108109') where id=30; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM json_test where id=30\G; *************************** 1. row *************************** id: 30 person_desc: {"deviceinfo": {"EnglishName": "LevooAllCanBeHatch", "chineseName": "蛋壳108109", "deviceMacAdd": "00-50-56-C0-00-08"}, "tcpserverinfo": {"fd": "Isaac", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, "websocketinfo": [{"fd": "1", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, {"fd": "21", "connIp": "127.0.0.2", "connTime": "2014-11-11 23:45:21"}]} 1 row in set (0.00 sec) =================================================================================================================== mysql> update `json_test` set person_desc=json_set(person_desc, "$.tcpserverinfo.fd", '蛋壳108109') where id=30; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 ======================================================修改二级===================================================== mysql> update `json_test` set person_desc=json_set(person_desc, "$.websocketinfo[1].fd", '22') where id=30; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM json_test where id=30\G; *************************** 1. row *************************** id: 30 person_desc: {"deviceinfo": {"EnglishName": "LevooAllCanBeHatch", "chineseName": "蛋壳108109", "deviceMacAdd": "00-50-56-C0-00-08"}, "tcpserverinfo": {"fd": "蛋壳108109", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, "websocketinfo": [{"fd": "1", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, {"fd": "22", "connIp": "127.0.0.2", "connTime": "2014-11-11 23:45:21"}]} 1 row in set (0.00 sec) =================================插入新的数组属性=================================== mysql> update `json_test` set person_desc=json_insert(person_desc, "$.websocketinfo[1].fdfd", '2222') where id=30; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM json_test where id=30\G; *************************** 1. row *************************** id: 30 person_desc: {"deviceinfo": {"EnglishName": "LevooAllCanBeHatch", "chineseName": "蛋壳108109", "deviceMacAdd": "00-50-56-C0-00-08"}, "tcpserverinfo": {"fd": "蛋壳108109", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, "websocketinfo": [{"fd": "1", "connIp": "127.0.0.1", "connTime": "2014-11-11 23:45:21"}, {"fd": "22", "fdfd": "2222", "connIp": "127.0.0.2", "connTime": "2014-11-11 23:45:21"}]} 最后想了一下，那个websocket是没有啥用的，直接简单化即可： { "deviceinfo":{ "EnglishName":"LevooAllCanBeHatch", "chineseName":"蛋壳108109", "deviceMacAdd":"00-50-56-C0-00-08" }, "tcpserverinfo":{ "fd":"蛋壳108109", "connIp":"127.0.0.1", "connTime":"2014-11-11 23:45:21" }, "websocketinfo":[ { "fd":"1", "connIp":"127.0.0.1", "connTime":"2014-11-11 23:45:21" } ] } josn类不能有如下默认值： { "deviceinfo":{ "EnglishName":"N/A", "chineseName":"N/A", "deviceMacAdd":"N/A" }, "tcpserverinfo":{ "fd":"N/A", "connIp":"N/A", "connTime":"N/A" }, "websocketinfo":[ { "fd":"N/A", "connIp":"N/A", "connTime":"N/A" } ] } 修改tcpserver里的fd的句柄及查询该句柄的值： mysql> update hatch_dev_temp set data=json_set(data, "$.tcpserverinfo.fd", '108') where hatchdevid=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select json_extract(data,'$.tcpserverinfo.fd') as fd from hatch_dev_temp where hatchdevid=1; +-------+ | fd | +-------+ | "108" | +-------+ 1 row in set (0.00 sec) mysql> update hatch_dev_temp set data=json_set(data,"$.tcpserverinfo.fd",22) where hatchdevid=1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select json_extract(data,'$.tcpserverinfo.fd') as fd from hatch_dev_temp where hatchdevid=1; +------+ | fd | +------+ | 22 | +------+ 1 row in set (0.00 sec) EOF ======================================================================================== ALTER TABLE `XXX_dev_temp` ADD `data` JSON NOT NULL AFTER `XXX`; 修改json数据： JSON_SET(json_doc, path, val[, path, val] ...) 修改数据 update t set js=json_set('{"a":1,"s":"abc"}','$.a',456,'$.b','bbb') where id=1 结果js={"a":456,"s":"abc","b":"bbb"} path中$就代表整个doc,然后可以用javascript的方式指定对象属性或者数组下标等. 执行效果，类似json的语法 $.a=456 $.b="bbb" 存在就修改,不存在就设置. $.c.c=123 这个在javascript中会出错，因为.c为null。 但是在json_set('{}','$.c.c',123)中，不存在的路径将直接被忽略。 来自：http://blog.5ibc.net/p/36344.html http://jackyrong.iteye.com/blog/2282003 多唯json数组的修改处理方法： http://blog.csdn.net/yueliangdao0608/article/details/49760213 MySQL 5.7.7 labs版本开始InnoDB存储引擎已经原生支持JSON格式，该格式不是简单的BLOB类似的替换。原生的JSON格式支持有以下的优势： JSON数据有效性检查：BLOB类型无法在数据库层做这样的约束性检查 查询性能的提升：查询不需要遍历所有字符串才能找到数据 支持索引：通过虚拟列的功能可以对JSON中的部分数据进行索引 [root@iZ25dcp92ckZ ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1273700 Server version: 8.0.0-dmr-log Source distribution Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> create table user ( uid int auto_increment, data json,primary key(uid))engine=innodb; Query OK, 0 rows affected (0.54 sec) mysql> insert into user values (NULL, '{"name":"David","mail":"jiangchengyao@gmail.com","address":"Shangahai"}'); Query OK, 1 row affected (0.01 sec) mysql> insert into user values (NULL,'{"name":"Amy","mail":"amy@gmail.com"}'); Query OK, 1 row affected (0.00 sec) mysql> insert into user values (NULL,"test"); ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 1 in value for column 'user.data'. mysql> select jsn_extract(data, '$.name'),jsn_extract(data,'$.address') from user; ERROR 1305 (42000): FUNCTION test.jsn_extract does not exist mysql> select json_extract(data, '$.name'),json_extract(data,'$.address') from user; +------------------------------+--------------------------------+ | json_extract(data, '$.name') | json_extract(data,'$.address') | +------------------------------+--------------------------------+ | "David" | "Shangahai" | | "Amy" | NULL | +------------------------------+--------------------------------+ 2 rows in set (0.00 sec) mysql> ALTER TABLE user ADD user_name varchar(128) GENERATED ALWAYS AS (json_extract(data,'$.name')) VIRTUAL; Query OK, 0 rows affected (0.92 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select user_name from user; +-----------+ | user_name | +-----------+ | "David" | | "Amy" | +-----------+ 2 rows in set (0.00 sec) mysql> alter table user add index idx_username (user_name); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from user where user_name='"Amy"'\G; *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ref possible_keys: idx_username key: idx_username key_len: 387 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.01 sec) 来自：http://database.51cto.com/art/201504/472302.htm ------------------------------------------------------------------------------------------------------------------------- MySQL 5.7原生JSON格式支持: mysql> create table user ( uid int auto_increment, -> data json,primary key(uid))engine=innodb; Query OK, 0 rows affected (0.01 sec) mysql> insert into user values (NULL, -> '{"name":"David","mail":"jiangchengyao@gmail.com","address":"Shangahai"}'); Query OK, 1 row affected (0.00 sec) mysql> insert into user values (NULL,'{"name":"Amy","mail":"amy@gmail.com"}'); Query OK, 1 row affected (0.00 sec) 可以看到我们新建了表user，并且将列data定义为了JSON类型。这意味着我们可以对插入的数据做JSON格式检查，确保其符合JSON格式的约束，如插入一条不合法的JSON数据会报如下错误： mysql> insert into user values (NULL,"test"); ERROR 3130 (22032): Invalid JSON text: "Invalid value" at position 2 in value (or column) 'test'. 此外，正如前面所说的，MySQL 5.7提供了一系列函数来高效地处理JSON字符，而不是需要遍历所有字符来查找，这不得不说是对MariaDB dynamic column的巨大改进： mysql> select json_extract(data, '$.name'),json_extract(data,'$.address') from user; +-----------------------------+-------------------------------+ | json_extract(data, '$.name') | json_extract(data,'$.address') | +-----------------------------+-------------------------------+ | "David" | "Shangahai" | | "Amy" | NULL | +-----------------------------+-------------------------------+ 2 rows in set (0.00 sec) 当然，最令人的激动的功能应该是MySQL 5.7的虚拟列功能，通过传统的B+树索引即可实现对JSON格式部分属性的快速查询。使用方法是首先创建该虚拟列，然后在该虚拟列上创建索引： mysql> ALTER TABLE user ADD user_name varchar(128) -> GENERATED ALWAYS AS (json_extract(data,'$.name')) VIRTUAL; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select user_name from user; +-----------+ | user_name | +-----------+ | "Amy" | | "David" | +-----------+ 2 rows in set (0.00 sec) mysql> alter table user add index idx_username (user_name); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 然后可以通过添加的索引对用户名进行快速的查询，这和普通类型的列查询一样。而通过explain可以验证优化器已经选择了在虚拟列上创建的新索引： mysql> explain select * from user where user_name='"Amy"'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ref possible_keys: idx_username key: idx_username key_len: 131 ref: const rows: 1 filtered: 100.00 Extra: NULL 1 row in set, 1 warning (0.00 sec) 可以发现MySQL 5.7对于JSON格式堪称完美 摘自：http://www.innomysql.net/article/15319.html 修改json地址： mysql> set @json='["apple", {"attr": [50, true], "name": "orange"}]'; Query OK, 0 rows affected (0.00 sec) mysql> select json_insert(@json, '$[1].attr[0]', 2, '$[2]', "pear"); +-----------------------------------------------------------+ | json_insert(@json, '$[1].attr[0]', 2, '$[2]', "pear") | +-----------------------------------------------------------+ | ["apple", {"attr": [50, true], "name": "orange"}, "pear"] | +-----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select json_replace(@json, '$[1].attr[0]', 2, '$[2]', "pear"); +--------------------------------------------------------+ | json_replace(@json, '$[1].attr[0]', 2, '$[2]', "pear") | +--------------------------------------------------------+ | ["apple", {"attr": [2, true], "name": "orange"}] | +--------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select json_set(@json, '$[1].attr[0]', 2, '$[2]', "pear"); +----------------------------------------------------------+ | json_set(@json, '$[1].attr[0]', 2, '$[2]', "pear") | +----------------------------------------------------------+ | ["apple", {"attr": [2, true], "name": "orange"}, "pear"] | +----------------------------------------------------------+ 1 row in set (0.01 sec) mysql> select json_remove(@json, '$[1].attr[0]', '$[2]'); +-----------------------------------------------+ | json_remove(@json, '$[1].attr[0]', '$[2]') | +-----------------------------------------------+ | ["apple", {"attr": [true], "name": "orange"}] | +-----------------------------------------------+ 1 row in set (0.00 sec) Generated by Jackxiang's Bo-blog 2.1.1 Release