I think you started with good advice then took a strange turn.
Chris Blackwell wrote:
If you want an enum to have the possible values of NULL or 1
alter table `Associate` modify `Active` enum('1');
from the mysql manual http://dev.mysql.com/doc/mysql/en/ENUM.html
If an ENUM column is declared to allow NULL, the NULL value is a legal
value for the column, and the default value is NULL. If an ENUM column
is declared NOT NULL, its default value is the first element of the list
of allowed values.
If you simply want a column to hold boolean data I prefer to use "tiyint(1) unsigned not null" In my application I then treat 0 as False and 1 (or any other number) as True
This is probably the best way to go.
I do not believe you can make mysql evaluate NULL to be False, mysql will evaluate 1 = True and 0 = False, TRUE And FALSE are simply aliases for 1 and 0
Correct. NULL is "no value", while FALSE (0) and TRUE (not 0) are values. Rows with NULL for Active will not match comparisons against TRUE or FALSE (or any other value).
you have two options
1) You could simply update your table
alter table `Associate` modify `Active` enum('0','1'); (or 1 then 0 if you want true to be the default)
then
update `Associate` set `Active` = 0 where isNull(Active);
2) modify your queries select count(*) from Associate where Active!=1 OR isNull(Active); select count(*) from Associate where Active=1;
This makes no sense to me. More to the point, it won't work. ENUMs hold strings, not numbers. Each string, however, is stored as an integer in your rows, starting with 1. That's important, because when used in numeric context you get the position number of the string. In other words, with Active ENUM('0', '1'), the string '0' is 1 in numeric context and the string '1' is 2 in numeric context. In other words,
WHERE Active=1
will return the rows with Active = '0', the opposite of what you intended.
The main advantage of using ENUM for boolean values is for human readability. That is, you define something like
Active ENUM('T', 'F')or
Active ENUM('yes', 'no')so that when viewing the data you see the values of Active as text. The disadvantage of this method is that you must do a string comparison in your WHERE clause to select rows based on this value (Active = 'T', or Active = 'yes'). If you are going to use '0' and '1' in your ENUM, that advantage disappears, and you might as well use a TINYINT as you originally suggested, particularly as it makes your queries simpler (and slightly more efficient).
I recommend you define Active as a TINYINT, then store 1 for true and 0 for false. Then your queries become
SELECT COUNT(*) FROM Associate WHERE Active;
to find all rows with Active set to true (1), and
SELECT COUNT(*) FROM Associate WHERE NOT Active;
to find all rows with Active set to false (0).
Chris
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
