2

Why inserting '' on a column with default integer value on MYSQL/WINDOWS produces ERROR 1366 (HY000): Incorrect integer value: '' for column 'id' at row 1

The query is:

insert into test2 (id,test) values('','');

If I use this query on MYSQL/LINUX, it runs just fine:

mysql>  insert into test2 (id,test) values('','');
Query OK, 1 row affected, 2 warnings (0.01 sec)

mysql>

id is autoincrement test column is INT and has default value 0

Thanks.

SOLUTION: on my windows machine i disable "strict" mode in my.ini file, i removed STRICT_TRANS_TABLES from sql-mode=

  • 2
    '' is not a valid number, it's a string - why would you ever want to insert a string into a number column? The difference in behavior probably comes from using a different value for the strict_mode or the no_auto_value_on_zero configuration parameter. If you do a show warnings on the Linux installation you'll probably see the explanation. In general you should not ignore the message 2 warnings. – a_horse_with_no_name Apr 23 '14 at 7:32
1

PROBLEM

You need to insert nothing ?

SOLUTION

Then, insert nothing at all ... literally !!!

One of the following will work

INSERT INTO test2 () VALUES (),(),();
INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);

SAMPLE DATA

use test
DROP TABLE IF EXISTS test2;
CREATE TABLE test2
(id int not null auto_increment primary key,
test int not null default 0);
SHOW CREATE TABLE test2\G
SELECT * FROM test2;

SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test2;
Query OK, 0 rows affected (0.11 sec)

mysql> CREATE TABLE test2
    -> (id int not null auto_increment primary key,
    -> test int default 0);
Query OK, 0 rows affected (0.34 sec)

mysql> SHOW CREATE TABLE test2\G
*************************** 1. row ***************************
       Table: test2
Create Table: CREATE TABLE `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `test` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql>

QUERIES EXECUTED

If id and test are the only two columns in the table, the INSERT does not need a column list. If there are other columns in test2 besides id and test, name the test column and insert a NULL. Keep in mind, inserting a NULL into a column that has DEFAULT 0 will become 0.

mysql> SELECT * FROM test2;
Empty set (0.00 sec)

mysql> INSERT INTO test2 () VALUES (),(),();
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> INSERT INTO test2 (test) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected, 3 warnings (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 3

mysql> INSERT INTO test2 (id) VALUES (NULL),(NULL),(NULL);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test2;
+----+------+
| id | test |
+----+------+
|  1 |    0 |
|  2 |    0 |
|  3 |    0 |
|  4 |    0 |
|  5 |    0 |
|  6 |    0 |
|  7 |    0 |
|  8 |    0 |
|  9 |    0 |
+----+------+
9 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!

CAVEAT #1 : I did this in Windows 8.1 from the command line

mysql> select * from information_schema.global_variables
    -> where variable_name like 'version%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION_COMPILE_MACHINE | x86_64                       |
| VERSION_COMPILE_OS      | Win64                        |
| VERSION                 | 5.6.15                       |
+-------------------------+------------------------------+
4 rows in set (0.07 sec)

mysql>

CAVEAT #2 : I am using MySQL's default SQL mode. Thus, I did not need to disable anything.

-2

loading empty values by a date file will insert the data with empty strings instead of NULL. I solved my case loading the data into tekst fields and subsequently ran an update: update table <> set col1=NULL where col1='';

  • This doesn't answer the question. – Colin 't Hart May 24 at 9:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.