Can MySQL automatically convert empty strings to NULL?

Question

There is an excellent SO on MySQL empty strings versus NULL here, MySQL, better to insert NULL or empty string?, however it doesn't take in to account 'uniformity' - i.e. if you want to have just one choice in your tables (i.e. empty string OR NULL), which should it be?

My question is, can I get MySQL to automatically store empty strings as NULLs?

After reading the previous SO I am generally inclined to store NULL but the problem is that I have a lot of PHP forms with optional fields, and (when left blank) these return empty strings.

score 45 · Accepted Answer · answered Nov 15 '12 at 00:10

45

You can enclose your strings with NULLIF()

You use it like this:

NULLIF('test','') --> returns 'test'
NULLIF(''    ,'') --> returns NULL

answered Nov 15 '12 at 00:10

Wouter van Nifterick

23,603
7
78
122

4

[NULLIF](http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_nullif) – Andrei Dec 01 '16 at 16:22
2

If the first and second parameters of `NULLIF(string1, string2)` match, `NULL` is returned. – Lemmings19 Oct 28 '19 at 02:32

score 2 · Answer 2 · answered Nov 15 '12 at 00:13

2

Alternatively to NULLIF you could set the default to NULL and just not pass empty fields along.

answered Nov 15 '12 at 00:13

m02ph3u5

3,022
7
38
51

Can MySQL automatically convert empty strings to NULL?

2 Answers2