Tidb 与 Mysql 关于not null不兼容

【TiDB 使用环境】测试环境
【TiDB 版本】8.5.1
【操作系统】ubuntu
【部署方式】天翼云,32g/16c
【集群数据量】几百G
【集群节点数】3节点
【其他附件:截图/日志/监控】

数据表创建,指定not null 且 有默认值,当sql显示插入null,tidb提示不能是null,但是mysql可以兼容这种场景

看看对比下mysql的sql_mode变量,和tidb是否一样

1 个赞

是的,得看下 sql mode 得设置,https://docs.pingcap.com/zh/tidb/stable/sql-mode/#sql-模式

STRICT_TRANS_TABLES 模式开启时,TiDB 会严格检查 NOT NULL 字段插入 NULL,直接报错。

1 个赞

一般这种情况,我们首先检查sql_mode变量。

试下,还没遇到过这个情况

是sqlmode的原因吗,两者sqlmode不一样?

1、TiDB的表定义里面明确指定了【NOT NULL】了,所以值肯定不能为NULL了,否则就违反了表的定义要求了,tidb这里的做法更加严谨、可靠,是符合预期的,应该推荐这个用法。

2、如果想要在TiDB v8.5.1里的表插入date_add为NULL值,那么需要楼主修改表结构的定义,把date_add字段的【NOT NULL】去掉,即改为:date_add timestamp DEFAULT CURRENT_TIMESTAMP 。

具体可以参考如下:
3、5.7.54确实如楼主所言,即便设置了NOT NULL,还是可以插入NULL值。(不按照预期来)

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

mysql> show variables like '%sql_mode%' ;
+---------------------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name             | Value                                                                                                                  |
+---------------------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode                  | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------------------+------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.01 sec)

mysql> create table t (id int(11), update_time timestamp NULL DEFAULT NULL , date_add timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, primary key(id)) ;
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t (id,update_time,date_add) values (1,NULL,NULL) ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+----+-------------+---------------------+
| id | update_time | date_add            |
+----+-------------+---------------------+
|  1 | NULL        | 2025-06-25 09:34:32 |
+----+-------------+---------------------+
1 row in set (0.00 sec)

mysql> 

4、TiDB v8.5.1,可以通过去掉 NOT NULL限制实现插入NULL值。

  • 对于设置了NOT NULL属性的列,不支持插入NULL值,符合定义,不存在问题。严谨可靠!
mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v8.5.1 |
+--------------------+
1 row in set (0.00 sec)

mysql> show variables like '%sql_mode%' ;
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                                     |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int NOT NULL,
  `update_time` timestamp NULL DEFAULT NULL,
  `date_add` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> insert into t (id,update_time,date_add) values (1,NULL,NULL) ;
ERROR 1048 (23000): Column 'date_add' cannot be null

  • 去掉NOT NULL限制后,字段列可以插入NULL值。
mysql> create table t1 (id int(11), update_time timestamp NULL DEFAULT NULL , date_add timestamp DEFAULT CURRENT_TIMESTAMP, primary key(id)) ;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL,
  `update_time` timestamp NULL DEFAULT NULL,
  `date_add` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)

mysql> insert into t1 (id,update_time,date_add) values (1,NULL,NULL) ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 ;
+----+-------------+----------+
| id | update_time | date_add |
+----+-------------+----------+
|  1 | NULL        | NULL     |
+----+-------------+----------+
1 row in set (0.00 sec)

mysql> insert into t1 (id,update_time) values (2,NULL) ;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1 ;
+----+-------------+---------------------+
| id | update_time | date_add            |
+----+-------------+---------------------+
|  1 | NULL        | NULL                |
|  2 | NULL        | 2025-06-25 09:32:19 |
+----+-------------+---------------------+
2 rows in set (0.00 sec)

mysql> 

这么看这应该是MySQL的bug

在 TiDB 和 MySQL 中,当你为一个列定义 NOT NULL 约束时,该列不允许插入或更新为 NULL 值。如果尝试这样做,将会返回错误。这一点两者是相同的。

确认一下你的版本,8.5的发行说明中提到是已经修复:

  • 为了与 MySQL 兼容,在非严格模式下 (sql_mode = '') 向非 NULL 列插入 NULL 值会报错。 #55457 @joechenrh

:thinking:那估计是遇到这个问题了,应该就是sql_mode的问题。

能展开说说么?