结构化和非结构化数据

• 结构化

  • 二维表结构（行和列）
  • 使用SQL语句进行操作

• 非结构化(schema-free)

  • 使用Key-Value格式定义数据，无结构定义
  • Value可以嵌套Key-Value格式的数据
  • 使用JSON进行实现
    

例如 MongoDB 是这样定义表和插入数据的：

定义表：

db.createCollection("user")

插入数据：

db.user.insert({
 user_name:"tom",
 age:30
}) 

JSON介绍

• JSON（ Java Script Object Notation）是一种轻量级的数据交换语言，并且是独立于语言的文本格式。
• 一些 NoSQL 数据库选择 JSON 作为其数据存储格式，比如：MongoDB、CouchDB 等。
• MySQL 5.7.x 开始支持JSON数据类型。

5.7之前，只能把JSON当作BLOB进行存储。数据库层面无法对JSON数据做一些操作，只能由应用程序处理。换句话说，就是数据库不认识JSON格式的数据。

JSON VS BLOB

• JSON

  • JSON 数据可以做有效性检查;
  • JSON 使得查询性能提升;
  • JSON 支持部分属性索引，通过虚拟列的功能可以对JSON中的部分数据进行索引;

• BLOB

  • BLOB 类型无法在数据库层做约束性检查;
  • BLOB 进行查询，需要遍历所有字符串;
  • BLOB 做只能做指定长度的索引;

MySQL中的JSON数据存取

按照传统方式创建一个用户表 users，表有 id, name, email, address 等属性。在业备初期，列的设计可能并不能确定，如果业务跑了一段时间后，需要增长其它的列，是很痛苦的。

我们现在设计一个有 JSON 字段的表，将用户的属性列放到 JSON 中去：

mysql> CREATE TABLE UserJson(
    uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    data JSON
);

可以在 insert 记录的时候，传入 JSON 格式的值，也可以将现有 users 表中的数据查询出来保存到 UserJson 表中：

mysql> insert into UserJson 
SELECT 
    uid,JSON_OBJECT('name',name,'email',email,'address',address) AS data
FROM
    User;

mysql> select * from UserJson;
+-----+----------------------------------------------------------------------+
| uid | data                                                                 |
+-----+----------------------------------------------------------------------+
|   1 | {"name": "David", "email": "david@gmail.com", "address": "Shanghai"} |
|   2 | {"name": "Amy", "email": "amy@gmail.com", "address": "Beijing"}      |
|   3 | {"name": "Tom", "email": "tom@gmail.com", "address": "Guangzhou"}    |
+-----+----------------------------------------------------------------------+

JSON_OBJECT 函数的作用，就是将传入的值以 key-value 的方式组成 Json 格式。

可以使用 JSON_EXTRACT 来提取 key-value 中的值。例如，我想知道 uid 为 1 的员工的地址：

SQL> SELECT uid,JSON_EXTRACT(data,'$.address') 
        from UserJson 
        where uid=1;
        
+-----+--------------------------------+
| uid | JSON_EXTRACT(data,'$.address') |
+-----+--------------------------------+
|   1 | "Shanghai ..."                 |
+-----+--------------------------------+
1 row in set (0.00 sec)

同样，JSON_EXTRACT 也可以用于 where 子句，例如，想查寻 Tom 的地址：

mysql> SELECT JSON_EXTRACT(data,'$.address') 
        from UserJson 
        where JSON_EXTRACT(data,'$.name')='Tom';
        
+-----+--------------------------------+
| uid | JSON_EXTRACT(data,'$.address') |
+-----+--------------------------------+
|   3 | "Guangzhou ..."                |
+-----+--------------------------------+
1 row in set (0.00 sec)

EXTRACT 时，如果指定的列不存在，则返回 NULL。

使用了 JASON 后，如果想增加一个属性，就非常方便：

mysql> UPDATE UserJson
set data = json_insert(data,"$.address2","gaoxin")
where uid = 1;

这样一来， UID=1的记录里，data 字段就会多一个 "address2": "gaoxin"。

JSON创建索引

JSON 类型数据本身无法直接创建索引，需要将需要索引的JSON数据重新生成虚拟列(Virtual Columns)之后，对该列进行索引。