MYSQL5.8----M4-5
2024-08-31 08:18:02
mysql> CREATE TABLE joson( id INT AUTO_INCREMENT PRIMARY KEY, context JSON NOT NULL)//
Query OK, 0 rows affected (0.05 sec)
mysql> DESC joson;
+---------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| context | json | NO | | NULL | |
+---------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec) mysql> SELECT JSON_ARRAY(1,2,3,4,"AA","BB");
+-------------------------------+
| JSON_ARRAY(1,2,3,4,"AA","BB") |
+-------------------------------+
| [1, 2, 3, 4, "AA", "BB"] |
+-------------------------------+
1 row in set (0.13 sec) mysql> SELECT JSON_OBJECT(1,2,3,4,"AA","BB")
-> ;
+--------------------------------+
| JSON_OBJECT(1,2,3,4,"AA","BB") |
+--------------------------------+
| {"1": 2, "3": 4, "AA": "BB"} |
+--------------------------------+
1 row in set (0.00 sec)
mysql> SET @data = JSON_OBJECT(1,2,3,4,"AA","BB");
Query OK, 0 rows affected (0.00 sec) mysql> SELECT JSON_PRETTY(@data);
+--------------------------------------+
| JSON_PRETTY(@data) |
+--------------------------------------+
| {
"1": 2,
"3": 4,
"AA": "BB"
} |
+--------------------------------------+
1 row in set (0.08 sec) mysql> DESC joson;
+---------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| context | json | NO | | NULL | |
+---------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec) mysql> INSERT INTO joson(context) VALUES(@data);
Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM joson;
+----+------------------------------+
| id | context |
+----+------------------------------+
| 1 | {"1": 2, "3": 4, "AA": "BB"} |
+----+------------------------------+
1 row in set (0.00 sec)
mysql> SET @a = JSON_ARRAY(1,2,3,4);
Query OK, 0 rows affected (0.00 sec) mysql> SELECT @a;
+--------------+
| @a |
+--------------+
| [1, 2, 3, 4] |
+--------------+
1 row in set (0.00 sec) mysql> SELECT JSON_ARRAY_INSERT(@a,"$[0]","quan");
+-------------------------------------+
| JSON_ARRAY_INSERT(@a,"$[0]","quan") |
+-------------------------------------+
| ["quan", 1, 2, 3, 4] |
+-------------------------------------+
1 row in set (0.00 sec) mysql> SELECT JSON_ARRAY_INSERT(@a,"$[3]","zhi");
+------------------------------------+
| JSON_ARRAY_INSERT(@a,"$[3]","zhi") |
+------------------------------------+
| [1, 2, 3, "zhi", 4] |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SET @a = JSON_ARRAY_INSERT(@a,"$[0]","quan");
Query OK, 0 rows affected (0.00 sec) mysql> SELECT @a;
+----------------------+
| @a |
+----------------------+
| ["quan", 1, 2, 3, 4] |
+----------------------+
1 row in set (0.00 sec) mysql> SET @a = JSON_ARRAY_INSERT(@a,"$[3]","zhi");
Query OK, 0 rows affected (0.00 sec) mysql> SELECT @a;
+-----------------------------+
| @a |
+-----------------------------+
| ["quan", 1, 2, "zhi", 3, 4] |
+-----------------------------+
1 row in set (0.00 sec) mysql> SET @b = JSON_OBJECT("id",11,"name","quanzhiqiang");
Query OK, 0 rows affected (0.00 sec) mysql> SELECT @b;
+------------------------------------+
| @b |
+------------------------------------+
| {"id": 11, "name": "quanzhiqiang"} |
+------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_INSERT(@b,"$.age",90);
+-----------------------------------------------+
| JSON_INSERT(@b,"$.age",90) |
+-----------------------------------------------+
| {"id": 11, "age": 90, "name": "quanzhiqiang"} |
+-----------------------------------------------+
1 row in set (0.00 sec) mysql> SELECT @a;
+-----------------------------+
| @a |
+-----------------------------+
| ["quan", 1, 2, "zhi", 3, 4] |
+-----------------------------+
1 row in set (0.00 sec) mysql> SELECT @b;
+------------------------------------+
| @b |
+------------------------------------+
| {"id": 11, "name": "quanzhiqiang"} |
+------------------------------------+
1 row in set (0.00 sec)
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.00 sec) mysql> SELECT JSON_ARRAY_APPEND(@b,"$.name","namename");
+--------------------------------------------------+
| JSON_ARRAY_APPEND(@b,"$.name","namename") |
+--------------------------------------------------+
| {"id": 11, "name": ["quanzhiqiang", "namename"]} |
+--------------------------------------------------+
1 row in set (0.00 sec) mysql> SELECT @b;
+------------------------------------+
| @b |
+------------------------------------+
| {"id": 11, "name": "quanzhiqiang"} |
+------------------------------------+
1 row in set (0.00 sec)
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.00 sec) mysql> SELECT @b;
+------------------------------------+
| @b |
+------------------------------------+
| {"id": 11, "name": "quanzhiqiang"} |
+------------------------------------+
1 row in set (0.00 sec) 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.00 sec) mysql> SELECT * FROM joson;
+----+------------------------------+
| id | context |
+----+------------------------------+
| 1 | {"1": 2, "3": 4, "AA": "BB"} |
+----+------------------------------+
1 row in set (0.00 sec) mysql> SELECT context FROM joson
-> ;
+------------------------------+
| context |
+------------------------------+
| {"1": 2, "3": 4, "AA": "BB"} |
+------------------------------+
1 row in set (0.00 sec)
mysql> UPDATE joson SET context = JSON_SET(context,"$.sex",1,"$.homw",2);
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM joson;
+----+---------------------------------------------------+
| id | context |
+----+---------------------------------------------------+
| 1 | {"1": 2, "3": 4, "AA": "BB", "sex": 1, "homw": 2} |
+----+---------------------------------------------------+
1 row in set (0.00 sec) mysql> SELECT @c;
+-----------------------+
| @c |
+-----------------------+
| {"sex": 1, "homw": 2} |
+-----------------------+
1 row in set (0.00 sec) mysql> SELECT JSON_REMOVE(@c,"$.sex");
+-------------------------+
| JSON_REMOVE(@c,"$.sex") |
+-------------------------+
| {"homw": 2} |
+-------------------------+
1 row in set (0.00 sec) mysql> SELECT * FROM joson;
+----+---------------------------------------------------+
| id | context |
+----+---------------------------------------------------+
| 1 | {"1": 2, "3": 4, "AA": "BB", "sex": 1, "homw": 2} |
+----+---------------------------------------------------+
1 row in set (0.00 sec) mysql> UPDATE joson SET context = JSON_REMOVE(context,"$.sex");
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM joson;
+----+-----------------------------------------+
| id | context |
+----+-----------------------------------------+
| 1 | {"1": 2, "3": 4, "AA": "BB", "homw": 2} |
+----+-----------------------------------------+
1 row in set (0.00 sec) mysql> SELECT JSON_EXTRACT(context,"$.homw") FROM joson;
+--------------------------------+
| JSON_EXTRACT(context,"$.homw") |
+--------------------------------+
| 2 |
+--------------------------------+
1 row in set (0.00 sec) mysql> SELECT context->"$.homw" FROM joson;
+-------------------+
| context->"$.homw" |
+-------------------+
| 2 |
+-------------------+
1 row in set (0.00 sec) mysql> SELECT @a;
+-----------------------------+
| @a |
+-----------------------------+
| ["quan", 1, 2, "zhi", 3, 4] |
+-----------------------------+
1 row in set (0.00 sec) mysql> SELECT JSON_LENGTH(@a);
+-----------------+
| JSON_LENGTH(@a) |
+-----------------+
| 6 |
+-----------------+
1 row in set (0.00 sec) mysql> SELECT @b;
+------------------------------------+
| @b |
+------------------------------------+
| {"id": 11, "name": "quanzhiqiang"} |
+------------------------------------+
1 row in set (0.00 sec) mysql> SELECT JSON_LENGTH(@b);
+-----------------+
| JSON_LENGTH(@b) |
+-----------------+
| 2 |
+-----------------+
1 row in set (0.00 sec) mysql> SELECT JSON_CONTAINS_PATH(@b,"one","$.id");
+-------------------------------------+
| JSON_CONTAINS_PATH(@b,"one","$.id") |
+-------------------------------------+
| 1 |
+-------------------------------------+
1 row in set (0.00 sec) mysql> SELECT JSON_CONTAINS_PATH(@b,"one","$.idd");
+--------------------------------------+
| JSON_CONTAINS_PATH(@b,"one","$.idd") |
+--------------------------------------+
| 0 |
+--------------------------------------+
1 row in set (0.00 sec) mysql> SELECT JSON_CONTAINS_PATH(@b,"one","$.id","$[11]");
+---------------------------------------------+
| JSON_CONTAINS_PATH(@b,"one","$.id","$[11]") |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row in set (0.00 sec) mysql> SELECT @a;
+-----------------------------+
| @a |
+-----------------------------+
| ["quan", 1, 2, "zhi", 3, 4] |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(@a,"1","$[1]");
+------------------------------+
| JSON_CONTAINS(@a,"1","$[1]") |
+------------------------------+
| 1 |
+------------------------------+
1 row in set (0.00 sec) mysql> SELECT @a;
+-----------------------------+
| @a |
+-----------------------------+
| ["quan", 1, 2, "zhi", 3, 4] |
+-----------------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(@a,"1");
+-----------------------+
| JSON_CONTAINS(@a,"1") |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT JSON_CONTAINS(@a,"3");
+-----------------------+
| JSON_CONTAINS(@a,"3") |
+-----------------------+
| 1 |
+-----------------------+
1 row in set (0.00 sec)
最新文章
- iframe高度宽度自适应（转）
- HTML—one
- 利用utl_file来读取文件.
- 数据结构（堆）：POJ 1442 Black Box
- python 安装中的错误解决
- sdut2623--The number of steps（概率dp第一弹，求期望）
- mongodb 学习初探
- hdu_5750_Dertouzos(线性筛)
- Spring Security-用户密码自定义加密
- JSON & XML
- RPC 知识科普一下
- GCC编译器原理（三）------编译原理三：编译过程---预处理
- [mvc] 过滤器filter一览
- falsk 与 django 钩子方法
- sas infile 控制导入长度
- css3选择
- 【LOJ】#2525. 「HAOI2018」字串覆盖
- 修改 input 框输入光标的样式
- Codeforces735D Taxes 2016-12-13 12:14 56人阅读 评论(0) 收藏
- NYOJ-------三角形