WHERE enum_col = TRUE
will match rows whose enum_col has the *first* value defined in the ENUM list.
Also, every ENUM has the special error value '' in position 0. When you assign a value to enum_col which isn't in the predefined list, that's the value you get. Hence,
WHERE enum_col = FALSE
will match rows whose enum_col were assigned values not in the ENUM list.
Note that that means the ENUM column will not "only hold what I said it could hold", as erroneous input is stored as '' (0).
<http://dev.mysql.com/doc/mysql/en/ENUM.html>
The one way this will work is if you define only the value which should be true in your ENUM. That is, "Active ENUM('1')" or "Active ENUM('True')" or the like. Then setting Active = TRUE will set Active to the first (only) string, which is meant to be true, and setting Active = FALSE will set Active to the empty, error string in position 0. Then
WHERE enum_col = TRUE
and
WHERE enum_col = FALSE
will work as expected.
That said, I still think TINYINT is the way to go.
Michael
[EMAIL PROTECTED] wrote:
I would suggest that if you want to compare against FALSE that you make that one of your enumerated values. I would also make FALSE your default value and the field not nullable. That way you don't have 3 possible values to compare against in your field (null, empty string, and 1). If you need three values (like: true, false, don't know) then make 3 enum values. But, regardless of the contents of your ENUM list, I would still make an ENUM field as "NOT NULL" and would set its default value to one of its enumerated values. That way, the field should only hold what I said it could hold.
Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Scott Hamm <[EMAIL PROTECTED]> wrote on 11/10/2004 09:24:33 AM:
I'm trying to figure out how to make Active's null as FALSE and '1' as
TRUE,
in enum point of view....
Can anyone help me out here, trying to learn enum's phenomenon? I'm not
sure
I understood document quite clear -- as of yet.... :(
mysql> desc Associate;
+------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+----------------+
| ID | int(11) | | PRI | NULL |
auto_increment |
| LocationID | int(11) | YES | | NULL | | | NTLogon | varchar(8) | YES | | NULL | | | DomainID | int(11) | YES | | NULL | | | LastName | varchar(30) | YES | | NULL | | | FirstName | varchar(30) | YES | | NULL | | | Shift | int(11) | YES | | NULL | | | QADE | enum('','1') | YES | | NULL | | | DataEntry | enum('','1') | YES | | NULL | | | QAMR | enum('','1') | YES | | NULL | | | MailRoom | enum('','1') | YES | | NULL | | | QAT | enum('','1') | YES | | NULL | | | Taping | enum('','1') | YES | | NULL | | | QAF | enum('','1') | YES | | NULL | | | Filming | enum('','1') | YES | | NULL | | | CustomerContact | enum('','1') | YES | | NULL | | | Trainee | enum('','1') | YES | | NULL | | | Active | enum('','1') | YES | | NULL | | | Creator | varchar(8) | YES | | NULL | | | NewAssociateDate | date | YES | | NULL | |
+------------------+--------------+------+-----+---------+----------------+
20 rows in set (0.00 sec)
mysql> select count(*) from Associate where Active=FALSE; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.01 sec)
mysql> select count(*) from Associate where Active=TRUE; +----------+ | count(*) | +----------+ | 2611 | +----------+ 1 row in set (0.01 sec)
mysql> select count(*) from Associate; +----------+ | count(*) | +----------+ | 3947 | +----------+ 1 row in set (0.00 sec)
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
