Professional Web Applications Themes

Does MySQL support a "true" boolean column? - MySQL

I've lost too much hair over this one. I'm trying to create a table that has a column that will ONLY accept TRUE or FALSE (0 or 1). If I set the type to BOOL, then it will accept any value that will fit into a tinyint. If I set the type to ENUM and only allow 0 or 1, MySQL insists on allowing 3 values - NULL, 0, & 1, even if null is not allowed for the column. Any help would be greatly appreciated. Lee...

  1. #1

    Default Does MySQL support a "true" boolean column?

    I've lost too much hair over this one.

    I'm trying to create a table that has a column that will ONLY accept
    TRUE or FALSE (0 or 1).

    If I set the type to BOOL, then it will accept any value that will fit
    into a tinyint.

    If I set the type to ENUM and only allow 0 or 1, MySQL insists on
    allowing 3 values - NULL, 0, & 1, even if null is not allowed for the
    column.

    Any help would be greatly appreciated.

    Lee
    Lee Guest

  2. #2

    Default Re: Does MySQL support a "true" boolean column?

    · Lee Peedin <rr.com>:
     

    Hm. You wan't a column, which can only take two values. With an
    ENUM which allows 0 and 1, you've got *three* possible values:
    0, 1 and '' (empty string) (BTW: NULL can be excluded by defining
    the column with NOT NULL, which contradicts what you wrote).

    To get this, I've got the following table:

    mysql> describe booltest;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | b | enum('true') | NO | | | |
    +-------+--------------+------+-----+---------+-------+
    1 row in set (0.00 sec)

    In "b", I can only insert "true" or "" which gives
    two possible values.

    OTOH: What's bad about a plain tinyint field? In your program,
    you could define, that 0 is false and anything which is not 0
    is true. Then you've got your two possible values as well.

    Reg. your NULL problem:

    mysql> insert into booltest set b = not null;
    ERROR 1048 (23000): Column 'b' cannot be null

    Alexander Skwar
    --
    BOFH Excuse #309:

    firewall needs cooling

    Alexander Guest

  3. #3

    Default Re: Does MySQL support a "true" boolean column?

    > I'm trying to create a table that has a column that will ONLY accept 

    mysql> create table t(a enum('0','1') not null);
    Query OK, 0 rows affected (0.03 sec)

    mysql> insert into t values(3);
    Query OK, 1 row affected, 1 warning (0.00 sec)

    mysql> select * from t;
    +---+
    | a |
    +---+
    | |
    +---+
    1 row in set (0.00 sec)

    mysql> delete from t;
    Query OK, 1 row affected (0.00 sec)

    mysql> set sql_mode = 'STRICT_ALL_TABLES';
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into t values(3);
    ERROR 1265 (01000): Data truncated for column 'a' at row 1
    mysql> select * from t;
    Empty set (0.01 sec)



    Regards
    Dimitre
    Radoulov, Guest

  4. #4

    Default Re: Does MySQL support a "true" boolean column?

    · Radoulov, Dimitre <com>:
     [/ref]
     

    So your "a" column can take 3 values: 0, 1 and ''. OP wanted
    to get only 2 values.

    Alexander Skwar
    --
    Do not overtax your powers.

    Alexander Guest

  5. #5

    Default Re: Does MySQL support a "true" boolean column?

    >> mysql> insert into t values(3); 
    >
    > So your "a" column can take 3 values: 0, 1 and ''. OP wanted
    > to get only 2 values.[/ref]

    It doesn't (after setting sql_mode to strict_all_tables).
    However, it accepts the value 2.

    So you can use views (see http://arjen-lentz.livejournal.com/49881.html).

    mysql> create table t(a tinyint not null);
    Query OK, 0 rows affected (0.03 sec)

    mysql> set sql_mode=strict_all_tables;
    Query OK, 0 rows affected (0.00 sec)

    mysql> create or replace view t_view as select * from t where a between 0
    and 1 WITH CASCADED CHECK OPTION;
    Query OK, 0 rows affected (0.01 sec)

    mysql> insert into t_view values (0);
    Query OK, 1 row affected (0.01 sec)

    mysql> insert into t_view values (1);
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into t_view values (2);
    ERROR 1369 (HY000): CHECK OPTION failed 'test.t_view'
    mysql> insert into t_view values (NULL);
    ERROR 1048 (23000): Column 'a' cannot be null
    mysql> insert into t_view values ('');
    ERROR 1366 (HY000): Incorrect integer value: '' for column 'a' at row 1



    Regards
    Dimitre


    Radoulov, Guest

Similar Threads

  1. Replies: 0
    Last Post: December 4th, 03:12 PM
  2. Replies: 0
    Last Post: October 19th, 07:36 AM
  3. #39195 [NEW]: if (0=="any sting") echo "it's return true";
    By waynewn at citiz dot net in forum PHP Bugs
    Replies: 0
    Last Post: October 19th, 07:33 AM
  4. Acrobat Form Submit error: changes VALUE="true" to "0"
    By Douglas_Maus@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 1
    Last Post: August 12th, 05:25 PM
  5. #12029 [Com]: "--with-apxs" can cause the error "MySQL: Unable to save result set in"
    By sietzepost at streetwisedd dot nl in forum PHP Development
    Replies: 1
    Last Post: October 3rd, 09:21 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139