MySQL5.7 supports a native JSON data type, and how to use it?

mysql5.7已经支持JSON的数据结构,意味着我们可以更加方便的对JSON结构的数据进行增删改成

什么是JSON?

JSON: JavaScript Object Notation(JavaScript 对象表示法)
JSON 是存储和交换文本信息的语法。类似 XML。
JSON 比 XML 更小、更快,更易解析。
JSON 具有自我描述性,更易理解

建立表,添加JSON的数据结构字段col

建表qtest,然后添加col的字段,其数据类型为JSON,插入一条JSON字符串,然后发现添加不是json结构的数据 mysql会给我们报如下错误ERROR 3140 (22032) at line 2: Invalid JSON text:,表明我们插入的是非法JSON。

{"mascot": "Our mascot is a dolphin named \"Sakila\"."}
mysql> CREATE TABLE qtest (col JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"mascot": "Our mascot is a dolphin named \"Sakila\"."}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.

如何查询JSON的某个字段

  1. 字段指向一个字符串,由$.name组成,美元符号+字段名称
mysql> SELECT col->"$.mascot" FROM qtest;
+---------------------------------------------+
| col->"$.mascot"                             |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)
  1. 查询数组$[0],美元符号+中括号及数组索引
mysql> select *from sourcedev;
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "sourcedev.cc"} | [1, 2, 3] |
+----+------------------------------+-----------+
1 row in set (0.00 sec)

mysql> select id, category->'$.id', tags->'$[0]' from sourcedev;
+----+------------------+--------------+
| id | category->'$.id' | tags->'$[0]' |
+----+------------------+--------------+
|  1 | 1                | 1            |
+----+------------------+--------------+
1 row in set (0.00 sec)

mysql5.7 JSON相关函数

  1. JSON_TYPE JSON类型,ARRAY、STRING 所有的Json type类型如下

    BLOB
    BIT
    OPAQUE
    DATETIME
    TIME
    DATE
    BOOLEAN
    ARRAY
    OBJECT
    STRING
    INTEGER, DOUBLE
    NULL
  2. JSON_ARRAY SELECT JSON_ARRAY('a', 1, NOW());

mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+
  1. JSON_OBJECT
    mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
    +---------------------------------------+
    | JSON_OBJECT('key1', 1, 'key2', 'abc') |
    +---------------------------------------+
    | {"key1": 1, "key2": "abc"}            |
    +---------------------------------------+
  2. 等等

引用

  1. 百度百科 #https://baike.baidu.com/item/JSON/2462549
  2. mysql5.7官方文档 #https://dev.mysql.com/doc/refman/8.0/en/json.html

0 comments

To reply to the article, please Login or registered