How to allow empty string for integer in MySQL?

Question

I have integer fields in a table. The POSTs are sent by a complicated JavaScript. They send empty strings like "" but as you guessed MySQL doesn't allow empty strings in integer fields. Are there any options to allow empty strings? Like if it takes an empty string it will save it as NULL.

Answer 1

There are 2 ways to do this.

1. For Current Mysql Session (Temporary Solution)

First execute query to get current SQL mode of your mysql server.

    mysql> SELECT @@sql_mode;
    +----------------------------------------------------------------+
    | @@sql_mode                                                     |
    +----------------------------------------------------------------+
    |STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION  |
    +----------------------------------------------------------------+
    1 row in set (0.00 sec)

If result contains STRICT_TRANS_TABLES, you have to remove that value to allow insert query to pass NULL value. Make sure your mysql User have privileges to apply this changes and restart Mysql Server after applying this.

    SET GLOBAL sql_mode = '';

2. For Life Time of Mysql (Permanent Solution)

You have to update my.cnf file. Location of that file is : \etc\my.cnf or \etc\mysql\mysql.cnf

There will be some default parameters set under [mysqld] like

[mysqld]
innodb_file_per_table=1
default-storage-engine=MyISAM
performance-schema=0
max_allowed_packet=268435456
open_files_limit=10000

Just add one line under that

sql-mode=""

Make sure to restart Mysql Server after changing this file. Normally root user will be the owner of file so you have to login with root user on server.

For more details to understand what this SQL mode do.

STRICT_TRANS_TABLES

Enable strict SQL mode for transactional storage engines, and when possible for non-transactional storage engines. For details, see Strict SQL Mode.

Refer : http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_strict_trans_tables

NO_AUTO_CREATE_USER

Prevent the GRANT statement from automatically creating new user accounts if it would otherwise do so, unless authentication information is specified. The statement must specify a nonempty password using IDENTIFIED BY or an authentication plugin using IDENTIFIED WITH.

Refer: http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_auto_create_user

NO_ENGINE_SUBSTITUTION

Control automatic substitution of the default storage engine when a statement such as CREATE TABLE or ALTER TABLE specifies a storage engine that is disabled or not compiled in.

Refer : http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_engine_substitution

Answer 2

Removing sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" from my.ini has solved the issue.

Edit: Removing the line above works but it is a bad idea. It allows to have things like 0000-00-00 or empty string dates. Better keep the line above and don't insert empty sting into an integer field, instead convert empty string into NULL and then insert that NULL into integer field.

Answer 3

Assuming that the column allows for NULL values, you must explicitly tell MySQL to use a value of NULL, rather than passing an empty string (which is cast to 0):

INSERT INTO table (column_name) VALUES (NULL);