MySQL: NULL in field edit

LotsOfZeros

LotsOfZeros

^^^ Bi-Winning ^^^
Feb 9, 2008
4,649
118
0
www.makemoniesonline.com
Just wondering about this:

jkjeb.png


Also, I've noticed despite this setup, the field is sometimes empty anyway which throws my queries off when searching for NULL values here.
 


Checking the "Null" box means that the value can be null, and it does not require a default value. Further, if you specify the default value as "NULL", that will BE the default value. If that is the case, it should not be inserting empty values. I have noticed that there seems to be a difference in the way various PHP engines (mysql_, mysqli_, PDO, etc.) handle null values. Sometimes it just returns empty, sometimes it returns the PHP "null" equivalent. As a tip, using PDO (Why you Should be using PHP’s PDO for Database Access | Nettuts+) makes all this much more standardized and simple. As another tip, your screenshot is just phpMyAdmin and I would not trust it as reliable insight into the MySQL engine.
 
  • Like
Reactions: LotsOfZeros
chatmasta said:
Checking the "Null" box means that the value can be null, and it does not require a default value. Further, if you specify the default value as "NULL", that will BE the default value. If that is the case, it should not be inserting empty values. I have noticed that there seems to be a difference in the way various PHP engines (mysql_, mysqli_, PDO, etc.) handle null values. Sometimes it just returns empty, sometimes it returns the PHP "null" equivalent. As a tip, using PDO (Why you Should be using PHP’s PDO for Database Access | Nettuts+) makes all this much more standardized and simple. As another tip, your screenshot is just phpMyAdmin and I would not trust it as reliable insight into the MySQL engine.
Click to expand...

Thanks for the detail. I believe it to be true since I get undesirable results when running WHERE clause searching for NULL. What would you suggest as an alternative to phpMyAdmin? I have used HeidiSQL and like it a lot as well.
 
And if something isn't null -
Code: 
SELECT ... FROM table WHERE col IS NOT NULL

That whole empty value issue is one annoyance you'll have to deal with in every programming language. All of them handle it differently, even in PHP you have empty(),which doesn't always return what you expect, and sometimes you need to actually check the length other ways like with (count(my_array) == 0) and the like.

The one that drives me completely insane is Javascript - sometimes you have to use weird functions like isNaN() to check if something isn't a number to figure out if it's empty, because by the time it gets there it's probably gone through 30 goddamn type conversions.
 
Also, ANYTHING that is evaluated AGAINST null BECOMES null. So, for example:

set @s = concat(nullfield,' with some other text');
select @s; // returns null

if (@null_variable = 3) // this whole expression becomes null, and cannot ever return "true"

Also, i'm a HUGE MySQL Query Browser advocate (now MySQL Workbench, bleh).
 
You must log in or register to reply here.
Top Bottom