Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2008
    Posts
    14

    Unanswered: Whats the best datatype to use for boolean data?

    I need a column for status that takes only one of two values, 0 or 1. I also need a way to flip the status (similar to binary bit compliment) from 0 to 1 or 1 to 0. Can I do this in MySql? Some thing like Update _tbl set flag=!flag?

    Thanks

    Grav

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    look on the manual at the bit data type

    And as for complimenting the boolean; I think the answer in MySQL is to use the bang (!) symbol
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use TINYINT

    reason? it is the easiest datatype to upgrade to 3 values (e.g. 0,1,2) with the least impact on application code

    to flip the status, use
    Code:
    UPDATE daTable
       SET status = CASE WHEN status = 1 THEN 0 ELSE 1 END
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Argueably, bit allows for 3 values, the third being the absence of a value, but a fair point nonetheless.
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2008
    Posts
    14
    I tried the bang (!) on tinyint I dont think it works. I ll try it on bit datatypes.

    Thanks everyone for your help.

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Failing that, try the tilde (~)

    Let us know how you get on
    George
    Home | Blog

  7. #7
    Join Date
    Oct 2008
    Posts
    14
    The ! doesnt work, but ~ seems to work but is behaving funny. It flipped a b'0' to b'1' but could not flip a 1 back. Must be something I am doing wrong. Anyways I opted to use tinyint(1)

    with "UPDATE daTable
    SET status = CASE WHEN status = 1 THEN 0 ELSE 1 END" for flipping bits as r937 suggested.

    Thanks everyone

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by georgev
    Argueably, bit allows for 3 values, the third being the absence of a value, but a fair point nonetheless.
    I always thought the absence of a value is represented with NULL in an RDBMS...

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That's what I was hinting at shammat, but I see your finicky point
    George
    Home | Blog

Posting Permissions

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