h2 mysql sql兼容,h2在MySQL兼容模式下不强制执行NOT NULL

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;

It fails as expected in the default mode. It also fails with MySQL 5.5 / InnoDB. Does "MySQL compatibility" actually mean "MyISAM compatibility"?

解决方案

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.

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.

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.