h2在MySQL兼容模式下不强制执行NOT NULL [英] h2 does not enforce NOT NULL in MySQL compatibility mode
问题描述
在MySQL兼容模式下,以下SQL成功并返回0:
In MySQL compatibility mode, the following SQL succeeds and returns 0:
CREATE TABLE test2 (i INTEGER NOT NULL);
INSERT INTO test2 VALUES (NULL);
SELECT * FROM test2;
在默认模式下,它按预期失败. MySQL 5.5/InnoDB也会失败. "MySQL兼容性"实际上意味着"MyISAM兼容性"吗?
It fails as expected in the default mode. It also fails with MySQL 5.5 / InnoDB. Does "MySQL compatibility" actually mean "MyISAM compatibility"?
推荐答案
此问题是由于MySQL
处理Null值和h2
的原因所致.基本上MySQL
会将接收到的空值转换为empty string/0
,其中与h2
一样,这三种类型之间有明显的区别.
This problem is due to both the way MySQL
handles Null values and h2
. Basically MySQL
converts nulls received to empty string/0
where as in h2
there is clear distinction among all three types.
因此,他们(h2
)已决定在MYSQL
模式下将空值转换为0/空字符串/当前时间戳.但是由于as per core h2 0/空字符串不为null,因此它将插入数据.
So, they(h2
) have decided to convert the nulls to 0/empty string/current time stamp in MYSQL
mode. But since the as per core h2 0/empty string are not nulls, so it inserts the data.
使用org.h2.engine.Mode并修改与此相关的变量:
Use org.h2.engine.Mode and modify the variable related to this:
Mode mode = Mode.getInstance("MYSQL");
mode.convertInsertNullToZero = false;
This has worked for me. Thanks to this link.
这篇关于h2在MySQL兼容模式下不强制执行NOT NULL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!