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.

Thomas K. · Accepted Answer · 2023-09-04 08:37:40Z

47

You can enclose your strings with NULLIF().

You use it like this:

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

edited Sep 4, 2023 at 8:37

Thomas K.

6,86035 silver badges42 bronze badges

answered Nov 15, 2012 at 0:10

Wouter van Nifterick

24.2k7 gold badges82 silver badges123 bronze badges

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

2 Comments

Andrei Over a year ago

NULLIF

Lemmings19 Over a year ago

If the first and second parameters of NULLIF(string1, string2) match, NULL is returned.

m02ph3u5 · Accepted Answer · 2012-11-15 00:13:52Z

2

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

answered Nov 15, 2012 at 0:13

m02ph3u5

3,1617 gold badges43 silver badges59 bronze badges

Collectives™ on Stack Overflow

Can MySQL automatically convert empty strings to NULL?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related