当前位置：Gxlcms > 数据库问题 > mysql json字段

mysql json字段

时间：2021-07-01 10:21:17 帮助过：8人阅读

  一. 创建json(不可以设置长度,可以是null,不能用有默认值)

mysql> CREATE TABLE lnmp (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `category` JSON,
    `tags` JSON,
    PRIMARY KEY (`id`)
);

  1. <span style="font-size: 12px;">mysql> DESC lnmp;
  2. +----------+------------------+------+-----+---------+----------------+
  3. | Field    | Type             | Null | Key | Default | Extra          |
  4. +----------+------------------+------+-----+---------+----------------+
  5. | id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  6. | category | json             | YES  |     | NULL    |                |
  7. | tags     | json             | YES  |     | NULL    |                |
  8. +----------+------------------+------+-----+---------+----------------+
  9. 3 rows in set (0.00 sec)</span>
  二. 插入数据 可以支持字典,列表,以及Null,可以直接插入,也可以通过专门的函数JSON_OBJECT, JSON_ARRAY生成json格式的数据. 
  1. <span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `lnmp` (category, tags) <span style="color: #0000ff;">VALUES</span> (<span style="color: #ff0000;">‘</span><span style="color: #ff0000;">{"id": 1, "name": "lnmp.cn"}</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">[1, 2, 3]</span><span style="color: #ff0000;">‘</span><span style="color: #000000;">);
  2. </span><span style="color: #0000ff;">INSERT</span> <span style="color: #0000ff;">INTO</span> `lnmp` (category, tags) <span style="color: #0000ff;">VALUES</span> (JSON_OBJECT("id", <span style="color: #800000; font-weight: bold;">2</span>, "name", "php.net"),JSON_ARRAY(<span style="color: #800000; font-weight: bold;">1</span>, <span style="color: #800000; font-weight: bold;">3</span>, <span style="color: #800000; font-weight: bold;">5</span>));

查看插入的数据

 

  1. <span style="font-size: 12px;">mysql> SELECT * FROM lnmp;
  2. +----+------------------------------+-----------+
  3. | id | category                     | tags      |
  4. +----+------------------------------+-----------+
  5. |  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
  6. |  2 | {"id": 2, "name": "php.net"} | [1, 3, 5] |
  7. +----+------------------------------+-----------+
  8. 2 rows in set (0.00 sec)</span>

 

三.查询数据

查询 json 中的数据用 column->path 的形式，其中对象类型 path 这样表示 $.path, 而数组类型则是 $[index]

 

  1. <span style="font-size: 12px;">mysql> SELECT id, category->‘$.id‘, category->‘$.name‘, tags->‘$[0]‘, tags->‘$[2]‘ FROM lnmp;
  2. +----+------------------+--------------------+--------------+--------------+
  3. | id | category->‘$.id‘ | category->‘$.name‘ | tags->‘$[0]‘ | tags->‘$[2]‘ |
  4. +----+------------------+--------------------+--------------+--------------+
  5. |  1 | 1                | "lnmp.cn"          | 1            | 3            |
  6. |  2 | 2                | "php.net"          | 1            | 5            |
  7. +----+------------------+--------------------+--------------+--------------+
  8. 2 rows in set (0.00 sec)</span>

可以看到对应字符串类型的 category->‘$.name‘ 中还包含着双引号，这其实并不是想要的结果，可以用 JSON_UNQUOTE 函数将双引号去掉，从 MySQL 5.7.13 起也可以通过这个操作符 ->> 这个和 JSON_UNQUOTE 是等价的

 

  1. <span style="font-size: 12px;">mysql> SELECT id, category->‘$.name‘, JSON_UNQUOTE(category->‘$.name‘), category->>‘$.name‘ FROM lnmp;
  2. +----+--------------------+----------------------------------+---------------------+
  3. | id | category->‘$.name‘ | JSON_UNQUOTE(category->‘$.name‘) | category->>‘$.name‘ |
  4. +----+--------------------+----------------------------------+---------------------+
  5. |  1 | "lnmp.cn"          | lnmp.cn                          | lnmp.cn             |
  6. |  2 | "php.net"          | php.net                          | php.net             |
  7. +----+--------------------+----------------------------------+---------------------+
  8. 2 rows in set (0.00 sec)</span>

 

下面说下 JSON 作为条件进行搜索。

虽然插入的时候可以用字符串的形式插入,但是实际上json字段的存储方式并不等同于字符串,查询的时候直接用字符串相等是不可以的.

可以通过cast方法将字符串转为json

  1. <span style="font-size: 12px;">mysql> SELECT * FROM lnmp WHERE category = CAST(‘{"id": 1, "name": "lnmp.cn"}‘ as JSON);
  2. +----+------------------------------+-----------+
  3. | id | category                     | tags      |
  4. +----+------------------------------+-----------+
  5. |  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
  6. +----+------------------------------+-----------+
  7. 1 row in set (0.00 sec)</span>

通过 JSON 中的元素进行查询, 对象型的查询同样可以通过 column->path

  1. <span style="font-size: 12px;">mysql> SELECT * FROM lnmp WHERE category->‘$.name‘ = ‘lnmp.cn‘;
  2. +----+------------------------------+-----------+
  3. | id | category                     | tags      |
  4. +----+------------------------------+-----------+
  5. |  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
  6. +----+------------------------------+-----------+
  7. 1 row in set (0.00 sec)</span>
  1. <em> </em>

要特别注意的是，JSON 中的元素搜索是严格区分变量类型的，比如说整型和字符串是严格区分的

除了用 column->path 的形式搜索，还可以用JSON_CONTAINS 函数，但和 column->path 的形式有点相反的是，JSON_CONTAINS 第二个参数是不接受整数的，无论 json 元素是整型还是字符串，否则会出现这个错误

  1. <span style="font-size: 12px;">mysql> SELECT * FROM lnmp WHERE JSON_CONTAINS(category, ‘1‘, ‘$.id‘);
  2. +----+------------------------------+-----------+
  3. | id | category                     | tags      |
  4. +----+------------------------------+-----------+
  5. |  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
  6. +----+------------------------------+-----------+
  7. 1 row in set (0.01 sec)</span>
  1. <span style="font-size: 14px;">JSON_CONTAINS通用于字典查询和列表查询</span>
  1. <span style="font-size: 14px;">字典:JSON_CONTAINS(column_name, value, key)</span>
  1. <span style="font-size: 14px;">列表：JSON_CONTAINS(column, value)<br><br></span>
  1. <span style="font-size: 18px;" data-wiz-span="data-wiz-span">四.更新数据</span>
  1. 如果是整个 json 更新的话，和插入时类似的。
  1. <span style="color: #0000ff;">UPDATE</span> lnmp <span style="color: #0000ff;">SET</span> tags <span style="color: #808080;">=</span> <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">[1, 3, 4]</span><span style="color: #ff0000;">‘</span> <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span>;
  1. 更新元素:
  1.  <span style="color: #0000ff;">UPDATE</span> lnmp <span style="color: #0000ff;">SET</span> category <span style="color: #808080;">=</span> JSON_INSERT(category, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">$.name</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">lnmp</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">$.url</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">www.lnmp.cn</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
  2.  </span><span style="color: #0000ff;">UPDATE</span> lnmp <span style="color: #0000ff;">SET</span> category <span style="color: #808080;">=</span> JSON_SET(category, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">$.host</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">www.lnmp.cn</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">$.url</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">http://www.lnmp.cn</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span><span style="color: #000000;">;
  3.  </span><span style="color: #0000ff;">UPDATE</span> lnmp <span style="color: #0000ff;">SET</span> category <span style="color: #808080;">=</span> JSON_REPLACE(category, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">$.name</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">php</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">$.url</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">http://www.php.net</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">2</span><span style="color: #000000;">;
  4.  </span><span style="color: #0000ff;">UPDATE</span> lnmp <span style="color: #0000ff;">SET</span> category <span style="color: #808080;">=</span> JSON_REMOVE(category, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">$.url</span><span style="color: #ff0000;">‘</span>, <span style="color: #ff0000;">‘</span><span style="color: #ff0000;">$.host</span><span style="color: #ff0000;">‘</span>) <span style="color: #0000ff;">WHERE</span> id <span style="color: #808080;">=</span> <span style="color: #800000; font-weight: bold;">1</span>;
  1. JSON_INSERT: 只会追加新的值,原有的值不会被覆盖;
  1. JSON_SET: 插入新值并可以覆盖原有的值
  1. JSON_REPLACE: 只替换原有的值,新值忽略
  1. JSON_REMOVE: 删除元素.<br><br>
  1. <span style="font-size: 18px;">五. python应用</span>
  1. <span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> JSON
  2. </span><span style="color: #0000ff;">from</span> sqlalchemy <span style="color: #0000ff;">import</span><span style="color: #000000;"> func
  3. self.db().query(TrainCourse).filter(func.json_contains(TrainCourse.teacher_id, </span><span style="color: #800000;">"</span><span style="color: #800000;">21</span><span style="color: #800000;">"</span><span style="color: #000000;">)).all()
  4. self.db().query(TrainClass).filter(func.json_contains(TrainClass.flex_data, </span><span style="color: #800000;">"</span><span style="color: #800000;">123</span><span style="color: #800000;">"</span>, <span style="color: #800000;">"</span><span style="color: #800000;">$.HoldDept</span><span style="color: #800000;">"</span>)).all()  <span style="color: #008000;">#</span><span style="color: #008000;"> 存疑,有时候会报无法理解的错误</span>
  1. 与sql原生语句用法类似,可以传三个参数或者两个参数,其中value值必须为字符串.
  1. <span style="font-size: 14px;"> </span>
  1. <em id="__mceDel"> </em>

mysql json字段

标签：长度   contains   query   teacher   整数   应用   错误   elf   生成   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！