Mysql5.7使用JSON数据标准示范.doc

《Mysql5.7使用JSON数据标准示范.doc》由会员分享，可在线阅读，更多相关《Mysql5.7使用JSON数据标准示范.doc（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、.目录一、创建表4二、插入数据4三、查询所有数据51. 按找user_info中的数据精确查询,数据格式需要与json数据中的存储方式完全一致52. - 等价于JSON_EXTRACT53. JSON_SEARCH(字段名,one or all,条件)，没找到one 和 all 的具体区别54.JSON_CONTAINS判断key是否包含指定的值,JSON_CONTAINS(字段名,条件,key);0为不包含，1为包含；55. JSON_LENGTH(字段名),查询字段的json节点数66.json_unquote(),去掉字符串首尾的特殊字符，包括：,b,f,n,r,t,uXXXX6四、其他

2、操作71.JSON_ARRAY_APPEND()数组类的追加71.1JSON_ARRAY_APPEND数组类的追加, 不是标准数组类型的， 在追加后会自动转为数组格式，并把之前的值作为第一个元素；71.2在字段中第二个数值的数组中追加一个元素171.3在字段中第三个数值的数组中追加一个元素1,如果不是数组形式的单个值会改变其为数组并追加：71.4在字段中第三个数值的数组中追加一个数组,如果不是数组形式的单个值会改变其为数组并追加：71.5在字段中第一个数值的数组中追加一个元素3,如果不是数组形式的单个值会改变其为数组并追加：81.6在字段中第二个数值的第一个数组中追加一个元素2，如果不是数组形

3、式的单个值会改变其为数组并追加：81.7在字段中key为”b”的value数组中追加一个元素”x”：81.8在字段中key为”c”的value数组中追加一个元素”y”：81.9如果JSON字段为标准的JSON类型，使用这个函数则会把其变化数组形式，并追加值：81.10在字段中追加一个元素181.11在字段中批量追加元素：2，391.12在字段的key为key1的节点中追加一个元素：1,如果不是数组形式的单个值会改变其为数组并追加：91.13如果JSON字段为标准的JSON类型并且其中某节点是数组格式，并追加值：91.14在字段的key为key2中追加一个元素：191.15在字段中key2的第一

4、个数组中追加一个元素2，如果不是数组形式的单个值会改变其为数组并追加：92.JSON_ARRAY_INSERT()数组类的插入92.1在数组第1个值前面插入字符y 元素:102.2在数组第100个值的后面插入字符z元素；如果超出数组长度，则在最后一个值后面插入；102.3在数组第1个值中key为b的value数组第1个值前面入字符元素x：102.4在数组第1个值中key为b的value数组第1个值后面入字符元素x：102.5在数组第3个值后面数组第1个值后面入字符元素y：102.6数组元素的批量插入：在数组第一个值后面添加元素x,在数组第3个值后面数组的第一个值后面添加元素y,在数组第3个值后

5、面数组的第一个值后面添加元素z,在数组第5个值后面添加元素HH112.7 JSON_INSERT标准JSON值的插入112.7.1如果这个key已经存在，则不会去覆盖旧值，不存在的插入113 JSON_MERGE合并操作113.1 JSON_MERGE这个主要用于合并：如果两个是数组的，则合并为一个数组；如果两个是JSON的，则合并为一个JSON，如果两个JSON有相同的key，则value合并为数组；如果是两个单值的，则合并为一个数组；如果是一个数组，一个是JSON，则合并为一个数组：113.2当r,s都是数组时，将r 和 s 两个数组合并后，组成的新数组包含两个数组中所有信息，重复数据不合

6、并。123.3当r,s都是JSON时，将r 和 s 两个JSON合并后，组成的新JSON包含两个JSON中所有信息，如何key值相同，则合并为一个数组；124JSON_REMOVE删除操作124.1 JSON_REMOVE删除操作，这个比较简单，删除字段中某个值，数组的或是JSON格式的都可以：先设置一个变量z,现在为数组格式：124.1删除数组z的第二个元素：124.2再设置变量z为JSON格式：134.3删除JSON:z的key1：134.4删除JSON中key4数组的第一个元素：134.5再设置变量z为数组格式：134.6删除数组中第四个JSON元素里的key1:135JSON_REPL

7、ACE操作135.1 JSON_REPLACE替换操作，有则替换，无则忽略。135.2设置变量 aa为JSON格式：135.3替换aa中的key1为aaaaa,替换aa中的key2的第二个元素为33,替换aa中的key3为1,2,3,4,ccc，在aa中没有key4，所以不会添加:146JSON_SET操作146.1 JSON_SET操作为更新和插入操作，已经存在的去更新；不存在则插入146.2设置变量bb为JSON格式：146.3替换bb的key1、key2、key4，增加key5147JSON字段索引以及Generated字段147.1 Generated Column介绍157.2 Ge

8、nerated Column注意事项167.3 利用Generated Column给JSON字段添加索引16一、创建表CREATE TABLE json_test ( id INT(11) NOT NULL AUTO_INCREMENT, user_name VARCHAR(20) DEFAULT NULL, user_info json DEFAULT NULL, PRIMARY KEY (id) ENGINE=INNODB DEFAULT CHARSET=utf8;二、插入数据INSERT INTO json_test(user_name,user_info)VALUES(用户1,age

9、:23,sex:男,name:李一,nat:汉族),(用户2,age:24,sex:男,name:李二,nat:汉族),(用户3,age:25,sex:男,name:李三,nat:汉族),(用户4,age:26,sex:男,name:李四,nat:汉族),(用户5,age:27,sex:男,name:李五,nat:汉族),(用户6,age:28,sex:男,name:李六,nat:汉族),(用户7,age:29,sex:男,name:李七,nat:汉族),(用户8,age:30,sex:男,name:李八,nat:汉族);三、查询所有数据SELECT * FROM json_test;1. 按

10、找user_info中的数据精确查询,数据格式需要与json数据中的存储方式完全一致SELECT * FROM json_test WHERE json_extract(user_info,$.age) = 23;SELECT * FROM json_test WHERE json_extract(user_info,$.name) = 李一;SELECT * FROM json_test WHERE json_extract(user_info,$.name) LIKE %李%; - 模糊查询2. - 等价于JSON_EXTRACTSELECT * FROM json_test WHERE

11、user_info - $.age = 23;SELECT * FROM json_test WHERE user_info - $.name = 李一;SELECT * FROM json_test WHERE user_info - $.name LIKE %李%; - 模糊查询3. JSON_SEARCH(字段名,one or all,条件)，没找到one 和 all 的具体区别SELECT * FROM json_test WHERE JSON_SEARCH(user_info,one,李一) IS NOT NULL;SELECT * FROM json_test WHERE JSON

12、_SEARCH(user_info,all,李一) IS NOT NULL;SELECT * FROM json_test WHERE JSON_SEARCH(user_info,all,%二) IS NOT NULL;SELECT * FROM json_test WHERE JSON_SEARCH(user_info,all,%李%) IS NOT NULL;4.JSON_CONTAINS判断key是否包含指定的值,JSON_CONTAINS(字段名,条件,key);0为不包含，1为包含；SELECT id,user_name,user_info,JSON_CONTAINS(user_in

13、fo,李二,$.name) AS path1 FROM json_test - where JSON_CONTAINS(user_info,李二,$.name);5. JSON_LENGTH(字段名),查询字段的json节点数SELECT id,user_name,JSON_LENGTH(user_info) AS length FROM json_test;6.json_unquote(),去掉字符串首尾的特殊字符，包括：,b,f,n,r,t,uXXXXSELECT *,json_unquote(json_extract(user_info,$.name) AS unquote FROM j

14、son_test;四、其他操作1.JSON_ARRAY_APPEND()数组类的追加1.1JSON_ARRAY_APPEND数组类的追加, 不是标准数组类型的， 在追加后会自动转为数组格式，并把之前的值作为第一个元素； SET j = a, b, c, d; SET k = a,b,c,d,e; 1.2在字段中第二个数值的数组中追加一个元素1SELECT JSON_ARRAY_APPEND(j, $1, 1); - 执行结果：a, b, c, 1, d1.3在字段中第三个数值的数组中追加一个元素1,如果不是数组形式的单个值会改变其为数组并追加：SELECT JSON_ARRAY_APPEND(

15、j, $2, 1); - 执行结果：a, b, c, d, 1SELECT JSON_ARRAY_APPEND(k, $2, 1); - 执行结果：a, b, c, d, e, 11.4在字段中第三个数值的数组中追加一个数组,如果不是数组形式的单个值会改变其为数组并追加：SELECT JSON_UNQUOTE(JSON_ARRAY_APPEND(j,$2,k); - 执行结果：a, b, c, d, a,b,c,d,e1.5在字段中第一个数值的数组中追加一个元素3,如果不是数组形式的单个值会改变其为数组并追加：SELECT JSON_ARRAY_APPEND(j,$0,3); - 执行结果：a

16、, 3, b, c, d1.6在字段中第二个数值的第一个数组中追加一个元素2，如果不是数组形式的单个值会改变其为数组并追加：SELECT JSON_ARRAY_APPEND(j,$10,2); - 执行结果：a, b, 2, c, d1.7在字段中key为”b”的value数组中追加一个元素”x”： SET l = a: 1, b: 2, 3, c: 4;SELECT JSON_ARRAY_APPEND(l,$.b,x); - 执行结果：a: 1, b: 2, 3, x, c: 41.8在字段中key为”c”的value数组中追加一个元素”y”：SELECTJSON_ARRAY_APPEND(

17、l,$.c,Y);- 执行结果：a: 1, b: 2, 3, c: 4, Y1.9如果JSON字段为标准的JSON类型，使用这个函数则会把其变化数组形式，并追加值：SET m = key1:a,key2:b,key3:c;1.10在字段中追加一个元素1SELECT JSON_ARRAY_APPEND(m,$,1);- 执行结果：key1: a, key2: b, key3: c, 11.11在字段中批量追加元素：2，3SELECT JSON_ARRAY_APPEND(m,$,2,$,3);- 执行结果：key1: a, key2: b, key3: c, 2, 31.12在字段的key为key

18、1的节点中追加一个元素：1,如果不是数组形式的单个值会改变其为数组并追加：SELECT JSON_ARRAY_APPEND(m,$.key1,1);- 执行结果：key1: a, 1, key2: b, key3: c1.13如果JSON字段为标准的JSON类型并且其中某节点是数组格式，并追加值：SET n = key1:a,key2:b,c,d,key3:e;1.14在字段的key为key2中追加一个元素：1SELECT JSON_ARRAY_APPEND(n,$.key2,1);- 执行结果：key1: a, key2: b, c, d, 1, key3: e1.15在字段中key2的第一

19、个数组中追加一个元素2，如果不是数组形式的单个值会改变其为数组并追加：SELECT JSON_ARRAY_APPEND(n,$.key20,1);- 执行结果：key1: a, key2: b, 1, c, d, key3: e2.JSON_ARRAY_INSERT()数组类的插入SET p = a,b:1,2,c,d,3,4,e,f;2.1在数组第1个值前面插入字符y 元素:SELECT JSON_ARRAY_INSERT(p,$0,y);- 执行结果：y, a, b: 1, 2, c, d, 3, 4, e, f2.2在数组第100个值的后面插入字符z元素；如果超出数组长度，则在最后一个值

20、后面插入；SELECT JSON_ARRAY_INSERT(p,$100,z);- 执行结果：a, b: 1, 2, c, d, 3, 4, e, f, z2.3在数组第1个值中key为b的value数组第1个值前面入字符元素x：SELECT JSON_ARRAY_INSERT(p,$1.b0,x);- 执行结果：a, b: x, 1, 2, c, d, 3, 4, e, f2.4在数组第1个值中key为b的value数组第1个值后面入字符元素x：SELECT JSON_ARRAY_INSERT(p,$1.b1,x);- 执行结果：a, b: 1, x, 2, c, d, 3, 4, e, f

21、2.5在数组第3个值后面数组第1个值后面入字符元素y：SELECT JSON_ARRAY_INSERT(p,$31,y);- 执行结果：a, b: 1, 2, c, d, 3, y, 4, e, f2.6数组元素的批量插入：在数组第一个值后面添加元素x,在数组第3个值后面数组的第一个值后面添加元素y,在数组第3个值后面数组的第一个值后面添加元素z,在数组第5个值后面添加元素HHSELECT JSON_ARRAY_INSERT(p,$1.b1,x,$31,y,$31,z,$5,HH);- 执行结果：a, b: 1, x, 2, c, d, 3, z, y, 4, e, f, HH2.7 JSON

22、_INSERT标准JSON值的插入SET q = a: 1, b: 2, 3;2.7.1如果这个key已经存在，则不会去覆盖旧值，不存在的插入SELECT JSON_INSERT(q, $.a, 10, $.c, true, false);- 执行结果：a: 1, b: 2, 3, c: true, false3 JSON_MERGE合并操作3.1 JSON_MERGE这个主要用于合并：如果两个是数组的，则合并为一个数组；如果两个是JSON的，则合并为一个JSON，如果两个JSON有相同的key，则value合并为数组；如果是两个单值的，则合并为一个数组；如果是一个数组，一个是JSON，则合并

23、为一个数组：SET r = a,b,c,d,1;SET s = 1,11,21,31;3.2当r,s都是数组时，将r 和 s 两个数组合并后，组成的新数组包含两个数组中所有信息，重复数据不合并。SELECT JSON_MERGE(r,s);- 执行结果：a, b, c, d, 1, 1, 11, 21, 31SET t = key1:a,key2:b,key3:c,key4:d;SET u = key1:1,key2:2,key3:3,key:4;3.3当r,s都是JSON时，将r 和 s 两个JSON合并后，组成的新JSON包含两个JSON中所有信息，如何key值相同，则合并为一个数组；SE

24、LECT JSON_MERGE(t,u);- 执行结果：key: 4, key1: a, 1, key2: b, 2, key3: c, 3, key4: d4JSON_REMOVE删除操作4.1 JSON_REMOVE删除操作，这个比较简单，删除字段中某个值，数组的或是JSON格式的都可以：先设置一个变量z,现在为数组格式：SET z = 1,2,3,4,5;4.1删除数组z的第二个元素：SELECT JSON_REMOVE(z,$1);- 执行结果：1, 3, 4, 54.2再设置变量z为JSON格式：SET z = key1:1,key2:2,key3:3,key4:1,2,3,e;4.

25、3删除JSON:z的key1：SELECT JSON_REMOVE(z,$.key1);- 执行结果：key2: 2, key3: 3, key4: 1, 2, 3, e4.4删除JSON中key4数组的第一个元素：SELECT JSON_REMOVE(z,$.key40);- 执行结果：key1: 1, key2: 2, key3: 3, key4: 2, 3, e4.5再设置变量z为数组格式：SET z = a,b,c,key1:d,key2:e,key3:f;4.6删除数组中第四个JSON元素里的key1:SELECT JSON_REMOVE(z,$3.key1);- 执行结果：a, b

26、, c, key2: e, key3: f5JSON_REPLACE操作5.1 JSON_REPLACE替换操作，有则替换，无则忽略。5.2设置变量 aa为JSON格式：SET aa = key1:a,key2:2,3,key3:c;5.3替换aa中的key1为aaaaa,替换aa中的key2的第二个元素为33,替换aa中的key3为1,2,3,4,ccc，在aa中没有key4，所以不会添加:SELECT JSON_REPLACE(aa,$.key1,aaaaa,$.key21,33,$.key3,1,2,3,4,ccc,$.key4,333);- 执行结果：key1: aaaaa, key2

27、: b, key3: 1,2,3,4,ccc6JSON_SET操作6.1 JSON_SET操作为更新和插入操作，已经存在的去更新；不存在则插入6.2设置变量bb为JSON格式：SET bb = key1:a,key2:b,key3:c,key4:d,e,f,1,2,3;6.3替换bb的key1、key2、key4，增加key5SELECT JSON_SET(bb,$.key1,aaa,$.key2,bbbb,b,$.key40,ddd,$.key5,e);- 执行结果：key1: aaa, key2: bbbb,b, key3: c, key4: ddd, key5: e7JSON字段索引以及

28、Generated字段JSON字段类型在当前的版本中自身没有索引，那么在生产中是非常可怕的，JSON字段的增、删、改、查效率可想而知，基本没法用，也许是基于此，MySQL5.7中提供了Generated字段类型，网上有叫生成列或是计算列的。这里先来了解一下什么是Generated Column。7.1 Generated Column介绍Generated Column是MySQL 5.7.6版本开始引入的新特性，所谓Cenerated Column，就是数据库中这一列由其他列计算而得。引用官方参考手册中的例子予以说明：CREATE TABLE triangle ( sidea DOUBLE,

29、 sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb);INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);SELECT * FROM triangle;+-+-+-+| sidea| sideb | sidec|+-+-+-+| 1 | 1 | 1.4142135623730951 | 3 | 4 | 5 | 6 | 8 | 10 |Generated Column有两种：Virtual Generated Column和Stored Gener

30、ated Column。前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得到的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。虽然一般情况下都应该使用Virtal Generated Column，但是，目前使用Virtual Generated Column还有很多限制：不能用作

31、主键、不能创建全文索引和空间索引等，所以如果使用Generated Column字段做索引的话，需要使用Stored Generated Column，在使用Generated Column做索引上，JSON字段索引的解决方案，官方也是推荐使用Stored Generated Column。使用Stored Generated Column建表语句如下，只是在加了个单词：CREATE TABLE triangle ( sidea DOUBLE, sideb DOUBLE, sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb) STORED);

32、7.2 Generated Column注意事项Generated Column是不能进行写操作的，它是自动生成的；在创建的时候要考虑这个列的计算公式是否合理，不合理的话创建的时候不会报错，使用时插入值就会报错；Generated Column依赖的列在删除的时候会提示报错，必须先删除Generated Column才能再去删除它依赖的列；Generated Column定义不合法，如我们将generated column定义为 x列+y列，很明显，x列或y列都是数值型，如果我们将x列或y列定义(或修改)为字符型，则预期会报错，但实际上我们可以正常创建，但是在插入的时候是会报错的。7.3 利用Generated Column给JSON字段添加索引正常情况下，JSON字段的相关查询是扫描全表的，因为JSON字段本身不能创建索引的，我们利用Generated Column特性，对JSON字段中相关key作为Generated Column来做生成列，然后对Generated Column做索引：ALTER TABLE json_test ADD COLUMN age INT AS (JSON_EXTRACT(user_info,$.age) STORED, ADD KEY idx_age (age);