首页 » MySQL/TiDB/GoldenDB » Inserting no value for the column NOT NULL and no explicit DEFAULT clause refused , After upgrade MySQL5.6 default

Inserting no value for the column NOT NULL and no explicit DEFAULT clause refused , After upgrade MySQL5.6 default

  • 2014/08/29
  • MySQL/TiDB/GoldenDB
  • 462 views
  • Inserting no value for the column NOT NULL and no explicit DEFAULT clause refused , After upgrade MySQL5.6 default已关闭评论

前段时间升级MySQL到了5.6，但是后来app 后台日志显示一些SQL insert 出错，手动执行提示有些字段没有default值，查看了表语法该字段是Not null，而且升级也是OS copy数据库文件（因为是MyISAM 引擎），对比升级前后表结构一致，正常对于not null 的列并且没有default的值，当在insert 时 而在值列表未指定值理论就是应该不通过的，只能说是5.6以前 忽略了not null 的约束。

下面我演示一下。

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.10-log |
+------------+
1 row in set (0.00 sec)

mysql> create table t(id int primary key not null auto_increment, name varchar(20) not null, addr varchar(10))  ENGINE=MyISAM ; 
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t(addr) values('bj');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t;
+----+------+------+
| id | name | addr |
+----+------+------+
|  1 |      | bj   |
+----+------+------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.00 sec)

Tip:
看到5.5是可以insert 只是 MySQL自动给not null的值加了default null。下面看一下5.6

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.16-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t(id int primary key not null auto_increment, name varchar(20) not null, addr varchar(10))  ENGINE=MyISAM ; 
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t(addr) values('bj');
ERROR 1364 (HY000): Field 'name' doesn't have a default value

mysql> SELECT @@SESSION.sql_mode;
+--------------------------------------------+
| @@SESSION.sql_mode                         |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

后来google到了一些案例
如
Inserting NULLs into NOT NULL columns in 5.6: refused by default

MySQL ignores the NOT NULL constraint

“As of MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause. This is the same as before 5.0.2.

If the column cannot take NULL as the value, MySQL defines the column with no explicit DEFAULT clause. For data entry, if an INSERT or REPLACE statement includes no value for the column, or an UPDATE statement sets the column to NULL, MySQL handles the column according to the SQL mode in effect at the time:

If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type.

If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted.”

The default SQL mode in MySQL 5.6.6 and later is NO_ENGINE_SUBSTITUTION; in MySQL 5.6.5 and earlier, it was empty (no modes set).

To change the SQL mode at runtime, set the global or session sql_mode system variable using a SET statement:

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

于是参考上面的提示，修改SQL_MODE ，回退到以前的处理方式，因为程序程序没有人来修改原因。下面修改MySQL5.6

mysql> SELECT @@SESSION.sql_mode;
+--------------------------------------------+
| @@SESSION.sql_mode                         |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';    
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+------------------------+
| @@SESSION.sql_mode     |
+------------------------+
| NO_ENGINE_SUBSTITUTION |
+------------------------+
1 row in set (0.00 sec)

mysql> insert into t(addr) values('bj');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from t;
+----+------+------+
| id | name | addr |
+----+------+------+
|  1 |      | bj   |
+----+------+------+
1 row in set (0.00 sec)

mysql> SET  GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';       
Query OK, 0 rows affected (0.00 sec)

Related Posts:

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：