当前位置： 首页 > article >正文

数据库的两种模式

article 2025/3/21 23:01:24

数据库的 严格模式（Strict Mode） 和 宽松模式（Non-Strict Mode） 是数据库管理系统（DBMS）中用于控制数据验证和处理方式的两种不同模式。它们的主要区别在于对数据完整性、一致性和错误处理的严格程度。

1. 严格模式（Strict Mode）

在严格模式下，数据库会对数据的插入、更新和删除操作进行严格的验证，确保数据的完整性和一致性。如果数据不符合定义的表结构或约束条件，数据库会拒绝操作并返回错误。

特点
  • 数据验证：
    • 数据库会检查数据的类型、长度、唯一性、外键约束等。
    • 例如，如果尝试插入一个超出字段长度的字符串，数据库会拒绝操作。
  • 错误处理：
    • 如果数据不符合要求，数据库会直接抛出错误，操作失败。
  • 数据完整性：
    • 严格模式确保数据库中的数据始终符合预定义的规则，避免脏数据。
  • 适用场景：
    • 适用于对数据完整性要求高的场景，如金融系统、医疗系统等。
示例

假设有一个表 users，定义如下：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 0)
);

在严格模式下：

  • 如果尝试插入一个 name 为 NULL 的记录，数据库会拒绝操作。
  • 如果尝试插入一个 age 为负数的记录，数据库会拒绝操作。

2. 宽松模式（Non-Strict Mode）

在宽松模式下，数据库对数据的验证较为宽松，允许某些不符合规则的数据插入或更新，可能会自动截断数据或使用默认值。

特点
  • 数据验证：
    • 数据库对数据的验证较为宽松，可能会忽略某些约束条件。
    • 例如，如果插入一个超出字段长度的字符串，数据库可能会自动截断字符串。
  • 错误处理：
    • 如果数据不符合要求，数据库可能会尝试修复或忽略错误，而不是直接抛出错误。
  • 数据完整性：
    • 宽松模式可能导致数据不一致或脏数据，因为某些不符合规则的数据可能被允许插入。
  • 适用场景：
    • 适用于对数据完整性要求较低的场景，如日志记录、临时数据存储等。
示例

在宽松模式下：

  • 如果尝试插入一个 name 为 NULL 的记录，数据库可能会自动填充默认值（如空字符串）。
  • 如果尝试插入一个 age 为负数的记录，数据库可能会忽略约束条件并允许插入。

3. 严格模式 vs 宽松模式

特性严格模式宽松模式
数据验证严格验证，拒绝不符合规则的数据宽松验证，可能允许不符合规则的数据
错误处理抛出错误，操作失败可能忽略错误或自动修复
数据完整性高，确保数据始终符合规则低，可能导致脏数据或不一致
适用场景金融、医疗等高完整性要求的系统日志记录、临时数据存储等低要求场景

4. 如何设置严格模式或宽松模式

不同的数据库管理系统（如 MySQL、PostgreSQL、SQLite）对严格模式和宽松模式的支持和设置方式有所不同。

MySQL
  • 严格模式通过 sql_mode 参数控制。
  • 启用严格模式：
    SET sql_mode = 'STRICT_ALL_TABLES';
  • 禁用严格模式：
    SET sql_mode = '';
PostgreSQL
  • PostgreSQL 默认启用严格模式，不支持宽松模式。
  • 如果需要宽松行为，可以通过触发器或规则手动实现。
SQLite
  • SQLite 默认是宽松模式，但可以通过 PRAGMA 命令启用严格模式：
    PRAGMA foreign_keys = ON; -- 启用外键约束
PRAGMA ignore_check_constraints = OFF; -- 启用检查约束

5. 选择模式的建议

  • 严格模式：
    • 适用于对数据完整性要求高的场景。
    • 可以避免脏数据和不一致问题。
  • 宽松模式：
    • 适用于对数据完整性要求较低的场景。
    • 可以提高开发效率，但需要额外注意数据质量问题。

总结

严格模式和宽松模式是数据库管理中的重要概念，选择哪种模式取决于应用场景和对数据完整性的要求。严格模式适合高完整性要求的系统，而宽松模式适合对数据质量要求较低的场景。

数据库的常见模式分类:

sql_mode 是 MySQL 中的一个系统变量，它定义了数据库的运行模式，控制了 SQL 语句的解析和执行方式。通过设置不同的 sql_mode，可以启用或禁用特定的 SQL 特性和行为，从而改变数据库的行为方式。

sql_mode 可以包含多个选项，这些选项会影响数据验证、日期/时间处理、空值处理等方面的行为。sql_mode 变量可以在会话级别（针对当前连接）和全局级别（对所有连接）进行设置。

常见的 sql_mode 选项包括：

  1. STRICT_TRANS_TABLES：在严格模式下，如果出现不合法的数据插入（例如，插入空值到非空字段，插入超出字段长度的值等），MySQL 会返回错误而不是警告。如果没有设置该选项，MySQL 会将数据插入并发出警告。

  2. ALLOW_INVALID_DATES：允许插入无效日期（如 ‘2023-02-30’），而在严格模式下，MySQL 会拒绝插入无效日期。

  3. NO_ZERO_DATE：禁止插入值为 ‘0000-00-00’ 的日期。如果开启该模式，插入该日期时会返回错误。

  4. NO_ZERO_IN_DATE：禁止在日期中出现零日（例如，‘2023-01-00’）。该模式会在遇到零日时抛出错误。

  5. ERROR_FOR_DIVISION_BY_ZERO：在除零操作时抛出错误。在该模式下，执行除以零的操作会导致错误，而不是返回 NULL 或其他值。

  6. NO_AUTO_CREATE_USER：禁止 GRANT 语句自动创建用户。如果没有此模式，GRANT 语句可以在没有先前创建用户的情况下自动创建用户。

  7. NO_ENGINE_SUBSTITUTION：禁止在表的存储引擎不可用时使用默认存储引擎。当启用此选项时，如果指定的存储引擎不可用，MySQL 会抛出错误，而不是回退到默认的存储引擎。

  8. ONLY_FULL_GROUP_BY：启用此模式时，GROUP BY 子句必须包含所有在 SELECT 列表中的非聚合列。也就是说，查询必须按照标准 SQL 语法进行，避免在 GROUP BY 中省略某些字段。这有助于避免不明确的分组操作。

  9. TRADITIONAL：传统模式，它相当于启用了多种严格模式，确保 MySQL 更加严格地执行 SQL 语句，避免一些不良的行为，比如插入无效日期或自动类型转换。

  10. ANSI：启用 ANSI SQL 语法模式，会禁用 MySQL 特有的语法特性，并且遵循标准的 SQL 语法。

  11. STRICT_ALL_TABLES：启用严格模式，对所有表都应用严格检查，和 STRICT_TRANS_TABLES 类似，但是作用范围更广。

设置和查看 sql_mode

  • 查看当前的 sql_mode：

    SELECT @@sql_mode;

  • 设置会话级别的 sql_mode：

    SET SESSION sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

  • 设置全局级别的 sql_mode：

    SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

  • 设置为某个预定义的模式（例如 TRADITIONAL）：

    SET sql_mode = 'TRADITIONAL';

通过合理配置 sql_mode，可以确保数据库在处理数据时符合预期的规范和行为，避免出现潜在的错误或不一致。

以下是每个选项的详细解释：

好的，以下是 sql_mode 中每个选项的详细目的、作用以及示例：

1. STRICT_TRANS_TABLES

  • 目的：启用严格模式，确保插入的数据符合数据库约束条件，避免插入无效或不合法的数据。
  • 作用：当插入的数据不符合表的约束（例如，插入空值到非空字段、插入超出字段长度的数据）时，MySQL 会抛出错误，而不是警告或自动修正。
  • 示例：
    • 如果一个列定义为 NOT NULL，而插入 NULL 值：
      CREATE TABLE test (id INT NOT NULL);
INSERT INTO test (id) VALUES (NULL);  -- 会抛出错误，因为 id 是 NOT NULL
    • 如果插入的字符串长度超过字段长度限制：
      CREATE TABLE test (name VARCHAR(5));
INSERT INTO test (name) VALUES ('LongName');  -- 会抛出错误，因为 'LongName' 长度超过 5

2. ALLOW_INVALID_DATES

  • 目的：允许插入无效日期数据（如 ‘2023-02-30’）。
  • 作用：在没有严格模式时，MySQL 会允许插入无效的日期值。
  • 示例：
    SET sql_mode = 'ALLOW_INVALID_DATES';
CREATE TABLE test (date_col DATE);
INSERT INTO test (date_col) VALUES ('2023-02-30');  -- 插入成功，即使日期无效

3. NO_ZERO_DATE

  • 目的：禁止插入值为 ‘0000-00-00’ 的日期。
  • 作用：如果插入一个日期为 ‘0000-00-00’，会抛出错误。
  • 示例：
    SET sql_mode = 'NO_ZERO_DATE';
CREATE TABLE test (date_col DATE);
INSERT INTO test (date_col) VALUES ('0000-00-00');  -- 会抛出错误

4. NO_ZERO_IN_DATE

  • 目的：禁止在日期中出现零日（如 ‘2023-01-00’）。
  • 作用：如果日期中出现零日（‘00’ 日），会抛出错误。
  • 示例：
    SET sql_mode = 'NO_ZERO_IN_DATE';
CREATE TABLE test (date_col DATE);
INSERT INTO test (date_col) VALUES ('2023-01-00');  -- 会抛出错误

5. ERROR_FOR_DIVISION_BY_ZERO

  • 目的：在除零操作时抛出错误。
  • 作用：如果发生除以零的运算，MySQL 会抛出错误，而不是返回 NULL 或其他结果。
  • 示例：
    SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO';
SELECT 1 / 0;  -- 会抛出错误，因为除以零

6. NO_AUTO_CREATE_USER

  • 目的：禁止 GRANT 语句自动创建用户。
  • 作用：当使用 GRANT 语句授权时，如果指定的用户不存在，MySQL 不会自动创建该用户。
  • 示例：
    SET sql_mode = 'NO_AUTO_CREATE_USER';
GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'localhost';  -- 如果用户 'new_user' 不存在，会抛出错误

7. NO_ENGINE_SUBSTITUTION

  • 目的：禁止在指定的存储引擎不可用时回退到默认存储引擎。
  • 作用：如果指定的存储引擎不可用，MySQL 会抛出错误，而不是使用默认的存储引擎（如 InnoDB）。
  • 示例：
    SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE test (id INT) ENGINE=MYISAM;  -- 如果 MYISAM 不可用，会抛出错误

8. ONLY_FULL_GROUP_BY

  • 目的：强制在 GROUP BY 中包含所有非聚合列，遵循 SQL 标准。
  • 作用：当进行 GROUP BY 查询时，必须显式地在 GROUP BY 子句中包含所有非聚合列，否则查询会抛出错误。
  • 示例：
    SET sql_mode = 'ONLY_FULL_GROUP_BY';
CREATE TABLE test (id INT, name VARCHAR(20));
SELECT id, name FROM test GROUP BY id;  -- 会抛出错误，因为 `name` 没有被聚合或包含在 GROUP BY 中

9. TRADITIONAL

  • 目的：启用严格模式，确保 MySQL 更加严格地执行 SQL 语句。
  • 作用：启用此模式时，相当于启用了 STRICT_TRANS_TABLES、NO_ZERO_DATE、NO_ZERO_IN_DATE 等多个严格模式，确保数据更严格地符合约束条件。
  • 示例：
    SET sql_mode = 'TRADITIONAL';
CREATE TABLE test (id INT NOT NULL, name VARCHAR(5));
INSERT INTO test (id, name) VALUES (1, 'LongName');  -- 会抛出错误，因为 name 的长度超出了限制

10. ANSI

  • 目的：启用 ANSI SQL 语法模式，禁用 MySQL 特有的语法。
  • 作用：此模式会使 MySQL 遵循标准 SQL 语法，禁用 MySQL 特有的扩展特性。
  • 示例：
    SET sql_mode = 'ANSI';
CREATE TABLE test (id INT);
INSERT INTO test (id) VALUES (1);  -- 使用标准的 SQL 语法进行操作

11. STRICT_ALL_TABLES

  • 目的：对所有表应用严格模式。
  • 作用：启用此模式时，对所有表的数据操作都会严格检查，确保数据符合字段约束。
  • 示例：
    SET sql_mode = 'STRICT_ALL_TABLES';
CREATE TABLE test (id INT NOT NULL, name VARCHAR(5));
INSERT INTO test (id, name) VALUES (1, 'LongName');  -- 会抛出错误，因为 'LongName' 超过了字段长度限制

总结

sql_mode 可以根据需要启用不同的选项来严格控制 MySQL 数据库的行为，确保数据符合预期的规则。通过配置这些选项，可以防止一些潜在的错误和不一致性，保证数据的准确性和完整性。
``


http://www.kler.cn/a/594569.html

相关文章：

  • L1-005-008
  • 掌握 Shopee 商品数据：用爬虫解锁无限商机
  • 鸿蒙NEXT开发之开屏广告实现
  • 力扣hot100——三数之和（双指针）
  • SVN 泄露
  • 从模拟到现实：Sensodrive高精度力反馈技术赋能物流运输的高效与安全
  • 【OCR】使用Umi-OCR进行PDF文档的光学字符识别
  • 视频推拉流EasyDSS点播平台云端录像播放异常的问题排查与解决
  • Git 使用笔记
  • Redis常用数据类型深度解析：从理论到最佳实践
  • 宇树科技纯技能要求总结
  • 群体智能优化算法-牛顿-拉夫逊优化算法（Newton-Raphson-Based Optimizer, NRBO，含Matlab源代码）
  • 企业数字化20项目规划建设方案微服务场景与数据应用（50页PPT）（文末有下载方式）
  • 图解AUTOSAR_CP_SOMEIP_TransportProtocol
  • TCP/Socket
  • lua垃圾回收
  • MySQL:建表，修改，删除
  • Tailwind CSS 学习笔记(三)
  • Swagger2 使用教程
  • 如何根据 CUDA 配置安装 PyTorch 和 torchvision（大模型 环境经验）