how to define null as integer value on mysql server?

Question

I need to allow saving null as integer value on mysql server, how can I do this?

Answer 1

Set the field to not null and set its default to zero:

CREATE TABLE `t` (
    `foo` BIGINT NOT NULL DEFAULT 0
);

Value of foo will be zero if you send it NULL.

Answer 2

I found out how: I hided the line below then restarted the sql server and it worked.

# Set the SQL mode to strict sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Note this line is found in my.ini file.

Answer 3

Assuming your question is about the "Incorrect integer value" error message...

mysql> CREATE TABLE `foo` (
    ->  `foo_id` INT(10) NOT NULL AUTO_INCREMENT,
    ->  `foo_size` INT(10) NOT NULL DEFAULT '0',
    ->  PRIMARY KEY (`foo_id`)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO foo (foo_size) VALUES ('');
ERROR 1366 (HY000): Incorrect integer value: '' for column 'foo_size' at row 1

... that means that your server is running in strict mode (which is actually good). You can disable it:

mysql> SET @@session.sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO foo (foo_size) VALUES ('');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'foo_size' at row 1 |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM foo;
+--------+----------+
| foo_id | foo_size |
+--------+----------+
|      1 |        0 |
+--------+----------+
1 row in set (0.00 sec)

... and your error will be downgraded to warning.