MYSQL忽略非空约束，将空值插入表中，并且只显示警告

我使用的是MYSQL版本14.14 Distrib 5.7.40，适用于Linux（x86_64）。我创建了一个包含3列的表，其中的列设置了NOT NULL约束。然后，当我尝试将空值直接插入表中时，系统会显示一个错误，提示列不能为空。但是，如果我尝试使用select语句插入空值，然后MYSQL插入一个新记录，并且只显示一个警告。2在插入的记录中，我看到MYSQL用空字符串替换了NULL。

DROP TABLE IF EXISTS table1;

DROP TABLE IF EXISTS table2;

CREATE  TABLE IF NOT EXISTS table1 (
     id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
     col_1 VARCHAR(45) NOT NULL ,
     col_2 VARCHAR(45) NOT NULL ,
     PRIMARY KEY (id));

CREATE TABLE IF NOT EXISTS table2 LIKE table1;

-- gets inserted as expected
INSERT INTO table1(id, col_1, col_2) VALUES (1,'aaa', 'bbb');
INSERT INTO table2(id, col_1, col_2) VALUES (2,'ccc', 'ddd');

-- does not get inserted as expected
INSERT INTO table1(col_1, col_2) VALUES ('xxx', NULL); -- ERROR 1048 (23000): Column 'col_2' cannot be null

-- gets inserted which I didn't expect, it gives a warning but inserts
INSERT INTO table1(id, col_1, col_2) SELECT id, col_1, NULL FROM table2 where id=2; -- Query OK, 1 row affected, 1 warning (0.07 sec)

我试着在查询执行后显示警告，结果显示“警告|小行星1048|列'col_2'不能是”“。但MYSQL仍然没有阻止我向表中插入值，它替换了空字符串。

mysql> show warnings;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'col_2' cannot be null |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

mysql> select * from table1;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  1 | aaa   | bbb   |
|  2 | ccc   |       |
+----+-------+-------+
2 rows in set (0.01 sec)

我为什么要回答自己的问题？我有这个问题，并找到了类似的问题。答案简要地告诉我SQL STRICT MODE。但没有一个答案是全面的，所以我不得不自己挖掘。我已经了解了MYSQL NOT NULL约束和SQL STRICT MODE是如何一起工作的，我想在这里详细说明我的答案。
首先，让我们了解一下，当我们尝试将NULL值插入到具有NOT NULL约束的列中时会发生什么。
table1有三个列id、col_1和col_2，它们都具有NOT NULL约束条件

mysql> desc table1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| col_1 | varchar(45)      | NO   |     | NULL    |                |
| col_2 | varchar(45)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.03 sec)

当我们试图向列中插入显式NULL值时，MYSQL将抛出一个错误。

mysql> insert into table1(col_1, col_2) values (NULL,'xxx');
ERROR 1048 (23000): Column 'col_1' cannot be null

然而，如果我们试图跳过一列（不插入值），MYSQL不会给予错误，而是插入记录，只给出一个警告。

mysql> insert into table1(col_2) values ('xxx');
Query OK, 1 row affected, 1 warning (0.10 sec)

mysql> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1364 | Field 'col_1' doesnt have a default value |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from table1;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  4 |       | xxx   |
+----+-------+-------+
1 row in set (0.00 sec)

我们也可以使用选择查询将值插入到表中。在这种情况下，MYSQL也只给出一个警告并插入记录。

mysql> insert into table1(col_1, col_2) select NULL, col_2 from table2;
Query OK, 1 row affected, 1 warning (0.07 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+-------------------------------+
| Level   | Code | Message                       |
+---------+------+-------------------------------+
| Warning | 1048 | Column 'col_1' cannot be null |
+---------+------+-------------------------------+
1 row in set (0.00 sec)

mysql> select * from table1;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
|  4 |       | xxx   |
|  5 |       | ddd   |
+----+-------+-------+
2 rows in set (0.00 sec)

现在让我们来了解一下MYSQL中的SQL模式。严格模式控制MySQL如何处理数据更改语句（如INSERT或UPDATE）中的无效值或缺失值。请参考MYSQL文档了解详细信息。
要检查当前的SQL模式，请尝试以下操作。

mysql> select @@sql_mode;
+----------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                         |
+----------------------------------------------------------------------------------------------------+
| NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

要将MYSQL设置为严格模式，请尝试以下操作。

mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@sql_mode;
+-------------------+
| @@sql_mode        |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

在MYSQL设置为严格模式后，如果您尝试前面的插入语句，它们将失败并出现错误。

mysql> insert into table1(col_2) values ('xxx');
ERROR 1364 (HY000): Field 'col_1' doesnt have a default value
mysql> insert into table1(col_1, col_2) select NULL, col_2 from table2;
ERROR 1048 (23000): Column 'col_1' cannot be null

还有一点是SQL_MODE只会在当前的MYSQL会话中设置。如果你想永远设置它，那么你必须在GLOBAL或MYSQL配置文件中设置它。
请尝试以下操作将严格模式设置为全局SQL模式。

mysql> set global sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| STRICT_ALL_TABLES |
+-------------------+
1 row in set (0.00 sec)

我希望这个很长的答案能对SQL模式和MYSQL中的插入有所帮助。如果你觉得这个问题和答案有帮助，请投票支持。

MYSQL忽略非空约束，将空值插入表中，并且只显示警告

1条答案

相关问题

热门标签

最新问答