课程目录

第10天：JSON与数据库

概述

现代文档数据库和关系型数据库都能处理JSON形态的数据。今天将分别介绍MongoDB文档、MySQL的JSON字段和PostgreSQL的JSONB字段，并说明查询和索引方式的差异。

JSON与NoSQL数据库

NoSQL数据库是一种不依赖于SQL语言的数据库，它们通常支持存储半结构化数据，如JSON。这些数据库能够灵活地处理不断变化的数据结构。

示例：MongoDB中的JSON数据

MongoDB是一个流行的NoSQL数据库，数据实际以BSON文档存储，操作时通常使用与JSON相似的对象语法。BSON还支持日期、二进制等JSON规范之外的数据类型。

示例：在MongoDB中插入JSON数据

db.collection.insertOne({
  name: "John Doe",
  age: 30,
  address: {
    street: "123 Main St",
    city: "Anytown",
    zip: "12345"
  },
  hobbies: ["reading", "cycling", "hiking"]
});

在这个示例中，我们向MongoDB集合插入了一个包含个人信息的BSON文档。

JSON与关系型数据库

传统上，关系型数据库使用固定的表结构来存储数据。然而，许多关系型数据库现在也支持JSON数据类型，允许在单个列中存储JSON对象。

示例：MySQL中的JSON数据

MySQL提供原生JSON字段，会校验写入内容是否为合法JSON。下面创建表并插入一条记录：

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  data JSON NOT NULL
);

INSERT INTO users (id, data) VALUES
(1, '{"name": "John Doe", "age": 30}');

示例：PostgreSQL中的JSON数据

PostgreSQL同时支持JSON和JSONB。需要频繁查询和索引时通常选用JSONB，因为它以可处理的二进制结构存储数据。

示例：在PostgreSQL中插入JSON数据

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  data JSONB NOT NULL
);

INSERT INTO users (id, data) VALUES
(1, '{"name": "John Doe", "age": 30}');

在这个示例中，我们将一个JSON对象存储在users表的data列中。

查询JSON数据

无论是在NoSQL还是关系型数据库中，查询JSON数据都需要特定的语法和方法。

示例：在MongoDB中查询JSON数据

db.collection.find({"address.city": "Anytown"});

在这个示例中，我们查询了所有地址城市为"Anytown"的文档。

示例：在MySQL中查询JSON数据

SELECT *
FROM users
WHERE CAST(data->>'$.age' AS UNSIGNED) = 30;

->>会提取指定路径的标量文本；这里再转换为整数进行数值比较。

示例：在PostgreSQL中查询JSON数据

SELECT * FROM users WHERE data @> '{"age": 30}';

在这个示例中，我们查询了所有data列中包含{"age": 30}的记录。

JSON数据的索引和优化

为了提高查询性能，可以对JSON数据的特定键或路径创建索引。

示例：在MongoDB中为JSON数据创建索引

db.collection.createIndex({"address.city": 1});

在这个示例中，我们为address.city字段创建了一个索引。

示例：在MySQL中为JSON路径创建索引

ALTER TABLE users
  ADD COLUMN age INT
    GENERATED ALWAYS AS (
      CAST(data->>'$.age' AS UNSIGNED)
    ) STORED,
  ADD INDEX idx_users_age (age);

这个示例把JSON中的age提取为生成列，再对生成列建立普通索引。实际项目应只为稳定且经常查询的路径建立索引。

示例：在PostgreSQL中为JSON数据创建索引

CREATE INDEX idx_users_age ON users USING gin (data jsonb_path_ops);

这个GIN索引用于加速data列上的JSONB包含查询，并不是只索引age路径。若查询长期集中在单一路径，也可以针对提取表达式建立更小的B-tree索引。

结论

通过今天的学习，我们了解了MongoDB、MySQL和PostgreSQL存储、查询及索引JSON形态数据的基本方式。数据库选择与索引设计应根据数据结构是否稳定、查询模式和一致性要求决定，而不是仅因为数据看起来像JSON。

明天，我们将讨论JSON数据的安全问题和防护措施，这将帮助我们确保数据的安全性和完整性。

以上就是我们第十天课程的全部内容。希望您觉得有帮助，并为接下来的学习做好准备。如果您有任何疑问或需要进一步的解释，请随时联系我们。明天见！