MySQL 5.7原生JSON格式支持，对于JSON格式堪称完美，修改某个字段的json。

Fri, 25 Nov 2016 02:09:16 +0000

背景：在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"
        }
    ]
}

php json.php
{
    "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
(
    [deviceinfo] => 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
========================================================================================

修改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

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> 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格式支持:

可以看到我们新建了表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 5.7的虚拟列功能，通过传统的B+树索引即可实现对JSON格式部分属性的快速查询。使用方法是首先创建该虚拟列，然后在该虚拟列上创建索引：

然后可以通过添加的索引对用户名进行快速的查询，这和普通类型的列查询一样。而通过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)

向东博客专注WEB应用构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除

MySQL 5.7原生JSON格式支持，对于JSON格式堪称完美，修改某个字段的json。

[评论] MySQL 5.7原生JSON格式支持，对于JSON格式堪称完美，修改某个字段的json。

向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除

MySQL 5.7原生JSON格式支持，对于JSON格式堪称完美，修改某个字段的json。

[评论] MySQL 5.7原生JSON格式支持，对于JSON格式堪称完美，修改某个字段的json。

向东博客专注WEB应用构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除