MySQL TINYINT(1) to BOOL

Question

What exactly does a parameter on a column's data type do? Essentially:

I tried TINYINT(1), and it seemed that it only held 1 or 0. Why doesn't it display/store at least 0-9?

---

Edit: it turns out that the issue was not a MySQL problem but was rather unexpected behavior by CakePHP. Read my answer for details. I've kept my original question below so the helpful answers keep making sense.

---

What I've done:

I created a MySQL table using the command

CREATE TABLE table_name (
    ... irrelevant columns
    transaction_type TINYINT(1) NOT NULL COMMENT 'identifier from 0-5 (for now)'
);

and promptly became thoroughly confused, because I was unable to store values other than 0 and 1 in the transaction_type column.

After some research, I determined fairly confidently that the parameter to TINYINT (or any other integer data type) is nothing more than a display width in characters. I understood this to mean two things:

• It should still store the entire inserted value, as long as it is within TINYINT's bounds
• It will only display one character, so I should be able to see values 0-9 when I select from the table.

I figure at least one of these assumptions (or my understanding of the parameter as a whole) must be wrong, because whenever I tried to insert a value larger than 1 into that column, it would display 1.

I then tried:

ALTER TABLE table_name CHANGE transaction_type transaction_type TINYINT;

and I was suddenly able to insert whatever value I wanted. However, even though the above command changed the whole table, previous inserts still had a value of 1, making me suspicious that they were really stored as 1.

Am I misunderstanding display width, or am I doing something else wrong entirely?

Aside: If I don't care about how integer columns look in the table, is there any reason to ever supply one with a parameter? i.e. is INT(11) somehow better than INT?

A similar question that probably should have been more helpful to me: MySql: Tinyint (2) vs tinyint(1) - what is the difference?

The clearest source that I could find explaining display width: http://matt.thomm.es/archive/2006/11/28/mysql-integer-columns-and-display-width

Answer 1

In short: It only depends on how much the integer is zero filled if ZEROFILL has been enabled on the column.

---

Longer explanation:

The length of an integer in SQL doesn't change what numbers it holds (a TINYINT will hold -128 to 127, but an unsigned TINYINT will hold 0-255 etc. no matter the length.)

I really like the MySQL Cheat Sheet to check sizes.

Now - what's the actual difference between INT and INT(3)? It's simple. If ZEROFILL is enabled, the integer will be zero-padded to this length. That's the only difference.

There's a good article explaining this here.

Specifically, copied from the article (check it out! Get him some page-views!)

mysql> select * from b;  
+-------+ | b     |  
+-------+ | 10000 |  
+-------+ 1 row in set (0.00 sec)      

mysql> alter table b change b b int(11) zerofill; 
Query OK, 1 row   affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0      

mysql> select * from b;  
+-------------+ | b           |  
+-------------+ | 00000010000 |  
+-------------+ 1 row in set (0.00 sec) 

Answer 2

It turns out that CakePHP assumes that TINYINT(1) should be a boolean type, because MySQL aliases BOOL to TINYINT(1). Thus, any "true" values (like values greater than one) are assigned to be one, and any "false" values zero.

Warning for weary travelers: when using TINYINT(1), CakePHP may change your values without warning.

Old CakePHP bug here

Answer 3

The number in the parenthesis after TINYINT is just used for display purposes - it has nothing to do with the number of bytes that MySQL uses to store value in this field, and nothing to do with the range of values in this field. The number in parenthesis is simply used to specify how many digits will be displayed in this field when you run queries using MySQL's client.