Which MySQL data type to use for storing boolean values

 

Since MySQL doesn't seem to have any 'boolean' data type, which data type do you 'abuse' for storing true/false information in MySQL?

Especially in the context of writing and reading from/to a PHP script.

Over time I have used and seen several approaches:

  • tinyint, varchar fields containing the values 0/1,
  • varchar fields containing the strings '0'/'1' or 'true'/'false'
  • and finally enum Fields containing the two options 'true'/'false'.

    • None of the above seems optimal. I tend to prefer the tinyint 0/1 variant, since automatic type conversion in PHP gives me boolean values rather simply.

    So which data type do you use? Is there a type designed for boolean values which I have overlooked? Do you see any advantages/disadvantages by using one type or another?

    For MySQL 5.0.3 and higher, you can use BIT . The manual says:

    As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

    Otherwise, according to the MySQL manual you can use bool and boolean which are at the moment aliases of tinyint(1):

    Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

    MySQL also states that:

    We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

    References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

    BTW: this is just a matter of https://google.com/search?q=mysql+boolean+datatype.

    Funny isn't it, this link, posted a few years back, has become recursive.

    BOOL and BOOLEAN are synonyms of TINYINT(1) . Zero is false , anything else is true . More information here.

    This is an elegant solution that I quite appreciate because it uses zero data bytes: 

    some_flag CHAR(0) DEFAULT NULL

    To set it to true, set some_flag = '' and to set it to false, set some_flag = NULL .

    Then to test for true, check if some_flag IS NOT NULL , and to test for false, check if some_flag IS NULL .

    (This method is described in "High Performance MySQL: Optimization, Backups, Replication, and More" by Jon Warren Lentz, Baron Schwartz and Arjen Lentz.)

    链接地址: http://www.djcxy.com/p/4262.html

    上一篇: PHP使用MySQL的最佳排序规则是什么？

    下一篇: 哪种MySQL数据类型用于存储布尔值