向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除|赢在IT，Playin' with IT,Focus on Killer Application,Marketing Meets Technology.

背景：在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 = <<< EOF {     "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"         }       ]   } EOF; echo $json; $jsonArr = json_decode($json,true); print_r($jsonArr);

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
========================================================================================
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)

作者：jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
地址：http://jackxiang.com/post/9071/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明！