MySQL - Size Limits to Integer Columns

Question

I'm using phpMyAdmin to create my table structures.

I can read from the documentation pages on MySQL about size limits for Integer Types: MySQL Integer Types Reference

---

So here is where I'm getting a little confused with creating a column.

I want to create a column in the table: tbl_note_categories called notescounter

I don't foresee myself creating thousands of noteids in the tbl_notes with any specific categoryid. But I do believe I'd create hundreds of notes to each categoryid.

I'm at that point of choosing between: tinyint, smallint, mediumint. According the documentation link above, I'm guessing smallint is my best choice.

So here's my confusion. PhpMyAdmin asks for a Length/Values parameter to be specified. I'm going to make sure this new column (notescounter) is unsigned, giving me up to 65536. Does that mean I need the Length/Values to be (5)?

I'm guessing Length is character length, but I'm not sure. (comparing to varchar)

Solution 1

No, this is a common misconception about MySQL. In fact, the "length" has no effect on the size of an integer or the range of values it can store.

• TINYINT is always 8 bits and can store 2⁸ distinct values.
• SMALLINT is always 16 bits and can store 2¹⁶ distinct values.
• INT is always 32 bits and can store 2³² distinct values.
• BIGINT is always 64 bits and can store 2⁶⁴ distinct values.

There's also a MEDIUMINT, but the engineers who work on MySQL tell me MEDIUMINT always gets promoted to a 32-bit INT internally, so there's actually no benefit to using MEDIUMINT.

The length is only for display, and this only matters if you use the ZEROFILL option.

See an example in my answer to What is the difference (when being applied to my code) between INT(10) and INT(12)?

Solution 2

Yes, you want to specify a length of 5.

In MySQL, the "length" attribute on the integer types is optional. It's a MySQL extension which is non-standard).

When it is omitted from the column declaration, MySQL provides a default value. For a SMALLINT UNSIGNED, the default value is 5.

This value does NOT have any impact on the range of values that can be stored for an integer type. It specifies a "display length", which is returned in resultset metadata, which a client can choose to use or ignore.

http://dev.mysql.com/doc/refman/5.5/en/numeric-type-attributes.html