JSON类型相关函数

JSON_ARRAY()

返回json数组

  1. mysql> SELECT JSON_ARRAY(1,'A',true);;
  2. +------------------------+
  3. | JSON_ARRAY(1,'A',true) |
  4. +------------------------+
  5. | [1, "A", true]         |
  6. +------------------------+

JSON_OBJECT()

返回json对象

  1. mysql> SELECT JSON_OBJECT("name",'小明',"age",18);;
  2. +---------------------------------------+
  3. | JSON_OBJECT("name",'小明',"age",18)   |
  4. +---------------------------------------+
  5. | {"age": 18, "name": "小明"}           |
  6. +---------------------------------------+
  7. 1 row in set (0.00 sec)

插入JSON数据

  1. -- 插入json数据
  2. mysql> INSERT INTO json_tab(content) VALUES (JSON_OBJECT("id",1,"name","小明","age",18));;
  3. -- 查看数据
  4. mysql> SELECT * FROM json_tab;;
  5. +----+----------------------------------------+
  6. | id | content                                |
  7. +----+----------------------------------------+
  8. |  1 | {"id": 1, "age": 18, "name": "小明"}   |
  9. +----+----------------------------------------+

创建JSON表

mysql 8 开始有JSON关键字来创建JSON对象

  1. mysql> CREATE TABLE json_tab(
  2.     -> id INT AUTO_INCREMENT PRIMARY KEY,
  3.     -> content JSON NOT NULL
  4.     -> );;