Mysql int field with default null is set to 0

Question

I have a problem with Mysql table. First of all my server version 5.6.17 and I use PHPmyadmin 4.2.0 to manage it on PHP version 5.5.12 and the database client that PHPmyadmin use: Database client version: libmysql - mysqlnd 5.0.11-dev - 20120503 - $Id: bf9ad53b11c9a57efdb1057292d73b928b8c5c77 $ and the PHP extension used is in phpmyadmin mysqli

The problem is when changing or setting a numerical field to be int or float using default value null and it accept null value it set it as 0 for fields without data supplied to it.

I use the following SQL:

ALTER TABLE `country` CHANGE `area` `area` INT(11) NULL DEFAULT NULL;

I expect to see NULL in the fields without data not to see 0 because this make some problems in the application when it check for NULL values but it find it 0. I tried to look for any announcement from MySQL about any bugs related for the version regarded but I couldn't find any thing.

Existing 0 values will not be changed to null. Only future values will be null — juergen d
– juergen d, Commented Jan 14, 2015 at 14:46
I'm unclear on the last part of your description. Are you seeing problems with existing data in the table? If you are, could you give us some example existing data, and tell us what it looks like after the change? (Also, you may want to use MODIFY rather than CHANGE if you're not actually renaming the column) — Matt Gibson
– Matt Gibson, Commented Jan 14, 2015 at 14:50
@juergend Oh!, I found it, it seems that my application, in some way previously, changed the values of the field and it applied it to all rows. Thank you. For testing, I created new field and, already, as you said, it has been set to NULL by default. I used the answer below to fix the table. — SaidbakR
– SaidbakR, Commented Jan 14, 2015 at 14:57

Vasil Nikolov · Accepted Answer · 2015-01-14 14:51:43Z

1

This is not a bug, your old values is 0 (valid integer). Feature values will be NULL. If 0 is normal value for your application, you have a problem. If 0 is not expected value for your application, simmply update your 0 values to NULL.

UPDATE country SET `area` = NULL WHERE `area` = 0

answered Jan 14, 2015 at 14:51

Vasil Nikolov

1,12010 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

Mysql int field with default null is set to 0

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