MySQL 严格模式下的验证

MySQL是最流行的开源数据库之一，其严格模式是一种配置，可以提高MySQL的数据完整性和一致性。在本文中，我们将重点讨论MySQL在严格模式下进行的验证操作。

阅读更多：MySQL 教程

什么是MySQL严格模式？

MySQL严格模式是一种强制执行严格数据输入检查的功能，它可以帮助开发人员和管理人员更好地保护数据的一致性和完整性。默认情况下，MySQL处于非严格模式，但可以通过在MySQL配置中设置mode=strict来启用它。当严格模式被启用时，MySQL会执行下列操作：

不允许插入空值数据到非空字段中；
必须为所有分组表达式列包含在GROUP BY中；
遇到无效日期时报错而非警告；
要求外键应用于支持完整性的表格。

严格模式下的验证操作

在MySQL的严格模式下，有许多方面的验证操作。下面是严格模式下MySQL执行的一些验证操作。

插入非空数据到非空字段

在MySQL的严格模式下，不允许将空数据插入到非空字段中。如果尝试这样做，MySQL会返回以下错误：ERROR 1048 (23000): Column ‘column_name’ cannot be null

例如，在以下表格中，如果尝试将一个空值插入到name列，将会引发错误：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

INSERT INTO users (name, email) VALUES (NULL, 'test@test.com');

输出：

ERROR 1048 (23000): Column 'name' cannot be null

GROUP BY中包含所有分组表达式列

在MySQL的严格模式下，必须在SELECT查询中的GROUP BY子句中包含所有分组表达式列。如果GROUP BY子句不完整，则会返回以下错误：ERROR 1055 (42000): Expression #n of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘column_name’.

例如，如果对以下表格执行以下查询，则会引发上述错误：

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    country VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

SELECT name, country, COUNT(*) FROM contacts GROUP BY name;

输出：

ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.contacts.country' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

在上述查询中，country列不包含在GROUP BY子句中，并且没有聚合函数。因此，MySQL会产生一个错误。

报告无效日期

在MySQL的严格模式下，遇到无效的日期会报错，而不仅是警告。如果尝试将无效的日期插入到MySQL日期或日期时间字段中，则会返回以下错误：ERROR 1292 (22007): Incorrect datetime value: ‘invalid_date’ for column ‘column_name’.

例如，在以下表格中，如果尝试插入一个无效日期，将会引发错误：

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL
);

INSERT INTO orders (order_date, customer_id, total) VALUES ('2022-02-31', 1001, 500.00);

输出：

ERROR 1292 (22007): Incorrect date value: '2022-02-31' for column 'order_date' at row ...\n"

引用被外键约束的表格

在MySQL中，外键是一种用于管理表格之间引用完整性的机制。在严格模式下，MySQL要求对于支持完整性的表格，必须定义外键约束。如果尝试向未定义外键约束的表格中插入数据，则会引发错误。

例如，在以下表格中，orders表格的customer_id列引用customers表格的id列。在严格模式下，如果orders表格没有定义外键约束，则会引发错误。

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

总结

MySQL的严格模式是一种用于验证数据完整性和一致性的机制。在严格模式下，MySQL要求进行更严格的数据输入检查，包括禁止将空值插入到非空字段中，要求在GROUP BY子句中包含所有分组表达式列，在遇到无效日期时报错而非警告，以及要求定义外键约束以支持引用完整性。了解MySQL严格模式下的验证操作，可以帮助开发人员和管理人员更好地保护他们的数据，并降低数据损坏和数据丢失的风险。