NULL vs. empty string in CHAR(0) field

In the New Riders MySQL book (which is awesome, by the way) page 99, there's a reference to using a CHAR(0) field as a super-efficient ON/OFF flag (it only uses one bit).

CHAR(0) can have two values, either NULL or the empty string. Using IS NULL, I can detect my column RType (which I set to CHAR(0)) as being NULL or empty string - this does work, as I've checked it with known NULL and non-null values (I've also tried IFNULL and ISNULL() as well).

However, it seems I can't set RType to anything other than NULL

In this example, I create a record where Rtype is NULL.

Then I try to set all the RType values to an empty string ('') to get it to be somethign other than NULL (I even tried setting RType to '1').


Finally, I do a search for all records where RType is NULL. I'm thinking that this should return NO records, but I get them all back.

Is there another way to set a column's value(I'm using MySQL 3.23.7) ?

_____________

<this is in PHP>

$ query = " INSERT INTO R2000 (Qno, RType) VALUES (333,NULL) ";

$result= mysql_query($query);

$query = "UPDATE R2000 SET RType='' ";

$result= mysql_query($query);

$query1 = " SELECT * FROM R2000 WHERE RType IS NULL" ;

etc ...etc.... (I omitted the error checking stuff from this posting for brevity's sake).

Thanks !

You are not defining a WHERE clause in your update. I believe that is required in update statements.

TAICBW

RTFM, Rod. Yes, I was wrong, you don't need a where clause.

I'm assuming it WILL work in the opposite direction? i.e. that you can set it to an empty string on insert and then change to null?

No, actually that doesn't work either - it seems like I can't actually alter the value of a CHAR(0) field with a PHP MySQL query, which just doesn't sound right !