自己免费制作app平台,自助seo网站建设,win2008 r2 搭建网站,东阿网站建设费用MySQL数据类型之JSON
SON类型是MySQL 5.7版本新增的数据类型#xff0c;用好JSON数据类型可以有效解决很多业务中实际问题。
使用JSON数据类型#xff0c;推荐用MySQL 8.0.17以上的版本#xff0c;性能更好#xff0c;同时也支持Multi-Valued Indexes#xff1b;
JSON数…MySQL数据类型之JSON
SON类型是MySQL 5.7版本新增的数据类型用好JSON数据类型可以有效解决很多业务中实际问题。
使用JSON数据类型推荐用MySQL 8.0.17以上的版本性能更好同时也支持Multi-Valued Indexes
JSON数据类型的好处是无须预先定义列数据本身就具有很好的描述性
不要将有明显关系型的数据用JSON存储如用户余额、用户姓名、用户身份证等这些都是每个用户必须包含的数据
JSON数据类型推荐使用在不经常更新的静态数据存储。
JSON数据类型
mysql create table json_user(id int primary key auto_increment, data json);
Query OK, 0 rows affected (0.09 sec)mysql insert into json_user values(0, {name:morris,age:18,address:china});
Query OK, 1 row affected (0.03 sec)mysql insert into json_user values(0, {name:tom,age:16,mail:tomcatgoogle.com});
Query OK, 1 row affected (0.01 sec)mysql select * from json_user;
-------------------------------------------------------------
| id | data |
-------------------------------------------------------------
| 1 | {age: 18, name: morris, address: china} |
| 2 | {age: 16, mail: tomcatgoogle.com, name: tom} |
-------------------------------------------------------------
2 rows in set (0.01 sec)
常见JSON函数的使用 json_extract抽取字段
mysql select json_extract([1, 2, 3, [4, 5]], $[1]);
-------------------------------------------
| json_extract([1, 2, 3, [4, 5]], $[1]) |
-------------------------------------------
| 2 |
-------------------------------------------
1 row in set (0.02 sec)mysql select json_extract({age: 18, name: morris, address: china}, $.name);
-----------------------------------------------------------------------------
| json_extract({age: 18, name: morris, address: china}, $.name) |
-----------------------------------------------------------------------------
| morris |
-----------------------------------------------------------------------------
1 row in set (0.00 sec)mysql select data-$.name from json_user;
----------------
| data-$.name |
----------------
| morris |
| tom |
----------------
2 rows in set (0.00 sec)
json_object将对象转为json
mysql select json_object(name,bob,age,22,email,bobsina.com);
-------------------------------------------------------------
| json_object(name,bob,age,22,email,bobsina.com) |
-------------------------------------------------------------
| {age: 22, name: bob, email: bobsina.com} |
-------------------------------------------------------------
1 row in set (0.01 sec)json_insert插入数据 key存在则忽略不存在则插入。
mysql select json_insert({age: 18, name: morris, address: china}, $.male, male, $.age, 22);
-------------------------------------------------------------------------------------------------
| json_insert({age: 18, name: morris, address: china}, $.male, male, $.age, 22) |
-------------------------------------------------------------------------------------------------
| {age: 18, male: male, name: morris, address: china} |
-------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
json_merge合并数据 json_merge已不推荐使用与json_merge_preserve类似相同的key会合并为数组。
json_merge_patch中相同的key会使用后面的key替换前面的key。
mysql select json_merge_patch({name: enjoy}, {id: 47, name:morris});
----------------------------------------------------------------------
| json_merge_patch({name: enjoy}, {id: 47, name:morris}) |
----------------------------------------------------------------------
| {id: 47, name: morris} |
----------------------------------------------------------------------
1 row in set (0.00 sec)mysql select json_merge({name: enjoy}, {id: 47, name:morris});
----------------------------------------------------------------
| json_merge({name: enjoy}, {id: 47, name:morris}) |
----------------------------------------------------------------
| {id: 47, name: [enjoy, morris]} |
----------------------------------------------------------------
1 row in set, 1 warning (0.00 sec)mysql select json_merge_preserve({name: enjoy}, {id: 47, name:morris});
-------------------------------------------------------------------------
| json_merge_preserve({name: enjoy}, {id: 47, name:morris}) |
-------------------------------------------------------------------------
| {id: 47, name: [enjoy, morris]} |
-------------------------------------------------------------------------
1 row in set (0.00 sec)
json_array_append后面追加元素
mysql select json_array_append([a, b, c], $, x);
------------------------------------------------
| json_array_append([a, b, c], $, x) |
------------------------------------------------
| [a, b, c, x] |
------------------------------------------------
1 row in set (0.00 sec)json_array_insert里面插入元素
mysql select json_array_insert([a, b, c], $[1], x);
---------------------------------------------------
| json_array_insert([a, b, c], $[1], x) |
---------------------------------------------------
| [a, x, b, c] |
---------------------------------------------------
1 row in set (0.00 sec)json_contains包含某个json子串
mysql select json_contains({a:1,b:4},{a:1});
------------------------------------------
| json_contains({a:1,b:4},{a:1}) |
------------------------------------------
| 1 |
------------------------------------------
1 row in set (0.00 sec)mysql select json_contains({age: 18, name: morris, address: china}, 18, $.name);
------------------------------------------------------------------------------------
| json_contains({age: 18, name: morris, address: china}, 18, $.name) |
------------------------------------------------------------------------------------
| 0 |
------------------------------------------------------------------------------------
1 row in set (0.00 sec)json_array创建json数组
mysql select json_array(a, b, c, d);
--------------------------------
| json_array(a, b, c, d) |
--------------------------------
| [a, b, c, d] |
--------------------------------
1 row in set (0.00 sec)json_contains_path判断json是否包含某个key mysql select json_contains_path({a: 1, b: 2, c: {d: 4}}, one, $.a, $.e);
----------------------------------------------------------------------------
| json_contains_path({a: 1, b: 2, c: {d: 4}}, one, $.a, $.e) |
----------------------------------------------------------------------------
| 1 |
----------------------------------------------------------------------------
1 row in set (0.01 sec)mysql select json_contains_path({a: 1, b: 2, c: {d: 4}}, all, $.a, $.e);
----------------------------------------------------------------------------
| json_contains_path({a: 1, b: 2, c: {d: 4}}, all, $.a, $.e) |
----------------------------------------------------------------------------
| 0 |
----------------------------------------------------------------------------
1 row in set (0.00 sec)
json_keys查询json所有的key
mysql select json_keys({a: 1, b: 2, c: {d: 4}});
----------------------------------------------
| json_keys({a: 1, b: 2, c: {d: 4}}) |
----------------------------------------------
| [a, b, c] |
----------------------------------------------
1 row in set (0.00 sec)json_pretty格式化json
mysql select json_pretty({a: 1, b: 2, c: {d: 4}});
-------------------------------------------------
| json_pretty({a: 1, b: 2, c: {d: 4}}) |
-------------------------------------------------
| {a: 1,b: 2,c: {d: 4}
} |
-------------------------------------------------
1 row in set (0.00 sec)json_depth查询json的深度
mysql select json_depth({a: 1, b: 2, c: {d: 4}});
-----------------------------------------------
| json_depth({a: 1, b: 2, c: {d: 4}}) |
-----------------------------------------------
| 3 |
-----------------------------------------------
1 row in set (0.00 sec)json_length返回json元素的个数
mysql select json_length({a: 1, b: 2, c: {d: 4}});
------------------------------------------------
| json_length({a: 1, b: 2, c: {d: 4}}) |
------------------------------------------------
| 3 |
------------------------------------------------
1 row in set (0.00 sec)
————————————————json_remove根据key删除元素
mysql select json_remove({a: 1, b: 2, c: {d: 4}}, $.c);
-------------------------------------------------------
| json_remove({a: 1, b: 2, c: {d: 4}}, $.c) |
-------------------------------------------------------
| {a: 1, b: 2} |
-------------------------------------------------------
1 row in set (0.00 sec)json_replace替换元素
mysql select json_replace({a: 1, b: 2, c: {d: 4}}, $.c, cc);
--------------------------------------------------------------
| json_replace({a: 1, b: 2, c: {d: 4}}, $.c, cc) |
--------------------------------------------------------------
| {a: 1, b: 2, c: cc} |
--------------------------------------------------------------
1 row in set (0.01 sec)json_search搜索元素
mysql select json_search([abc, [{k: 10}, def], {x:abc}, {y:bcd}], one, abc);
--------------------------------------------------------------------------------------
| json_search([abc, [{k: 10}, def], {x:abc}, {y:bcd}], one, abc) |
--------------------------------------------------------------------------------------
| $[0] |
--------------------------------------------------------------------------------------
1 row in set (0.01 sec)mysql select json_search([abc, [{k: 10}, def], {x:abc}, {y:bcd}], all, abc);
--------------------------------------------------------------------------------------
| json_search([abc, [{k: 10}, def], {x:abc}, {y:bcd}], all, abc) |
--------------------------------------------------------------------------------------
| [$[0], $[2].x] |
--------------------------------------------------------------------------------------
1 row in set (0.00 sec)json_set往json中插入元素 存在则覆盖不存在则新增。
mysql select json_set({a: 1, b: 2, c: {d: 4}}, $.a, aaa, $.e, eee);
-------------------------------------------------------------------------
| json_set({a: 1, b: 2, c: {d: 4}}, $.a, aaa, $.e, eee) |
-------------------------------------------------------------------------
| {a: aaa, b: 2, c: {d: 4}, e: eee} |
-------------------------------------------------------------------------
1 row in set (0.00 sec)json_storage_size查看json存储占用的空间大小
mysql select json_storage_size({a: 1, b: 2, c: {d: 4}});
------------------------------------------------------
| json_storage_size({a: 1, b: 2, c: {d: 4}}) |
------------------------------------------------------
| 41 |
------------------------------------------------------
1 row in set (0.00 sec)json_type查询json的类型
mysql select json_type({a: 1, b: 2, c: {d: 4}});
----------------------------------------------
| json_type({a: 1, b: 2, c: {d: 4}}) |
----------------------------------------------
| OBJECT |
----------------------------------------------
1 row in set (0.01 sec)json_valid判断json是否是有效的
mysql select json_valid({a: 1, b: 2, c: {d: 4}});
-----------------------------------------------
| json_valid({a: 1, b: 2, c: {d: 4}}) |
-----------------------------------------------
| 1 |
-----------------------------------------------
1 row in set (0.00 sec)JSON索引 JSON类型数据本身无法直接创建索引需要将需要索引的JSON数据重新生成虚拟列Virtual Columns之后对该列进行索引。
mysql create table test_json_index(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (json_extract(data, $.name)));
mysql insert into test_json_index(data) values({name:morris,age:18,address:china});
mysql insert into test_json_index(data) values({name:tom,age:16,mail:tomcatgoogle.com});
mysql select * from test_json_index;
-----------------------------------------------------------------------
| id | data | gen_col |
-----------------------------------------------------------------------
| 1 | {age: 18, name: morris, address: china} | morris |
| 2 | {age: 16, mail: tomcatgoogle.com, name: tom} | tom |
-----------------------------------------------------------------------
mysql select * from test_json_index where gen_colmorris; -- 查不到数据
mysql select * from test_json_index where gen_colmorris;
-----------------------------------------------------------------
| id | data | gen_col |
-----------------------------------------------------------------
| 1 | {age: 18, name: morris, address: china} | morris |
-----------------------------------------------------------------要想在查询时不加引号可以在加索引时使用json_unquote去除引号。
mysql create table test_json_index2(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (json_unquote(json_extract(data, $.name))));
Query OK, 0 rows affected (0.08 sec)mysql create table test_json_index3(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (JSON_UNQUOTE(data-$.name)));
Query OK, 0 rows affected (0.08 sec)mysql create table test_json_index4(id int primary key auto_increment, data json, gen_col varchar(10) generated always as (data-$.name));
Query OK, 0 rows affected (0.05 sec)
