MySQL的JSON数据类型
mysql视频教程栏目介绍JSON数据类型。
MySQL 5.7 添加了 JSON 数据类型的支撑，在以前要是要存储 JSON 类型的数据的话我们只能本人做 JSON.stringify() 和 JSON.parse() 的操纵，并且没方法针对 JSON 内的数据进行查询操纵，所有的操纵必需读掏出来 parse 之后进行，非常的费事。原生的 JSON 数据类型支撑之后，我们就可以直接对 JSON 进行数据查询和修改等操纵了，较以前会利便非常多。
为了利便演示我先新建一个 user 表，其中 info 字段用来存储会员的根基信息。要将字段定义成 JSON 类型数据非常简略，直接字段名后接 JSON 即可。
CREATE TABLE user ( id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, info JSON );
表新建成功之后我们就按照经典的 CRUD 数据操纵来讲讲怎么进行 JSON 数据类型的操纵。
增加数据
增加数据这块是比拼简略，不外需要了解 MySQL 对 JSON 的存储本质上还是字符串的存储操纵。只是当定义为 JSON 类型之后内部会对数据再进行一些索引的新建利便后续的操纵罢了。所以增加 JSON 数据的时候需要运用字符串包装。
mysql> INSERT INTO user (`name`, `info`) VALUES('lilei', '{"sex": "male", "age": 18, "hobby": ["basketball", "football"], "score": [85, 90, 100]}');
Query OK, 1 row affected (0.00 sec) 除了本人拼 JSON 以外，你还可以调取 MySQL 的 JSON 新建函数进行新建。
JSON_OBJECT：迅速新建 JSON 对象，奇数列为 key，偶数列为 value，运用办法JSON_OBJECT(key,value,key1,value1)JSON_ARRAY：迅速新建 JSON 数组，运用办法JSON_ARRAY(item0, item1, item2)
mysql> INSERT INTO user (`name`, `info`) VALUES('hanmeimei', JSON_OBJECT(
-> 'sex', 'female',
-> 'age', 18,
-> 'hobby', JSON_ARRAY('badminton', 'sing'),
-> 'score', JSON_ARRAY(90, 95, 100)
-> ));
Query OK, 1 row affected (0.00 sec) 不外关于 JavaScript 工程师来说无论是运用字符串来写还是运用自带函数来新建 JSON 都是非常费事的一件事，远没有 JS 原生对象来的好用。所以在 think-model 模块中我们添加了 JSON 数据类型的数据主动进行 JSON.stringify() 的支撑，所以直接传入 JS 对象数据即可。
因为数据的主动序列化和解析是依据字段类型来做的，为了避免影响已运转的项目，需要在模块中配置 jsonFormat: true 才干开启这项功能。
//adapter.jsconst MySQL = require('think-model-mysql');exports.model = { type: 'mysql', mysql: { handle: MySQL,
... jsonFormat: true
}
}; //user.jsmodule.exports = class extends think.Controller { async indexAction() { const userId = await this.model('user').add({ name: 'lilei', info: { sex: 'male', age: 16, hobby: ['basketball', 'football'], score: [85, 90, 100]
}
}); return this.success(userId);
}
} 下面让我们来看看终究存储到数据库中的数据有哪些样的
mysql> SELECT * FROM `user`;
+----+-----------+-----------------------------------------------------------------------------------------+
| id | name | info |
+----+-----------+-----------------------------------------------------------------------------------------+
| 1 | lilei | {"age": 18, "sex": "male", "hobby": ["basketball", "football"], "score": [85, 90, 100]} |
| 2 | hanmeimei | {"age": 18, "sex": "female", "hobby": ["badminton", "sing"], "score": [90, 95, 100]} |
+----+-----------+-----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec) 查询数据
为了更好的支撑 JSON 数据的操纵，MySQL 供给了一些 JSON 数据操纵类的办法。和查询操纵相干的办法主要如下：
JSON_EXTRACT()：依据 Path 猎取局部 JSON 数据，运用办法JSON_EXTRACT(json_doc, path[, path] ...)->：JSON_EXTRACT()的等价写法->>：JSON_EXTRACT()和JSON_UNQUOTE()的等价写法JSON_CONTAINS()：查询 JSON 数据可否在指定 Path 包括指定的数据，包括则返回1，不然返回0。运用办法JSON_CONTAINS(json_doc, val[, path])JSON_CONTAINS_PATH()：查询可否存在指定途径，存在则返回1，不然返回0。one_or_all只能取值 "one" 或 "all"，one 表示只有有一个存在即可，all 表示所有的都存在才行。运用办法JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)JSON_KEYS()：猎取 JSON 数据在指定途径下的所有键值。运用办法JSON_KEYS(json_doc[, path])，相似 JavaScript 中的Object.keys()办法。JSON_SEARCH()：查询包括指定字符串的 Paths，并作为一个 JSON Array 返回。查询的字符串可以用 LIKE 里的 '%' 或 '_' 匹配。运用办法JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])，相似 JavaScript 中的findIndex()操纵。
我们在这里不合错误每个办法进行逐个的举例描述，仅提出一些场景举例应当怎么操纵。
返回会员的年龄和性别
举这个例子就是想告诉下大家怎么猎取 JSON 数据中的局部内容，并按照正常的表字段进行返回。这块可以运用 JSON_EXTRACT 或者等价的 -> 操纵都可以。其中依据例子可以看到 sex 返回的数据都带有引号，这个时候可以运用 JSON_UNQUOTE() 或者直接运用 ->> 就可以把引号去除了。
mysql> SELECT `name`, JSON_EXTRACT(`info`, '$.age') as `age`, `info`->'$.sex' as sex FROM `user`; +-----------+------+----------+ | name | age | sex | +-----------+------+----------+ | lilei | 18 | "male" | | hanmeimei | 16 | "female" | +-----------+------+----------+ 2 rows in set (0.00 sec)
这里我们首先次接触到了 Path 的写法，MySQL 通过这种字符串的 Path 描述帮忙我们映照到对应的数据。和 JavaScript 中对象的操纵比拼相似，通过 . 猎取下1级的属性，通过 [] 猎取数组元素。
不同的地方在于需要通过 $ 表示自身，这个也比拼好了解。别的就是可以运用 * 和 ** 两个通配符，比方 .* 表示目前层级的所有成员的值，[*] 则表示目前数组中所有成员值。** 相似 LIKE 同样可以接前缀和后缀，比方 a**b 表示的是以 a 开头，b结尾的途径。
途径的写法非常简略，背面的内容里也会涌现。上面的这个查询对应在 think-model 的写法为
//user.jsmodule.exports = class extends think.Controller { async indexAction() { const userModel = this.model('user'); const field = "name, JSON_EXTRACT(info, '$.age') AS age, info->'$.sex' as sex"; const users = await userModel.field(field).where('1=1').select(); return this.success(users);
}
} 返回喜好篮球的男性会员
mysql> SELECT `name` FROM `user` WHERE JSON_CONTAINS(`info`, '"male"', '$.sex') AND JSON_SEARCH(`info`, 'one', 'basketball', null, '$.hobby'); +-------+ | name | +-------+ | lilei | +-------+ 1 row in set, 1 warning (0.00 sec)
这个例子就是简略的告诉大家怎么对属性和数组进行查询搜寻。其中需要注意的是 JSON_CONTAINS() 查询字符串因为不带类型转换的题目字符串需要运用加上 "" 包裹查询，或者运用 JSON_QUOTE('male') 也可以。
要是你运用的是 MySQL 8 的话，也可以运用新增的 JSON_VALUE() 来取代 JSON_CONTAINS()，新办法的益处是会带类型转换，以免方才双引号的尴尬题目。不需要返回的途径的话，JSON_SEARCH() 在这里也可以运用新增的 MEMBER OF 或者 JSON_OVERLAPS() 办法替代。
mysql> SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, '$.sex') = 'male' AND 'basketball' MEMBER OF(JSON_VALUE(`info`, '$.hobby'));
+-------+
| name |
+-------+
| lilei |
+-------+
1 row in set (0.00 sec)
mysql> SELECT `name` FROM `user` WHERE JSON_VALUE(`info`, '$.sex') = 'male' AND JSON_OVERLAPS(JSON_VALUE(`info`, '$.hobby'), JSON_QUOTE('basketball'));
+-------+
| name |
+-------+
| lilei |
+-------+
1 row in set (0.00 sec) 上面的这个查询对应在 think-model 的写法为
//user.jsmodule.exports = class extends think.Controller { async indexAction() { const userModel = this.model('user'); const where = { _string: [ "JSON_CONTAINS(info, '\"male\"', '$.sex')", "JSON_SEARCH(info, 'one', 'basketball', null, '$.hobby')"
]
}; const where1 = { _string: [ "JSON_VALUE(`info`, '$.sex') = 'male'", "'basketball' MEMBER OF (JSON_VALUE(`info`, '$.hobby'))"
]
}; const where2 = { _string: [ "JSON_VALUE(`info`, '$.sex') = 'male'", "JSON_OVERLAPS(JSON_VALUE(`info`, '$.hobby'), JSON_QUOTE('basketball'))"
]
} const users = await userModel.field('name').where(where).select(); return this.success(users);
}
} 修改数据
MySQL 供给的 JSON 操纵函数中，和修改操纵相干的办法主要如下：
JSON_APPEND/JSON_ARRAY_APPEND：这两个名字是统一个功能的两种叫法，MySQL 5.7 的时候为JSON_APPEND，MySQL 8 更新为JSON_ARRAY_APPEND，而且以前的名字被废弃。该办法犹如字面意思，给数组增加值。运用办法JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)JSON_ARRAY_INSERT：给数组增加值，区别于JSON_ARRAY_APPEND()它可以在指定位置插值。运用办法JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)JSON_INSERT/JSON_REPLACE/JSON_SET：以上三个办法都是对 JSON 插入数据的，他们的运用办法都为JSON_[INSERT|REPLACE|SET](json_doc, path, val[, path, val] ...)，不外在插入准则上存在一些差别。JSON_INSERT：当途径不存在才插入JSON_REPLACE：当途径存在才替代JSON_SET：无论途径可否存在
JSON_REMOVE：移除指定途径的数据。运用办法JSON_REMOVE(json_doc, path[, path] ...)
因为 JSON_INSERT, JSON_REPLACE, JSON_SET 和 JSON_REMOVE 几个办法支撑属性和数组的操纵，所之前两个 JSON_ARRAY 办法用的会略微少一点。下面我们依据以前的数据继续举几个实例看看。
修改会员的年龄
mysql> UPDATE `user` SET `info` = JSON_REPLACE(`info`, '$.age', 20) WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT JSON_VALUE(`info`, '$.age') as age FROM `user` WHERE `name` = 'lilei'; +------+ | age | +------+ | 20 | +------+ 1 row in set (0.00 sec)
JSON_INSERT 和 JSON_SET 的例子也是相似，这里就未几做演示了。对应到 think-model 中的话，需要运用 EXP 前提表达式处置，对应的写法为
//user.jsmodule.exports = class extends think.Controller { async indexAction() { const userModel = this.model('user'); await userModel.where({name: 'lilei'}).update({ info: ['exp', "JSON_REPLACE(info, '$.age', 20)"]
}); return this.success();
}
} 修改会员的喜爱
mysql> UPDATE `user` SET `info` = JSON_ARRAY_APPEND(`info`, '$.hobby', 'badminton') WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT JSON_VALUE(`info`, '$.hobby') as hobby FROM `user` WHERE `name` = 'lilei'; +-----------------------------------------+ | hobby | +-----------------------------------------+ | ["basketball", "football", "badminton"] | +-----------------------------------------+ 1 row in set (0.00 sec)
JSON_ARRAY_APPEND 在对数组进行操纵的时候还是要比 JSON_INSERT 之类的利便的，起码你不需要晓得数组的长度。对应到 think-model 的写法为
//user.jsmodule.exports = class extends think.Controller { async indexAction() { const userModel = this.model('user'); await userModel.where({name: 'lilei'}).update({ info: ['exp', "JSON_ARRAY_APPEND(info, '$.hobby', 'badminton')"]
}); return this.success();
}
} 删除会员的分数
mysql> UPDATE `user` SET `info` = JSON_REMOVE(`info`, '$.score[0]') WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT `name`, JSON_VALUE(`info`, '$.score') as score FROM `user` WHERE `name` = 'lilei'; +-------+-----------+ | name | score | +-------+-----------+ | lilei | [90, 100] | +-------+-----------+ 1 row in set (0.00 sec)
删除这块和以前修改操纵相似，没有什么太多需要说的。但是对数组进行操纵许多时候我们可能就是想删值，但是却不晓得这个值的 Path 有哪些。这个时候就需要应用以前讲到的 JSON_SEARCH() 办法，它是依据值去查寻途径的。比方说我们要删除 lilei 乐趣中的 badminton 选项可以这么写。
mysql> UPDATE `user` SET `info` = JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, 'one', 'badminton'))) WHERE `name` = 'lilei'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT JSON_VALUE(`info`, '$.hobby') as hobby FROM `user` WHERE `name` = 'lilei'; +----------------------------+ | hobby | +----------------------------+ | ["basketball", "football"] | +----------------------------+ 1 row in set (0.00 sec)
这里需要注意因为 JSON_SEARCH 不会做类型转换，所以匹配出来的途径字符串需要进行 JSON_UNQUOTE() 操纵。别的还有非常重要的一点是 JSON_SEARCH 没法对数值类型数据进行查寻，也不晓得这个是 Bug 还是 Feature。这也是为何我没有运用 score 来进行举例而是换成了 hobby 的缘由。要是数值类型的话当前只能掏出来在代码中处置了。
mysql> SELECT JSON_VALUE(`info`, '$.score') FROM `user` WHERE `name` = 'lilei'; +-------------------------------+ | JSON_VALUE(`info`, '$.score') | +-------------------------------+ | [90, 100] | +-------------------------------+ 1 row in set (0.00 sec) mysql> SELECT JSON_SEARCH(`info`, 'one', 90, null, '$.score') FROM `user` WHERE `name` = 'lilei'; +-------------------------------------------------+ | JSON_SEARCH(`info`, 'one', 90, null, '$.score') | +-------------------------------------------------+ | NULL | +-------------------------------------------------+ 1 row in set (0.00 sec)
以上对应到 think-model 的写法为
//user.jsmodule.exports = class extends think.Controller { async indexAction() { const userModel = this.model('user'); // 删除分数
await userModel.where({name: 'lilei'}).update({ info: ['exp', "JSON_REMOVE(info, '$.score[0]')"]
}); // 删除乐趣
await userModel.where({name: 'lilei'}).update({ info: ['exp', "JSON_REMOVE(`info`, JSON_UNQUOTE(JSON_SEARCH(`info`, 'one', 'badminton')))"]
});
return this.success();
}
} 跋文
因为比来有一个需求，有一堆数据，要记载这堆数据的排序状况，利便依据排序进行导出。个别状况下确定是给每条数据添加一个 order 字段来记载该条数据的排序状况。但是因为有着大量操纵，在这种时候运用单字段去存储会显得特殊费事。在办事端同事的倡议下，我采取了运用 JSON 字段存储数组的状况来解决这个题目。
也由于这样理解了一下 MySQL 对 JSON 的支撑状况，同时将 think-model 做了一些优化，对 JSON 数据类型添加了支撑。因为大局部 JSON 操纵需要通过内置的函数来操纵，这个自身是可以通过 EXP 前提表达式来完成的。所以只需要对 JSON 数据的增加和查询做好优化就可以了。
整体来看，配合供给的 JSON 操纵函数，MySQL 对 JSON 的支撑完成一些日常的需求还是没有题目的。除了作为 WHERE 前提以及查询字段以外，其它的 ORDER, GROUP, JOIN 等操纵也都是支撑 JSON 数据的。
不外对照 MongoDB 这种天生支撑 JSON 的话，在操纵性上还是要费事很多。特殊是在类型转换这块，运用一段工夫后发明非常容易掉坑。什么时候会带引号，什么时候会不带引号，什么时候需要引号，什么时候不需要引号，这些都容易让新手发憷。别的 JSON_SEARCH() 不支撑数字查寻这个也是一个不小的坑了。
相干免费学习举荐：mysql视频教程
以上就是MySQL 的 JSON 数据类型的细致内容，更多请关注 百分百源码网 其它相干文章！
