MySQL display width
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?
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) - Which 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
No comments:
Post a Comment