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屋！