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?
"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;
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)
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>
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?
CREATE TABLEstatement for your table and how you insert new rows.