Null value for integer

[zenon84]

Hello.

I create a database in MySql and i set an integer to default null.

example:
create table members
(id int(4) NOT NULL AUTO_INCREMENT,
privNum int(16) default null,
password varchar(32) NOT NULL,
primary key (id));

Then, i import a csv (excel file) but with no value for privNum, but when i do a select, it shows me that privNum has value 0 but i want it to be null.Is there a way to fix this?

Thank you very much and sorry for my but english.

[eeek]

Does the CSV file have nulls where they should be?

[zenon84]

Yes, there are some empty spaces with no value.

[rocknbil]

Welcome aboard zenon84, I couldn't find specific documentation, but I think an integer field type is always going the have zero for a "null" value because, well, it's an integer field. If you absolutely require it to be null, you may have to change it to a varchar type.

If it's more important it remain an integer type (faster, it will always be numeric) A more workable solution is to make changes in your programming.

Example: you want to know if a certain action has been done based on the value of this integer field, so you are expecting it to be null if this action has not been done, but it's zero. So add another field as a boolean/tinyint field.

user_id action_done
0 0

If the action has been performed, action_done gets set as a 1. Then it becomes irrelevant that "user_id" (or whatever) is a zero.

[Gibble]

The database CAN handle NULL values in an integer field.

The caveats being that the field is not set to disallow nulls, and no default value is set.

[zenon84]

Hello.

Oh, thank you very much!
Especially you rocknbil! I appreciate it a lot!

That saved me a lot of time!

Have a nice day! God bless you!

[rocknbil]

Gibble is correct, the idea of inserting a NULL into an integer field is just alien to me, so I am in error. See eeek's recommendation, using your create:

insert into members values (null,null,'test');

results in

¦ id ¦ privNum ¦ password ¦ 
 1  ¦ NULL   ¦ test   ¦ 

When you split/explode the CSV, the value is a blank string, which is not the same as null, so this may be the root of your problem.

insert into members values ('','','test');

results in

¦ id ¦ privNum ¦ password ¦ 
 1  ¦ 0    ¦ test   ¦ 

because the (empty) string is interpreted as it's integer value, which is zero. Same result if you do

insert into members values ('NULL','NULL','test');

or

insert into members values ('test','test','test');

Note the oddities above between quoting and not quoting; if you wish to insert a null value, you cannot quote it or it will result in zero (which is probably why I decided "nulls don't work on integer fields," and forgot about it, years ago.)

So in your programming,

// PHP example
if (($fields[1] == '') or (is_null($fields[1])) {
$fields[1] = "NULL";
// OR, $fields[1] = NULL; may correctly pass NULL
// from the programming language of choice, adjust
// as necessary
}
else { $fields[1] = "'".$fields[1]."'"; }

This should handle your quoting/nonquoting for the final select. My preference is to just avoid nulls altogether.

[Gibble]

To clarify slightly

0 is not NULL, it is an integer with the value of zero
"" is not NULL, it is an empty string, but still a string
"NULL" is not NULL, it is a string with the value of "NULL"

NULL is the absence of any value, 0, "", and "NULL" are ALL values

[rocknbil]

Yes, but by defining a field with not null, inserting a row with no value or a non-integer value for that field will give it a value of zero (hence my comments about experimenting with setting the value of "NULL" versus NULL from PHP.)

I think this is what Shakespeare meant when he said "much ado about nothing . . . "

[eeek]

Yes, there are some empty spaces with no value.

Yes, but does MySQL consider those empty spaces to be NULLs? Postgresql uses \N for NULL in text dumps.