0

I studied the MySQL documentation and am now unsure what the column length specification (M) in TINYINT(M) means. A TINYINT UNSIGNED has a value range of 0 to 255, but TINYINT(1) UNSIGNED has a range of 0 to 9 ?

Is it better for compression?

Holger Just
  • 52,918
  • 14
  • 115
  • 123
Robin
  • 15
  • 1
  • 6

1 Answers1

3

From MySQL documentation about numeric type attributes:

The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

When used in conjunction with the optional (nonstandard) attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.

That said, you should consider this attribute only if you care about the 0s you could show on the left or if you are in a CLI environment. Furthermore, if you declare a field as tinyint(2) and the number stored in is 113, all 3 characters will shown (despite what previous answer said)

carmine
  • 1,597
  • 2
  • 24
  • 33
  • +1 This is correct. Also TINYINT(1) is a mnemonic for a boolean. MySQL supports a data type `BOOL` which is an "alias" so to speak. When you define a column as BOOL, it's instantly translated into TINYINT(1), even though the (1) doesn't actually constrain the length or range of values. – Bill Karwin Dec 06 '13 at 20:08
  • Ok so when the zerofill argument isn't use, there is no need to set a M number. – Robin Dec 06 '13 at 21:14