Mysql5.7使用JSON数据标准示范.doc
.目录一、创建表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四、其他操作71.JSON_ARRAY_APPEND()数组类的追加71.1JSON_ARRAY_APPEND数组类的追加, 不是标准数组类型的， 在追加后会自动转为数组格式，并把之前的值作为第一个元素；71.2在字段中第二个数值的数组中追加一个元素171.3在字段中第三个数值的数组中追加一个元素1,如果不是数组形式的单个值会改变其为数组并追加：71.4在字段中第三个数值的数组中追加一个数组,如果不是数组形式的单个值会改变其为数组并追加：71.5在字段中第一个数值的数组中追加一个元素3,如果不是数组形式的单个值会改变其为数组并追加：81.6在字段中第二个数值的第一个数组中追加一个元素2，如果不是数组形式的单个值会改变其为数组并追加：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的第一个数组中追加一个元素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个值后面数组的第一个值后面添加元素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 两个数组合并后，组成的新数组包含两个数组中所有信息，重复数据不合并。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_REPLACE操作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 Generated 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":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. 按找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 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_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_info,"李二",$.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 json_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, "d"1.3在字段中第三个数值的数组中追加一个元素1,如果不是数组形式的单个值会改变其为数组并追加：SELECT JSON_ARRAY_APPEND(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","e""1.5在字段中第一个数值的数组中追加一个元素3,如果不是数组形式的单个值会改变其为数组并追加：SELECT JSON_ARRAY_APPEND(j,$0,3); - 执行结果："a", 3, "b", "c", "d"1.6在字段中第二个数值的第一个数组中追加一个元素2，如果不是数组形式的单个值会改变其为数组并追加：SELECT JSON_ARRAY_APPEND(j,$10,2); - 执行结果："a", "b", 2, "c", "d"1.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(l,$.c,"Y");- 执行结果："a": 1, "b": 2, 3, "c": 4, "Y"1.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为key1的节点中追加一个元素：1,如果不是数组形式的单个值会改变其为数组并追加：SELECT JSON_ARRAY_APPEND(m,$.key1,1);- 执行结果："key1": "a", 1, "key2": "b", "key3": "c"1.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": "e"1.15在字段中key2的第一个数组中追加一个元素2，如果不是数组形式的单个值会改变其为数组并追加：SELECT JSON_ARRAY_APPEND(n,$.key20,1);- 执行结果："key1": "a", "key2": "b", 1, "c", "d", "key3": "e"2.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", "f"2.2在数组第100个值的后面插入字符z元素；如果超出数组长度，则在最后一个值后面插入；SELECT JSON_ARRAY_INSERT(p,$100,z);- 执行结果："a", "b": 1, 2, "c", "d", 3, 4, "e", "f", "z"2.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", "f"2.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"2.5在数组第3个值后面数组第1个值后面入字符元素y：SELECT JSON_ARRAY_INSERT(p,$31,y);- 执行结果："a", "b": 1, 2, "c", "d", 3, "y", 4, "e", "f"2.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", "HH"2.7 JSON_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, false"3 JSON_MERGE合并操作3.1 JSON_MERGE这个主要用于合并：如果两个是数组的，则合并为一个数组；如果两个是JSON的，则合并为一个JSON，如果两个JSON有相同的key，则value合并为数组；如果是两个单值的，则合并为一个数组；如果是一个数组，一个是JSON，则合并为一个数组：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值相同，则合并为一个数组；SELECT JSON_MERGE(t,u);- 执行结果："key": "4", "key1": "a", "1", "key2": "b", "2", "key3": "c", "3", "key4": "d"4JSON_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.3删除JSON:z的key1：SELECT JSON_REMOVE(z,$.key1);- 执行结果："key2": 2, "key3": 3, "key4": 1, 2, 3, "e"4.4删除JSON中key4数组的第一个元素：SELECT JSON_REMOVE(z,$.key40);- 执行结果："key1": 1, "key2": 2, "key3": 3, "key4": 2, 3, "e"4.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", "c", "key2": "e", "key3": "f"5JSON_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": "b", "key3": "1,2,3,4,ccc"6JSON_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": "e"7JSON字段索引以及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, 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 Generated Column。前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得到的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。虽然一般情况下都应该使用Virtal Generated Column，但是，目前使用Virtual Generated Column还有很多限制：不能用作主键、不能创建全文索引和空间索引等，所以如果使用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);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);
