mysql if string is empty use default value

Question

I have the following query, all the '' come up as empty strings

INSERT INTO `locations` ( `house`,`house_number`,`road`,`city`,`state`,`country`,`country_code`,`supermarket`,`county`,`place_of_worship`,`city_district`,`suburb`,`neighbourhood`,`pedestrian`,`postcode`) 
VALUES ('','','','Gemeinde Wien','Vienna','Austria','at','','W','Saint Stephen\'s cathedral','Innere Stadt','Innere Stadt','Textilviertel','Stephansplatz','1010');

table structure

All fields have a default value of null.

problem

So the question is, how can I get it so that empty strings keep their default value?

You will need to either insert a value of NULL or modify your query to not insert anything for those columns if there is no value — John Conde
– John Conde, Commented May 21, 2014 at 17:52
Bummer, I had hoped mysql could take care of it :) Lazy php right here! — Jamie Hutber
– Jamie Hutber, Commented May 21, 2014 at 18:00

Sharikov Vladislav · Accepted Answer · 2014-05-21 18:17:36Z

3

Instead of inserting '' you can just use null

INSERT INTO`locations`(`house`,`house_number`,`road`,`city`,`state`,`country`,`country_code`,`supermarket`,`county`,`place_of_worship`,`city_district`,`suburb`,`neighbourhood`,`pedestrian`,`postcode`) 
VALUES (null,null,null,'Gemeinde Wien','Vienna','Austria','at','','W','Saint Stephen\'s cathedral','Innere Stadt','Innere Stadt','Textilviertel','Stephansplatz','1010');

or do not insert anything like this

INSERT INTO `locations` (`city`,`state`,`country`,`country_code`,`supermarket`,`county`,`place_of_worship`,`city_district`,`suburb`,`neighbourhood`,`pedestrian`,`postcode`) 
VALUES ('Gemeinde Wien','Vienna','Austria','at','','W','Saint Stephen\'s cathedral','Innere Stadt','Innere Stadt','Textilviertel','Stephansplatz','1010');

edited May 21, 2014 at 18:17

Sharikov Vladislav

7,2999 gold badges53 silver badges92 bronze badges

answered May 21, 2014 at 17:57

ElenA

786 bronze badges

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

Collectives™ on Stack Overflow

mysql if string is empty use default value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related