[SOLVED] Sql insert into Tidb int field with '' Got Error Data Truncated

Sql insert into Tidb int field with '' Got Error Data Truncated

I create a table in TiDB, with a int field, while inserting use value '' to this field, I got an error 'Data Truncated'.

My code like this:

CREATE TABLE test(
i1 INT(11),
s1 VARCHAR(16)
)

INSERT INTO test(i1,s1) VALUES ('11','aa');   //ok

INSERT INTO test(i1,s1) VALUES ('','aa');    //Error 'Data Truncated'

INSERT INTO test(i1,s1) VALUES (NULL,'aa') //ok

While in mysql 5.7,the following sql returns ok

INSERT INTO test(i1,s1) VALUES ('','aa');

My TiDB version is :

Release Version: v1.0.6-1-g17c1319

Git Commit Hash: 17c13192136c1f0bf26db6dec994b9f1b43c90f0

Git Branch: release-1.0

UTC Build Time: 2018-01-09 09:07:08

https://github.com/pingcap/tidb/issues/6317

Solution

In the case you present, TiDB behaves the same as MySQL. This error is caused by the strict SQL mode. As a workaround, you can:

set @@sql_mode='';
INSERT INTO test(i1,s1) VALUES ('','aa');