mysql中not-null插入问题

最新推荐文章于 2023-11-11 12:26:08 发布

原创最新推荐文章于 2023-11-11 12:26:08 发布 · 2.3k 阅读

2 ·

CC 4.0 BY-SA版权

mysql学习专栏收录该内容

38 篇文章

订阅专栏

本文探讨了MySQL中NOT NULL约束的实际应用及其与SQL_MODE设置的关系。通过示例展示了默认情况下MySQL如何处理空值插入，并介绍了如何通过设置STRICT_TRANS_TABLES来启用严格模式，确保数据完整性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql表结构中not null插入的问题

问题

mysql> desc t_info;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | NO   |     | NULL    |       |
| age   | int(11)      | NO   |     | NULL    |       |
| addr  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set

mysql> insert into t_info(addr) values ('上海');
Query OK, 1 row affected


mysql> select * from t_info;
+------+-----+------+
| name | age | addr |
+------+-----+------+
|      |   0 | 上海 |
+------+-----+------+
1 row in set

mysql> insert into t_info (name, age, addr) values(null, null, '北京');
1048 - Column 'name' cannot be null
mysql> insert into t_info (name, age, addr) values('', null, '北京');
1048 - Column 'age' cannot be null
mysql> insert into t_info (name, age, addr) values('', 12, '北京');
Query OK, 1 row affected

mysql> select * from t_info;
+------+-----+------+
| name | age | addr |
+------+-----+------+
|      |   0 | 上海 |
|      |  12 | 北京 |
+------+-----+------+
2 rows in set

what?我明明是设置了表结构中nama和age字段是not null的，发生了什么？怎么可能将’上海’这条数据插入？应该是要报错的！！！

mysql> show create table t_info;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                             |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_info | CREATE TABLE `t_info` (
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set

原因

查了一下资料，原来是mysql的sql_mode的设置问题

mysql> show variables like '%str%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_strict_mode | OFF   |
+--------------------+-------+
1 row in set

mysql> select @@sql_mode;
+------------------------+
| @@sql_mode             |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set

需要将sql_mode设置成STRICT_TRANS_TABLES,即严格模式，进行数据的严格校验，错误数据不能插入，报error错误

mysql> set @@sql_mode=STRICT_TRANS_TABLES;
Query OK, 0 rows affected

//临时设置，重启失效!!永久设置需修改配置文件my*.ini文件
mysql> select @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set

mysql> insert into t_info(addr) values('江西');
1364 - Field 'name' doesn't have a default value

NICE!!!