Mysql Change Int default value zero to null

Question

Hi i'm working laravel 5 with mysql. I did store zipcode into my database it working well but when the field is empty it stored zero(0) but i want to store empty value in database.

I'm also changed mysql using alter table like below ALTER TABLE insurances CHANGE COLUMN zipcode4 zipcode4 int(4) unsigned DEFAULT NULL; but it wont work, have any idea ?

Thanks in advance

Answer 1

Well, zipcode is something which has fixed characters, and wont need any arithmetic operations so,

1. You can change the datatype to CHAR or VARCHAR

2. Forcefully insert NULL value while inserting query !

Try This,

3.

 ALTER TABLE insurances CHANGE zipcode4 zipcode4 int(4) DEFAULT NULL;

• user2172424: I tried this alter query and insert null value when insert but it also store zero only..
• Hytool: plz run these queries and show the proper output in Question area ; SELECT @@version; SHOW CREATE TABLE insurances

Answer 2

the prevous default were 0, so after changing the default they still keep it, to update it after having changed the default, do something like:

UPDATE mytable set zipcode4=NULL WHERE zipcode4=0

the new values will correctly put NULL if not value is provided

Answer 3

100 % accuracy this code

UPDATE mytable SET zipcode4 = NULL WHERE zipcode4 = 0
UPDATE mytable SET Myfield = NULL WHERE Myfield = 0

• miken32: Why would someone just copy another answer from 18 months previous? What is the point?