数据库教程: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)

查看插入的数据

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 json字段，都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)!

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', 'https://www.lnmp.cn') where id = 1;   update lnmp set category = json_replace(category, '$.name', 'php', '$.url', 'https://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值必须为字符串.