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 add 0 to that column. I tried to change column default value to NULL and it told Invalid default value. Is there any way to get empty row without having there the zero?

Solution 1

"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;

Solution 2

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)