CakePHP + MySQL + Tinyint(1) = Confusion

Just a quick note to point out that CakePHP fakes a BOOLEAN field in MySQL by using tinyint(1). MySQL doesn’t have a native support for BOOLEAN.

Therefore if you attempt to save some value other than 0 or 1, CakePHP will not allow you to do that (instead it will just save 1). The easiest way to get around this is to make your field tinyint(2), as one option.

FYI, supporting ticket:

14 thoughts on “CakePHP + MySQL + Tinyint(1) = Confusion

  1. @Teknoid: What do you mean by not having support for BOOLEAN type?
    You can set BOOLEAN type for a column.
    Maybe the problem is that isn’t CakePHP that fakes a BOOLEAN in tinyints. Maybe MySQL does.
    I’m I wrong?

  2. @Silver Knight

    ENUM is not supported by cake. There are a few discussions as to why (which I mostly tend to agree with), but a quick search at the google group should give you plenty to digest.

  3. Thank you for the quick response. After a bit of research, it appears the primary reason the enum type is not supported in CakePHP is that enum is specific to a particular database vendor (MySQL). However, the same research turned up several nifty snippets of code which did allow CakePHP to support the enum type. This causes me to wonder: why hack around the lack of a boolean type by misusing the tinyint type when it would be just as simple to hack in a small “fix” using enum only when it’s needed to replace the boolean? I’m probably not understanding something key to the issue, I’m guessing?

  4. @Silver Knight

    No, you’ve got it just right. The ENUM type has been ruled out completely, that’s the bottom line…
    To me the deal with tinyint(1) seems worthwhile, it probably allows for easier adoption across various DB’s. And the benefit of using tinyint(1) for boolean automagic, far outweighs the inconvenience of having to make the field tinyint(2) if other values are needed to be stored.

  5. Well, you are probably right on that one. After all, declaring tinyint(1) or tinyint(2) doesn’t actually change the max values a tinyint can store anyhow. Either way, it’s 0-255 unsigned or -128 to 127 signed any way you slice it. The (1), (2), or (3) only affects the output, not the storage capacity (according to afaict), so I guess it’s not really a big deal. ;-)

  6. The deal is, if I want to store values from 0 up to 9, and I defined a TINYINT(1) UNSIGNED NOT NULL in MySQL, Cake will just assume I want to have a boolean field.

  7. @Kemal

    Yes, you are absolutely right… hence the point of the post and suggestion that you should use tinyint(2) instead, if you need values other than 1 or 0 for that field.

  8. I came upon this situation today and was scratching my head. I had a field I had originally been using as a boolean, but I decided to make it store a numerical value 0 to 9 instead, so I left it as a tinyint(1) but CakePHP just wouldn’t save anything but 0 or 1!

    Now I understand why, of course. I decided for my purposes to switch it to a char(1) though instead of a tinyint(2). BUT… it still didn’t work. It occurred to me that CakePHP must cache model information, and it does. So, just a tip to anyone who might be in my shoes. If you change your database structure, be sure to head over to app/tmp/cache/models and delete the file for the table you’ve altered. Once I did that, it worked like a charm!

  9. @room34

    That’s an excellent advice. It is very important to remember to clear cache after any DB modification to save a few head/desk connections.

    Thank you for pointing that out.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s