mysql中not-null插入问题

来源：互联网发布：章鱼搜索网络错误编辑：程序博客网时间：2024/05/22 11:17

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 setmysql> insert into t_info(addr) values ('上海');Query OK, 1 row affectedmysql> select * from t_info;+------+-----+------+| name | age | addr |+------+-----+------+|      |   0 | 上海 |+------+-----+------+1 row in setmysql> insert into t_info (name, age, addr) values(null, null, '北京');1048 - Column 'name' cannot be nullmysql> insert into t_info (name, age, addr) values('', null, '北京');1048 - Column 'age' cannot be nullmysql> insert into t_info (name, age, addr) values('', 12, '北京');Query OK, 1 row affectedmysql> 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 setmysql> 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 setmysql> insert into t_info(addr) values('江西');1364 - Field 'name' doesn't have a default value

NICE!!!

阅读全文

0 0