>>>>> "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).
Hi!
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"
Regards,
Monty
