{"id":2181,"date":"2010-06-03T07:24:11","date_gmt":"2010-06-03T05:24:11","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=2181"},"modified":"2010-06-03T07:24:11","modified_gmt":"2010-06-03T05:24:11","slug":"choosing-mysql-boolean-data-types","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/choosing-mysql-boolean-data-types","title":{"rendered":"Choosing MySQL boolean data types"},"content":{"rendered":"

How do you implement True<\/strong>\/False<\/strong> columns?<\/p>\n

There are many ways to do it, each with its own pros and cons.<\/p>\n

ENUM<\/h4>\n

Create you column as ENUM(‘F’, ‘T’)<\/strong>, or ENUM(‘N’,’Y’)<\/strong> or ENUM(‘0’, ‘1’)<\/strong>.<\/p>\n

This is the method used in the mysql<\/strong> tables (e.g. mysql.user<\/strong> privileges table). It’s very simple and intuitive. It truly restricts the values to just two options, which serves well. It’s compact (just one byte).<\/p>\n

A couple disadvantages to this method:<\/p>\n

    \n
  1. Enums are represented by numerical values (which is good) and start with 1<\/strong> instead of 0<\/strong>. This means ‘F’<\/strong> is 1<\/strong>, and ‘T’<\/strong> is 2<\/strong>, and they both translate to True<\/strong> when directly used in a booleanic expression (e.g. IF(val, ‘True’, ‘False’)<\/strong> always yields ‘True’<\/strong>)<\/li>\n
  2. There’s no real convention. Is it ‘Y’\/’N’<\/strong>? ‘T’\/’F’<\/strong>? ‘P’\/’N’<\/strong>? ‘1’\/’0′<\/strong>?<\/li>\n<\/ol>\n

    CHAR(1)<\/h4>\n

    Simple again. Proposed values are, as before, ‘F’<\/strong>, ‘T’<\/strong> etc. This time there’s no way to limit the range of values. You cannot (in MySQL, unless using triggers) prevent an ‘X’.<\/p>\n

    Watch out for the charset! If it’s utf8<\/strong> you pay with 3 bytes instead of just 1. And, again, ‘T’<\/strong>, ‘F’<\/strong>, ‘Y’<\/strong>, ‘N’<\/strong> values all evaluate as True<\/strong>. It is possible to use the zero-valued character, but it defeats the purpose of using CHAR<\/strong>.<\/p>\n

    CHAR(0)<\/h4>\n

    Many are unaware that it’s even valid to make this definition. What does it mean? Take a look at the following table:<\/p>\n

    \n
    CREATE TABLE `t1` (\r\n `bval` char(0) DEFAULT NULL\r\n);\r\nmysql> INSERT INTO t1 VALUES ('');\r\nmysql> INSERT INTO t1 VALUES ('');\r\nmysql> INSERT INTO t1 VALUES (NULL);\r\n\r\nmysql> SELECT * FROM t1;\r\n+------+\r\n| bval |\r\n+------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n| NULL |\r\n+------+\r\n<\/pre>\n<\/blockquote>\n
    NULLable columns cause for an additional storage per row. There’s one bit per NULLable column which notes down whether the column’s value is NULL or not. If you only have one NULLable column, you must pay for this bit with 1 byte. If you have two NULLable columns, you still only pay with 1 byte.<\/p>\n
    Furthermore:<\/p>\n
    \n
    mysql> SELECT bval IS NOT NULL FROM t1;\r\n+------------------+\r\n| bval IS NOT NULL |\r\n+------------------+\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 1 |\r\n|\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 0 |\r\n+------------------+\r\n<\/pre>\n<\/blockquote>\n
    So this plays somewhat nicely into booleanic expressions.<\/p>\n
    However, this method is unintuitive and confusing. I personally don’t use it.<\/p>\n
    TINYINT<\/h4>\n
    With integer values, we can get down to 0<\/strong> and 1<\/strong>. With TINYINT<\/strong>, we only pay with 1 byte of storage. As with CHAR(1)<\/strong>, we cannot prevent anyone from INSERTing other values. But that doesn’t really matter, if we’re willing to accept that 0 evaluates as False<\/strong>, and all other values<\/em> as True<\/strong>. In this case, boolean expressions work very well with your column values.<\/p>\n
    BOOL\/BOOLEAN<\/h4>\n
    These are just synonyms to TINYINT<\/strong>. I like to define my boolean values as such. Alas, when issuing a SHOW CREATE TABLE<\/strong> the definition is just a normal TINYINT<\/strong>. Still, it is clearer to look at if you’re storing your table schema under your version control.<\/p>\n","protected":false},"excerpt":{"rendered":"
    How do you implement True\/False columns? There are many ways to do it, each with its own pros and cons. ENUM Create you column as ENUM(‘F’, ‘T’), or ENUM(‘N’,’Y’) or ENUM(‘0’, ‘1’). This is the method used in the mysql tables (e.g. mysql.user privileges table). It’s very simple and intuitive. It truly restricts the values […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[18,21],"class_list":["post-2181","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-data-types","tag-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-zb","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2181","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=2181"}],"version-history":[{"count":28,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2181\/revisions"}],"predecessor-version":[{"id":2528,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/2181\/revisions\/2528"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=2181"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=2181"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=2181"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}