mysql json字段
从 mysql 5.7.8 开始,mysql 支持原生的 json 数据类型。
mysql> create table lnmp (
`id` int(10) unsigned not null auto_increment,
`category` json,
`tags` json,
primary key (`id`)
);
mysql> desc lnmp; +----------+------------------+------+-----+---------+----------------+ | field | type | null | key | default | extra | +----------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | no | pri | null | auto_increment | | category | json | yes | | null | | | tags | json | yes | | null | | +----------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)
insert into `lnmp` (category, tags) values ('{"id": 1, "name": "lnmp.cn"}', '[1, 2, 3]'); insert into `lnmp` (category, tags) values (json_object("id", 2, "name", "php.net"),json_array(1, 3, 5));
查看插入的数据
mysql> select * from lnmp; +----+------------------------------+-----------+ | id | category | tags | +----+------------------------------+-----------+ | 1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] | | 2 | {"id": 2, "name": "php.net"} | [1, 3, 5] | +----+------------------------------+-----------+ 2 rows in set (0.00 sec)
三.查询数据
查询 json 中的数据用 column->path 的形式,其中对象类型 path 这样表示 $.path, 而数组类型则是 $[index]
mysql> select id, category->'$.id', category->'$.name', tags->'$[0]', tags->'$[2]' from lnmp; +----+------------------+--------------------+--------------+--------------+ | id | category->'$.id' | category->'$.name' | tags->'$[0]' | tags->'$[2]' | +----+------------------+--------------------+--------------+--------------+ | 1 | 1 | "lnmp.cn" | 1 | 3 | | 2 | 2 | "php.net" | 1 | 5 | +----+------------------+--------------------+--------------+--------------+ 2 rows in set (0.00 sec)
可以看到对应字符串类型的 category->'$.name' 中还包含着双引号,这其实并不是想要的结果,可以用 json_unquote 函数将双引号去掉,从 mysql 5.7.13 起也可以通过这个操作符 ->> 这个和 json_unquote 是等价的
mysql> select id, category->'$.name', json_unquote(category->'$.name'), category->>'$.name' from lnmp; +----+--------------------+----------------------------------+---------------------+ | id | category->'$.name' | json_unquote(category->'$.name') | category->>'$.name' | +----+--------------------+----------------------------------+---------------------+ | 1 | "lnmp.cn" | lnmp.cn | lnmp.cn | | 2 | "php.net" | php.net | php.net | +----+--------------------+----------------------------------+---------------------+ 2 rows in set (0.00 sec)
下面说下 json 作为条件进行搜索。
虽然插入的时候可以用字符串的形式插入,但是实际上json字段的存储方式并不等同于字符串,查询的时候直接用字符串相等是不可以的.
可以通过cast方法将字符串转为json
mysql> select * from lnmp where category = cast('{"id": 1, "name": "lnmp.cn"}' as json); +----+------------------------------+-----------+ | id | category | tags | +----+------------------------------+-----------+ | 1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] | +----+------------------------------+-----------+ 1 row in set (0.00 sec)
通过 json 中的元素进行查询, 对象型的查询同样可以通过 column->path
mysql> select * from lnmp where category->'$.name' = 'lnmp.cn'; +----+------------------------------+-----------+ | id | category | tags | +----+------------------------------+-----------+ | 1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] | +----+------------------------------+-----------+ 1 row in set (0.00 sec)
要特别注意的是,json 中的元素搜索是严格区分变量类型的,比如说整型和字符串是严格区分的
除了用 column->path 的形式搜索,还可以用json_contains 函数,但和 column->path 的形式有点相反的是,json_contains 第二个参数是不接受整数的,无论 json 元素是整型还是字符串,否则会出现这个错误
mysql> select * from lnmp where json_contains(category, '1', '$.id'); +----+------------------------------+-----------+ | id | category | tags | +----+------------------------------+-----------+ | 1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] | +----+------------------------------+-----------+ 1 row in set (0.01 sec)
json_contains通用于字典查询和列表查询
字典:json_contains(column_name, value, key)
列表:json_contains(column, value)
四.更新数据
如果是整个 json 更新的话,和插入时类似的。
update lnmp set tags = '[1, 3, 4]' where id = 1;
更新元素:
update lnmp set category = json_insert(category, '$.name', 'lnmp', '$.url', 'www.lnmp.cn') where id = 1; update lnmp set category = json_set(category, '$.host', 'www.lnmp.cn', '$.url', 'http://www.lnmp.cn') where id = 1; update lnmp set category = json_replace(category, '$.name', 'php', '$.url', 'http://www.php.net') where id = 2; update lnmp set category = json_remove(category, '$.url', '$.host') where id = 1;
json_insert: 只会追加新的值,原有的值不会被覆盖;
json_set: 插入新值并可以覆盖原有的值
json_replace: 只替换原有的值,新值忽略
json_remove: 删除元素.
五. python应用
from sqlalchemy import json from sqlalchemy import func self.db().query(traincourse).filter(func.json_contains(traincourse.teacher_id, "21")).all() self.db().query(trainclass).filter(func.json_contains(trainclass.flex_data, "123", "$.holddept")).all() # 存疑,有时候会报无法理解的错误
与sql原生语句用法类似,可以传三个参数或者两个参数,其中value值必须为字符串.
上一篇: 我准备离婚了
下一篇: 三分天注定,七分靠打拼
推荐阅读
-
Python将txt文件输入到MySQL数据库中
-
centos7.2.1511安装jdk1.8.0_151及mysql5.6.38的方法
-
linux中mysql备份shell脚本代码
-
mysql 开发基础系列17 存储过程和函数(上)
-
mysql 的一些事
-
Linux登录MySQL时出现 Can't connect to local MySQL server through socket '/tmp/mysql.sock'解决方法
-
CentOS随笔 - 4.CentOS7安装MySql 5.5.60(下载 tar 方式安装)
-
高性能MySQL--innodb中事务的隔离级别与锁的关系
-
MYSQL基础语法的使用
-
docker中mysql初始化及启动失败问题解决方案