MySQL 不严格模式怎样确保数据完整性（mysql 不严谨模式）

MySQL 不严格模式：怎样确保数据完整性？

MySQL 是一款开源的关系型数据库管理系统，它广泛应用于 Web 开发和数据管理领域。MySQL 提供了严格模式和非严格模式，其中严格模式是默认的。不过，有时候我们可能需要使用 MySQL 的非严格模式来降低数据验证的限制，提升数据插入和修改的效率。但是，非严格模式也会影响到数据的完整性，因此我们需要采取一些措施来确保数据的完整性。

MySQL 非严格模式的影响

MySQL 的非严格模式，允许一些不合法的数据进行插入和修改。下面是一些常见的非严格模式对数据完整性的影响。

1. 字符串和数值类型的转换

在 MySQL 非严格模式下，字符串和数值类型可以相互转换，因此可以将一个字符串值插入到一个数值类型的字段中。这可能会导致一些意料之外的结果，如数据截断、数据丢失等。

下面是一些示例代码：

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  price DECIMAL(10,2)
);

INSERT INTO mytable(id, price) VALUES (1, '2.99');
SELECT * FROM mytable;
-- Output:
-- id | price
-- ---|-----
-- 1  | 2.99
INSERT INTO mytable(id, price) VALUES (2, 'text');
-- Output:
-- Error: Error Code: 1366. Incorrect decimal value: 'text' for column 'price' at row 1

可以看到，将字符串 ‘text’ 插入到 DECIMAL 类型的 price 字段中，会发生数据类型不匹配的错误。

2. 空值和默认值的处理

在 MySQL 非严格模式下，当插入或更新数据时，可以不用指定所有字段的值，而是使用空值 NULL 或默认值。这种做法可能会导致一些字段没有数据，从而出现数据不完整的情况。

下面是一些示例代码：

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(20) DEFAULT 'unknown',
  age INT
);
INSERT INTO mytable(id, age) VALUES (1, 20);
SELECT * FROM mytable;
-- Output:
-- id | name    | age
-- ---|---------|---
-- 1  | unknown | 20
UPDATE mytable SET age=NULL WHERE id=1;
SELECT * FROM mytable;
-- Output:
-- id | name    | age
-- ---|---------|---
-- 1  | unknown | NULL

可以看到，在 MySQL 非严格模式下，通过 UPDATE 语句将 age 字段的值设为 NULL，实际上就是删除了这个字段的数据。

确保数据完整性

为了确保数据的完整性，我们需要采取以下措施。

1. 使用 ENUM 类型

ENUM 类型可以在一组预定义的值中进行选择，因此不会有意外的字符串或数字值出现。当对字段进行 INSERT 或 UPDATE 操作时，如果赋值为 ENUM 类型中不存在的值，会出现错误。

下面是一个示例代码：

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  status ENUM('idle', 'working', 'done')
);

INSERT INTO mytable(id, status) VALUES (1, 'idle');
SELECT * FROM mytable;
-- Output:
-- id | status 
-- ---|--------
-- 1  | idle   
INSERT INTO mytable(id, status) VALUES (2, 'pending');

-- Output:
-- Error: Error Code: 1265. Data truncated for column 'status' at row 1

可以看到，当向 status 字段插入 ENUM 类型中不存在的值 ‘pending’ 时，会出现数据截断的错误。

2. 使用 NOT NULL 约束

NOT NULL 约束可以确保字段不为空，并防止在 INSERT 或 UPDATE 操作中出现空值。当字段不可为空时，必须为其指定一个值，否则会出现错误。

下面是一个示例代码：

CREATE TABLE mytable (
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL,
  phone VARCHAR(11) NOT NULL
);
INSERT INTO mytable(id, name, phone) VALUES (1, 'Tom', '1234567890');
SELECT * FROM mytable;
-- Output:
-- id | name | phone      
-- ---|------|------------
-- 1  | Tom  | 1234567890 
INSERT INTO mytable(id, name) VALUES (2, 'Jerry');

-- Output:
-- Error: Error Code: 1048. Column 'phone' cannot be null

可以看到，当将 phone 字段设为 NOT NULL 约束时，INSERT 语句中必须指定 phone 的值，否则会出现错误。

总结

MySQL 不严格模式可以降低数据验证的限制，提升数据插入和修改的效率。但是，非严格模式也会影响到数据的完整性，因此我们需要采取一些措施，如使用 ENUM 类型、使用 NOT NULL 约束等，来确保数据的完整性。