MySQL integer default value 0

Question

I have 4 integer columns in my table. They are not required to be filled. So some of them may be filled, some not.

When they are not filled, MySQL adds 0 to that column. I tried to change the column default value to NULL and it told "Invalid default value".

Is there any way to get an empty row without having there zero?

Please show `CREATE TABLE` statement for your table and how you insert new rows. — peterm, Nov 24 '13 at 08:40
possible duplicate of [How do I modify a MySQL column to allow NULL?](http://stackoverflow.com/questions/212939/how-do-i-modify-a-mysql-column-to-allow-null) — vascowhite, Nov 24 '13 at 10:41

score 5 · Answer 1 · answered Nov 24 '13 at 08:43

"Is there any way to get empty row without having there the zero?"

To have NULL in the column by default use the following syntax in create table:

`column` int(10) unsigned DEFAULT NULL,

To alter the existing column:

ALTER TABLE table_name CHANGE COLUMN `column_name` `column_name` int(10) unsigned DEFAULT NULL;

score 1 · Answer 2 · answered Nov 24 '13 at 08:42

If your columns are NULL'able then it should work just fine

mysql> CREATE TABLE Table1
    -> (id int not null auto_increment primary key,
    -> `col1` int, `col2` int, `col3` int, `col4` int);
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> INSERT INTO Table1 (`col1`, `col2`, `col3`, `col4`)
    -> VALUES (1, 1, 1, 1);
Query OK, 1 row affected (0.03 sec)

mysql> 
mysql> INSERT INTO Table1 () VALUES();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM table1;
+----+------+------+------+------+
| id | col1 | col2 | col3 | col4 |
+----+------+------+------+------+
|  1 |    1 |    1 |    1 |    1 |
|  2 | NULL | NULL | NULL | NULL |
+----+------+------+------+------+
2 rows in set (0.00 sec)

score 0 · Answer 3 · answered Nov 24 '13 at 08:36

0

In that case you need to change your datatype into varchar and add default value NULL.

ALTER TABLE <table_name> ALTER COLUMN <column_name1> <datatype1> <constraint1>

answered Nov 24 '13 at 08:36

Krish R

22,583
7
50
59

As long as the datatype is non-numerical, any form of String is fine too. – Leo Apr 10 '16 at 17:24

score 0 · Answer 4 · edited May 23 '17 at 12:25

0

You have to alter your column to allow NULL values. This question has already been answered before: How do I modify a MySQL column to allow NULL?

edited May 23 '17 at 12:25

Community

1
1

answered Nov 24 '13 at 08:42

nullop

544
2
6

That does not answer the question as it concerns a text column not an integer column – tangent Apr 06 '15 at 02:36

MySQL integer default value 0

4 Answers4

Linked