Closed Thread Icon

Preserved Topic: NULL MySQL values. Pages that link to <a href="https://ozoneasylum.com/backlink?for=20925" title="Pages that link to Preserved Topic: NULL MySQL values." rel="nofollow" >Preserved Topic: NULL MySQL values.\

 
Author Thread
jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 05-31-2001 21:14

I seem to recall in a past message someone making an offhanded comment about how using NULL values in a MySQL table is questionable practice. I think I understand why now. It would seem you can not use null in a where clause, err, you can, but it doesn't match with null values.

Does anyone know anything about this? Is there any way to match null values? Luckily I'm still in the coding stage of my project, and so it will be easy to fill my tables with empty strings rather than null.

-jiblet

butcher
Paranoid (IV) Inmate

From: New Jersey, USA
Insane since: Oct 2000

posted posted 05-31-2001 22:27

You can use null in a where clause, but not with regular operators (i.e., =, !=) you must use IS NULL or IS NOT NULL when trying to match null values in tables.

- Resolutions, Of All My Fruitless Searches -

linear
Paranoid (IV) Inmate

From: other places
Insane since: Mar 2001

posted posted 05-31-2001 22:38

Damn fine answer.

Are the "IS NOT NULL" and related constructs legal ANSI SQL, or MySQL extensions?

jiblet
Paranoid (IV) Inmate

From: Minneapolis, MN, USA
Insane since: May 2000

posted posted 05-31-2001 22:49

Hm, thanks, sadly that adds a lot of complexity to my PHP scripts since any one of 20 fields could be null. Luckily the empty string solution seems to work great as long as I reserve the integer 0 only for 'empty' fields.

Does someone know why the empty string is not considered null in MySQL?

-jiblet

butcher
Paranoid (IV) Inmate

From: New Jersey, USA
Insane since: Oct 2000

posted posted 06-01-2001 00:22

Because NULL does not have a definable value. The only way to describe null, is that it has no value, which is why you can't use mathematical operators to test for null, you can't compare something that has no legal value.

On the other hand, an empty string has a value, it's "", and can be compared to a string with something in between the quotes. Hope my explination makes sense for you.

Linear

I don't know if it's ANSI legal or not, the extent of my experience with a database is solely MySQL.

Sorry

- Resolutions, Of All My Fruitless Searches -

hyperbole
Paranoid (IV) Inmate

From: Madison, Indiana, USA
Insane since: Aug 2000

posted posted 06-01-2001 17:50

I have used test for "Is NULL" and "Is Not NULL" on MySQL, Sybase, and Access. I don't know if it's ANSII standard, but a lot of databases seem to use it.

« BackwardsOnwards »

Show Forum Drop Down Menu