List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 16 1999 12:56pm
Subject:Re: BIT fields?
View as plain text  
>>>>> "Bob" == Bob Kline <bkline@stripped> writes:

Bob> On 28 Oct 1999, Robin Bowes wrote:
>> Hi,
>> Is there any chance of MySQL supporting BIT fields in the near future?
>> For example, I use VisioModeller to create my data structures and when I
>> define a field as a Logical field (ie True or False) it creates the
>> following field definition:
>> IsSubstantive BIT constraint Hearing_IsSubstantive not null check
>> (IsSubstantive in ( 1, 0)),
>> I currently change this definition manually to:
>> IsSubstantive enum('N','Y') null,
>> ...but it would be nice to not have to!

Bob> Not only that, but your workaround has three possible values (four,
Bob> since you are allowing NULL): 'N', 'Y', NULL, and ' ' (blank, which is
Bob> stored when any value other than the first three is inserted).

Bob> Unfortunately, the manual has some gaps in its coverage of this type.
Bob> The change logs note that in 3.21.23 BIT was added to the list of
Bob> reserved words, and that back in 3.21.12 BIT was added as a synonym for
Bob> CHAR(1).  The grammar reference, however makes no mention of this.

Bob> The TODO list has "Optimize BIT type to take 1 bit (now BIT takes 1
Bob> char)."

Bob> As it turns out, BIT (as currently implemented) is even worse than your
Bob> workaround (though if you can live with the work of performing the
Bob> domain validation in your own application code, this would relieve you
Bob> of the need to change the type from the standard BIT to the non-standard
Bob> ENUM -- you'd still need to strip off the check constraint):

mysql> CREATE TABLE btest(b BIT(3));
Bob> ERROR 1064: You have an error in your SQL syntax near '(3))' at line 1
mysql> CREATE TABLE btest(b BIT);
Bob> Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO btest VALUES(100);
Bob> Query OK, 1 row affected (0.00 sec)

mysql> SELECT b FROM btest;
Bob> +------+
Bob> | b    |
Bob> +------+
Bob> |  100 |
Bob> +------+
Bob> 1 row in set (0.01 sec)

Bob> As you can see, the standard syntax for BIT(n) isn't implemented at all,
Bob> and BIT doesn't restrict the allowed values to 0 and 1 (not a surprise
Bob> once you read the note in the change history, though it seems clear
Bob> that even that note was incorrect -- a synonym for CHAR(1) would not
Bob> accept and store the number 100).

Bob> If there are no plans to fix the support for BIT (which would be
Bob> disappointing, but the logical conclusion from reading the TODO list),
Bob> then I'll write up a patch for the grammar reference section of the
Bob> manual.  I won't bother if a correct implementation for the type is on
Bob> its way (which would of course be preferable).


You are right;   I shall update the manual to say that BIT is
currently a synonym for TINY.

I have also fixed for 3.23.6 that it allows you to specify BIT(X), but 
currently it only works for 1 <= X <= 8.

By the way, what should BIT(5) return?  A string in '0x10101' format?
Should then BIT(1) return  "0x1" / "0x0" or 0 / 1 or "0" / "1"

BIT fields?Robin Bowes28 Oct
  • Re: BIT fields?Bob Kline28 Oct
    • Re: BIT fields?Michael Widenius18 Nov
      • Re: BIT fields?Bob Kline18 Nov