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

score 3 · Answer 1 · answered Aug 22 '15 at 07:52

3

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

You can change the datatype to CHAR or VARCHAR
Forcefully insert NULL value while inserting query !

Try This,

3.

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

answered Aug 22 '15 at 07:52

Hytool

1,358
1
7
22

I tried this alter query and insert null value when insert but it also store zero only.. – user2172424 Aug 22 '15 at 09:31
plz run these queries and show the proper output in Question area ; SELECT @@version; SHOW CREATE TABLE insurances; – Hytool Aug 24 '15 at 08:46

score 0 · Answer 2 · answered Mar 31 '17 at 08:37

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

score -2 · Answer 3 · edited Sep 06 '18 at 01:01

-2

100 % accuracy this code

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

edited Sep 06 '18 at 01:01

ilkerkaran

4,214
3
27
42

answered Sep 05 '18 at 20:07

Muhammad Saqib Patel

1
1

Why would someone just copy another answer from 18 months previous? What is the point? – miken32 Nov 29 '19 at 16:27

Mysql Change Int default value zero to null

3 Answers3