Insert NULL value into INT column

Question

How can I insert NULL value into INT column with MySQL? Is it possible?

score 52 · Accepted Answer · answered Mar 17 '11 at 11:45

52

If the column has the NOT NULL constraint then it won't be possible; but otherwise this is fine:

INSERT INTO MyTable(MyIntColumn) VALUES(NULL);

answered Mar 17 '11 at 11:45

sheikhjabootie

score 16 · Answer 2 · edited Jan 11 '16 at 06:09

16

2 ways to do it

insert tbl (other, col1, intcol) values ('abc', 123, NULL)

or just omit it from the column list

insert tbl (other, col1) values ('abc', 123)

edited Jan 11 '16 at 06:09

answered Mar 17 '11 at 11:52

RichardTheKiwi

score 4 · Answer 3 · answered Mar 17 '11 at 11:49

4

Does the column allow null?

Seems to work. Just tested with phpMyAdmin, the column is of type int that allows nulls:

INSERT INTO `database`.`table` (`column`) VALUES (NULL);

answered Mar 17 '11 at 11:49

Billy

score 2 · Answer 4 · answered Mar 17 '11 at 11:45

2

If column is not NOT NULL (nullable).

You just put NULL instead of value in INSERT statement.

answered Mar 17 '11 at 11:45

Andrey

score 0 · Answer 5 · answered Aug 05 '16 at 08:51

0

Just use the insert query and put the NULL keyword without quotes. That will work-

INSERT INTO `myDatabase`.`myTable` (`myColumn`) VALUES (NULL);

answered Aug 05 '16 at 08:51

Piyush Dangre

score -3 · Answer 6 · edited Sep 13 '18 at 05:37

-3

The optimal solution for this is provide it as '0' and while using string use it as 'null' when using integer.

ex:

INSERT INTO table_name(column_name) VALUES(NULL);

edited Sep 13 '18 at 05:37

A J

answered Feb 21 '14 at 09:03

vineeth

1

0 and null has very different meanings. Null could mean we do not have the data. 0 means we know it is zero. Or lets imagine a referecence to user_id. 0 will point to record with id 0 while null means there is no user. – borjab Feb 21 '14 at 09:27

6 Answers6