28

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.

Improve this question
10
  • What language is your backend in? PHP? Python? etc? Commented Feb 19, 2015 at 12:31
  • 3
    In most scripting languages, the POSTed data is a string. You must use the language's built-in cast functions to perform the conversion. Commented Feb 19, 2015 at 12:32
  • @AndrewDunai, it is PHP and Apache. Latest versions. Commented Feb 19, 2015 at 12:45
  • So you want to store an empty STRING in an INTEGER-type field? Commented Feb 19, 2015 at 12:48
  • 2
    You need to explicitly set NULL. Please see my answer. Commented Feb 19, 2015 at 12:51

4 Answers 4

Reset to default
40

There are 2 ways to do this.

  1. For Current Mysql Session (Temporary Solution)

First, execute a query to get the 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 the result contains STRICT_TRANS_TABLES, you have to remove that value to allow the insert query to pass a NULL value. Make sure your MySQL User has privileges to apply these changes and restart the MySQL Server after applying this.

    SET GLOBAL sql_mode = '';
  1. For Life Time of Mysql (Permanent Solution)

You have to update my.cnf file. The 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 the file so you have to log in with the root user on the server.

For more details understand what this SQL mode does.

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

Edit: Warning: Setting blank SQL Mode removes the validations. It's better to understand the purpose of each mode and set it accordingly.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I had to use sql-mode="" like TomCat said in comments, otherwise there was still an error mysqlimport: Error: 1366, Incorrect integer value: '' for column (and the documentation says TRADITIONAL is equivalent to STRICT_TRANS_TABLES). By the way, the path was /etc/mysql/my.cnf. Thanks for saying that the line should go below [mysqld], we can easily forget.
20

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.

Improve this answer

2 Comments

I had to clear SQL mode explicitly in my.ini on wamp environment to get this working. To clear the SQL mode explicitly, set it to an empty string using sql-mode=""
@TomCat Thanks, sql-mode="" below [mysqld] in my.cnf (on Linux) solved the error message I had when importing Geonames data.
5

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);
Improve this answer

4 Comments

(not related to the downvote) . . . I'm guessing the OP is using insert rather than update, but the similar logic applies.
Thanks @GordonLinoff You may be right. I've amended the answer to better suit the OP's specific situation (with INSERT).
@BenM, The JavaScript posts "" (or PHP gets "") so the Zend DB tries to INSERT INTO table (column_name) VALUES ("");
I would say that you don't always have control at the query level. I am using a library that is currently doing this error.
0

I have a solution not so fancy but that works, just before making the update, add a 0 to the integer variable... i mean... like... $i = 0 + $i. This will just affect the empty variables converting them to a simple 0.

Hope it helps!

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.