Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,191
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Sorry...dumb me. I had MySQL and SQL Server confused.

    SQL Server allows BIT(1) for a BOOLEAN value, and that really does mean one *BIT*, not a full byte.

    You are right, a TINYINT(1) is one *byte*, so it allows values from 0 to 127 (or to 255 if unsigned).

    *********

    The MySQL manual is poorly worded. NULL is not the same as FALSE, at all.

    Code:
    mysql> describe demo;
    +---------+------------+------+-----+---------+-------+
    | Field   | Type       | Null | Key | Default | Extra |
    +---------+------------+------+-----+---------+-------+
    | id      | int(11)    | YES  |     | NULL    |       |
    | approve | tinyint(1) | YES  |     | NULL    |       |
    +---------+------------+------+-----+---------+-------+
    
    mysql> select * from demo;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    1 |    NULL |
    |    2 |       0 |
    |    3 |       1 |
    +------+---------+
    
    mysql> select * from demo where approve = false;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    2 |       0 |
    +------+---------+
    
    mysql> select * from demo where approve = true;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    3 |       1 |
    +------+---------+
    
    mysql> select * from demo where approve is null;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    1 |    NULL |
    +------+---------+
    See?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  2. #17
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,191
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    And *ANY* field type in MySQL can hold a NULL if you specify the field as NULL.

    But not if you specify NOT NULL, as you did.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  3. #18
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,071
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Sorry...dumb me. I had MySQL and SQL Server confused.

    SQL Server allows BIT(1) for a BOOLEAN value, and that really does mean one *BIT*, not a full byte.

    You are right, a TINYINT(1) is one *byte*, so it allows values from 0 to 127 (or to 255 if unsigned).

    *********

    The MySQL manual is poorly worded. NULL is not the same as FALSE, at all.

    Code:
    mysql> describe demo;
    +---------+------------+------+-----+---------+-------+
    | Field   | Type       | Null | Key | Default | Extra |
    +---------+------------+------+-----+---------+-------+
    | id      | int(11)    | YES  |     | NULL    |       |
    | approve | tinyint(1) | YES  |     | NULL    |       |
    +---------+------------+------+-----+---------+-------+
    
    mysql> select * from demo;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    1 |    NULL |
    |    2 |       0 |
    |    3 |       1 |
    +------+---------+
    
    mysql> select * from demo where approve = false;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    2 |       0 |
    +------+---------+
    
    mysql> select * from demo where approve = true;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    3 |       1 |
    +------+---------+
    
    mysql> select * from demo where approve is null;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    1 |    NULL |
    +------+---------+
    See?
    That is how you would expect it to work.

    Very cool demo!

    I have questions, but see you wrote more...


    Debbie

  4. #19
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,071
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    And *ANY* field type in MySQL can hold a NULL if you specify the field as NULL.

    But not if you specify NOT NULL, as you did.
    So let's do a recap of all this discussion, and see if I can get everything down.

    - MySQL is retarded and doesn't have a BOOLEAN data-type.

    - MySQL allows you to use TinyInt(1) as a BOOLEAN, however it doesn't enforce the values of "0" and "1", right?

    - If that is true, then a TinyInt(1) could hold "0" to "9"

    - Any data-type in MySQL can have an attribute (?) which allows it to hold a NULL value

    - So a TinyInt(1) could hold my needed "Null", "0" (False) and "1" (True)

    - But what do I do to ensure only those 3 values are allowable WITHOUT using ENUM?

    - And would it be such a sin to have a Look-up Table like this...
    Code:
    key	value
    -----	--------
    null	Pending
    0	Declined
    1	Accepted
    - I haven't figured out the process-flow for my website yet, so I don't know exactly what I need, but if I have a table like above I should be golden.

    - I may not need the "values", but obviously I need to set the field as either "null", "0", "1"

    So what do you think? (Be nice!)


    Debbie

  5. #20
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,191
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Truly, it's not a big deal, whichever you decide. Just be aware that, to "see" the values from your main table you will have to always JOIN to that tiny little lookup table.

    Personally, I would find that bothersome for a simple ternary value. But that's really and truly up to you. Youse pays your money and takes youse choice.

    *******
    - MySQL allows you to use TinyInt(1) as a BOOLEAN, however it doesn't enforce the values of "0" and "1", right?
    - If that is true, then a TinyInt(1) could hold "0" to "9"
    Ummm...well, no. It can't hold string values such as "0" or "1" or "9", at all. (Sorry! Had to do that do you. <grin/>)

    It *CAN* hold numeric values of 0 and 9. But then quite a bit more than that:
    Code:
    mysql> describe demo;
    +---------+------------+------+-----+---------+-------+
    | Field   | Type       | Null | Key | Default | Extra |
    +---------+------------+------+-----+---------+-------+
    | id      | int(11)    | YES  |     | NULL    |       |
    | approve | tinyint(1) | YES  |     | NULL    |       |
    +---------+------------+------+-----+---------+-------+
    
    mysql> select * from demo;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    1 |    NULL |
    |    2 |       0 |
    |    3 |       1 |
    |    4 |     127 |
    |    5 |    -128 |
    +------+---------+
    TINYINT(1) is one *BYTE* in size, so it can hold 256 different values. A TINYINT(1) can hold any number from -128 to +127. A TINYINT(1) UNSIGNED can hold any number from 0 to 255. And, of course, NULL if you don't declare it as NOT NULL.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  6. #21
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,071
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    The MySQL manual is poorly worded. NULL is not the same as FALSE, at all.

    Code:
    mysql> describe demo;
    +---------+------------+------+-----+---------+-------+
    | Field   | Type       | Null | Key | Default | Extra |
    +---------+------------+------+-----+---------+-------+
    | id      | int(11)    | YES  |     | NULL    |       |
    | approve | tinyint(1) | YES  |     | NULL    |       |
    +---------+------------+------+-----+---------+-------+
    
    mysql> select * from demo;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    1 |    NULL |
    |    2 |       0 |
    |    3 |       1 |
    +------+---------+
    
    mysql> select * from demo where approve = false;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    2 |       0 |
    +------+---------+
    
    mysql> select * from demo where approve = true;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    3 |       1 |
    +------+---------+
    
    mysql> select * from demo where approve is null;
    +------+---------+
    | id   | approve |
    +------+---------+
    |    1 |    NULL |
    +------+---------+
    See?
    Yes, I see and now understand. But what you seem to be missing is why I created a Lookup Table in the first place...

    I did not create it so you can do a lookup like this...
    Code:
    key	value
    ----	------
    0	Null
    1	True
    2	False

    I did it so that - by your own admission - someone can't try and INSERT values like: 4, 5, 6,... 128,... 255

    By having a Lookup Table I can add Foreign Key Constraints that prevent that.

    Could I do that using an ENUM too? Yes.

    Would one be easier to maintain than the other? I don't know.

    But with a Lookup Table, if I did want to change things to this so I *could* display values, then it would be easier and have less impact on my code doing this...

    Code:
    key	value
    ----	------
    0	No Response
    1	Friendship Accepted
    2	Friendship Declined

    But again, I chose the Lookup Table for the Foreign Key Constraints...

    I don't think I'm way off here, and had at least one senior database person I know recommend this approach.

    In all of the back and forth I sorta lost what your message/sugegstion would be...

    Thanks for helping, nonetheless!


    Debbie

  7. #22
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,191
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    Sorry! I should have explained further.

    If you *did* opt to use just null/false/true, you are correct that MySQL doesn't enforce the use of null/0/1 for TINYINT(1). But if your code *interprets* the TINYINT(1) values as:
    Code:
    null : awaiting approval
    false (0) : declined
    true (any non-zero value) : approved
    then why does it matter if somebody plunks a value of 37 into the field? Any non-null, non-zero value is, logically, true.

    BUT...

    But from a PURIST view, you are of course correct.

    And while I can appreciate the purist view, there are times when I think pragmatism is called for.

    *********

    Having said all the above, I still don't know why the use of ENUM is such a bad idea. It, too, enforces the rules. Granted, it requires that you use what *looks* like a text field value when assigning to the field and retrieves what looks like text. But this doesn't seem terrible, to me.

    (There *is* one really bad aspect of ENUM, in MySQL: Unfortunately, MySQL *does* let you use the numeric equivalents of the ENUM values in queries. This doesn't particularly bother me, as you still can't specify values outside the ENUM range. But again, I can see why it would bother a purist.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  8. #23
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,191
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    And by the by, I would *NEVER* say you are "way off" by choosing the lookup table! If I ever gave that impression, I apologize. I've only been arguing from the pragmatic viewpoint.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  9. #24
    Senior Coder doubledee's Avatar
    Join Date
    Mar 2011
    Location
    Arizona
    Posts
    1,071
    Thanks
    26
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Sorry! I should have explained further.

    If you *did* opt to use just null/false/true, you are correct that MySQL doesn't enforce the use of null/0/1 for TINYINT(1). But if your code *interprets* the TINYINT(1) values as:
    Code:
    null : awaiting approval
    false (0) : declined
    true (any non-zero value) : approved
    then why does it matter if somebody plunks a value of 37 into the field? Any non-null, non-zero value is, logically, true.

    BUT...

    But from a PURIST view, you are of course correct.
    Just trying to learn and do things the *correct* way...


    And while I can appreciate the purist view, there are times when I think pragmatism is called for.
    Maybe.



    *********

    Having said all the above, I still don't know why the use of ENUM is such a bad idea. It, too, enforces the rules. Granted, it requires that you use what *looks* like a text field value when assigning to the field and retrieves what looks like text. But this doesn't seem terrible, to me.

    (There *is* one really bad aspect of ENUM, in MySQL: Unfortunately, MySQL *does* let you use the numeric equivalents of the ENUM values in queries. This doesn't particularly bother me, as you still can't specify values outside the ENUM range. But again, I can see why it would bother a purist.)
    Here is what others say...

    Google: why is enum evil

    http://komlenic.com/244/8-reasons-wh...-type-is-evil/


    Debbie

  10. #25
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,191
    Thanks
    75
    Thanked 4,341 Times in 4,307 Posts
    All 8 of komlenic's points are true.

    But...

    But take note of his "Criteria for when it might be okay to use enum:".

    Your case clearly matches those criteria.

    And I certainly agree with his point in the next section about using STRICT mode. I think running MySQL in other than STRICT mode is foolish.

    But now look at the little aside that he tosses in there in point 3 of the "Criteria for when it might be okay to use enum:".

    Why not, indeed, use BIT(1) instead of TINYINT(1)? In fact, if MySQL had any brains, they would make BOOLEAN an alias for BIT(1) instead of an alias for TINYINT(1). [They don't do so in order to maintain compatibility with earlier versions. Too bad. It should at least be an option.]

    Consider:
    Code:
       ... 
       approved bit(1) NULL,
       ...
    (and this is portable to some other DBs, notably SQL Server, if you care).

    So now really and truly approved can only have values of 0, 1, and NULL. And none of the objections about ENUM apply.

    ******************

    I hope you realize that all of this fall into the category of "The universal answer to most programming questions."

    Which is, of course, "It depends..."
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


 
Page 2 of 2 FirstFirst 12

Posting Permissions

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