mysql5.8特性_MYSQL5.8----M4-5

mysql> SET @a = JSON_ARRAY(1,2,3,4);

Query OK,0 rows affected (0.00sec)

mysql>SELECT @a;+--------------+

| @a |

+--------------+

| [1, 2, 3, 4] |

+--------------+

1 row in set (0.00sec)

mysql> SELECT JSON_ARRAY_INSERT(@a,"$[0]","quan");+-------------------------------------+

| JSON_ARRAY_INSERT(@a,"$[0]","quan") |

+-------------------------------------+

| ["quan", 1, 2, 3, 4] |

+-------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_ARRAY_INSERT(@a,"$[3]","zhi");+------------------------------------+

| JSON_ARRAY_INSERT(@a,"$[3]","zhi") |

+------------------------------------+

| [1, 2, 3, "zhi", 4] |

+------------------------------------+

1 row in set (0.00sec)

mysql> SET @a = JSON_ARRAY_INSERT(@a,"$[0]","quan");

Query OK,0 rows affected (0.00sec)

mysql>SELECT @a;+----------------------+

| @a |

+----------------------+

| ["quan", 1, 2, 3, 4] |

+----------------------+

1 row in set (0.00sec)

mysql> SET @a = JSON_ARRAY_INSERT(@a,"$[3]","zhi");

Query OK,0 rows affected (0.00sec)

mysql>SELECT @a;+-----------------------------+

| @a |

+-----------------------------+

| ["quan", 1, 2, "zhi", 3, 4] |

+-----------------------------+

1 row in set (0.00sec)

mysql> SET @b = JSON_OBJECT("id",11,"name","quanzhiqiang");

Query OK,0 rows affected (0.00sec)

mysql>SELECT @b;+------------------------------------+

| @b |

+------------------------------------+

| {"id": 11, "name": "quanzhiqiang"} |

+------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_INSERT(@b,"$.age",90);+-----------------------------------------------+

| JSON_INSERT(@b,"$.age",90) |

+-----------------------------------------------+

| {"id": 11, "age": 90, "name": "quanzhiqiang"} |

+-----------------------------------------------+

1 row in set (0.00sec)

mysql>SELECT @a;+-----------------------------+

| @a |

+-----------------------------+

| ["quan", 1, 2, "zhi", 3, 4] |

+-----------------------------+

1 row in set (0.00sec)

mysql>SELECT @b;+------------------------------------+

| @b |

+------------------------------------+

| {"id": 11, "name": "quanzhiqiang"} |

+------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_ARRAY_APPEND(@a,"$[0]","111","$[4]","4444");+--------------------------------------------------+

| JSON_ARRAY_APPEND(@a,"$[0]","111","$[4]","4444") |

+--------------------------------------------------+

| [["quan", "111"], 1, 2, "zhi", [3, "4444"], 4] |

+--------------------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_ARRAY_APPEND(@b,"$.name","namename");+--------------------------------------------------+

| JSON_ARRAY_APPEND(@b,"$.name","namename") |

+--------------------------------------------------+

| {"id": 11, "name": ["quanzhiqiang", "namename"]} |

+--------------------------------------------------+

1 row in set (0.00sec)

mysql>SELECT @b;+------------------------------------+

| @b |

+------------------------------------+

| {"id": 11, "name": "quanzhiqiang"} |

+------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_SET(@b,"$.id",90,"$.age",99);+-----------------------------------------------+

| JSON_SET(@b,"$.id",90,"$.age",99) |

+-----------------------------------------------+

| {"id": 90, "age": 99, "name": "quanzhiqiang"} |

+-----------------------------------------------+

1 row in set (0.00sec)

mysql>SELECT @b;+------------------------------------+

| @b |

+------------------------------------+

| {"id": 11, "name": "quanzhiqiang"} |

+------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_REPLACE(@b,"$.id",90,"$.age",99);+---------------------------------------+

| JSON_REPLACE(@b,"$.id",90,"$.age",99) |

+---------------------------------------+

| {"id": 90, "name": "quanzhiqiang"} |

+---------------------------------------+

1 row in set (0.00sec)

mysql> SELECT *FROM joson;+----+------------------------------+

| id | context |

+----+------------------------------+

| 1 | {"1": 2, "3": 4, "AA": "BB"} |

+----+------------------------------+

1 row in set (0.00sec)

mysql>SELECT context FROM joson->;+------------------------------+

| context |

+------------------------------+

| {"1": 2, "3": 4, "AA": "BB"} |

+------------------------------+

1 row in set (0.00sec)

mysql> UPDATE joson SET context = JSON_SET(context,"$.sex",1,"$.homw",2);

Query OK,1 row affected (0.11sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> SELECT *FROM joson;+----+---------------------------------------------------+

| id | context |

+----+---------------------------------------------------+

| 1 | {"1": 2, "3": 4, "AA": "BB", "sex": 1, "homw": 2} |

+----+---------------------------------------------------+

1 row in set (0.00sec)

mysql>SELECT @c;+-----------------------+

| @c |

+-----------------------+

| {"sex": 1, "homw": 2} |

+-----------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_REMOVE(@c,"$.sex");+-------------------------+

| JSON_REMOVE(@c,"$.sex") |

+-------------------------+

| {"homw": 2} |

+-------------------------+

1 row in set (0.00sec)

mysql> SELECT *FROM joson;+----+---------------------------------------------------+

| id | context |

+----+---------------------------------------------------+

| 1 | {"1": 2, "3": 4, "AA": "BB", "sex": 1, "homw": 2} |

+----+---------------------------------------------------+

1 row in set (0.00sec)

mysql> UPDATE joson SET context = JSON_REMOVE(context,"$.sex");

Query OK,1 row affected (0.00sec)

Rows matched:1 Changed: 1 Warnings: 0mysql> SELECT *FROM joson;+----+-----------------------------------------+

| id | context |

+----+-----------------------------------------+

| 1 | {"1": 2, "3": 4, "AA": "BB", "homw": 2} |

+----+-----------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_EXTRACT(context,"$.homw") FROM joson;+--------------------------------+

| JSON_EXTRACT(context,"$.homw") |

+--------------------------------+

| 2 |

+--------------------------------+

1 row in set (0.00sec)

mysql> SELECT context->"$.homw"FROM joson;+-------------------+

| context->"$.homw" |

+-------------------+

| 2 |

+-------------------+

1 row in set (0.00sec)

mysql>SELECT @a;+-----------------------------+

| @a |

+-----------------------------+

| ["quan", 1, 2, "zhi", 3, 4] |

+-----------------------------+

1 row in set (0.00sec)

mysql>SELECT JSON_LENGTH(@a);+-----------------+

| JSON_LENGTH(@a) |

+-----------------+

| 6 |

+-----------------+

1 row in set (0.00sec)

mysql>SELECT @b;+------------------------------------+

| @b |

+------------------------------------+

| {"id": 11, "name": "quanzhiqiang"} |

+------------------------------------+

1 row in set (0.00sec)

mysql>SELECT JSON_LENGTH(@b);+-----------------+

| JSON_LENGTH(@b) |

+-----------------+

| 2 |

+-----------------+

1 row in set (0.00sec)

mysql> SELECT JSON_CONTAINS_PATH(@b,"one","$.id");+-------------------------------------+

| JSON_CONTAINS_PATH(@b,"one","$.id") |

+-------------------------------------+

| 1 |

+-------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_CONTAINS_PATH(@b,"one","$.idd");+--------------------------------------+

| JSON_CONTAINS_PATH(@b,"one","$.idd") |

+--------------------------------------+

| 0 |

+--------------------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_CONTAINS_PATH(@b,"one","$.id","$[11]");+---------------------------------------------+

| JSON_CONTAINS_PATH(@b,"one","$.id","$[11]") |

+---------------------------------------------+

| 1 |

+---------------------------------------------+

1 row in set (0.00sec)

mysql>SELECT @a;+-----------------------------+

| @a |

+-----------------------------+

| ["quan", 1, 2, "zhi", 3, 4] |

+-----------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_CONTAINS(@a,"1","$[1]");+------------------------------+

| JSON_CONTAINS(@a,"1","$[1]") |

+------------------------------+

| 1 |

+------------------------------+

1 row in set (0.00sec)

mysql>SELECT @a;+-----------------------------+

| @a |

+-----------------------------+

| ["quan", 1, 2, "zhi", 3, 4] |

+-----------------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_CONTAINS(@a,"1");+-----------------------+

| JSON_CONTAINS(@a,"1") |

+-----------------------+

| 1 |

+-----------------------+

1 row in set (0.00sec)

mysql> SELECT JSON_CONTAINS(@a,"3");+-----------------------+

| JSON_CONTAINS(@a,"3") |

+-----------------------+

| 1 |

+-----------------------+

1 row in set (0.00 sec)