MySQL - 整数列的大小限制

2022-01-05 00:00:00 integer mysql phpmyadmin

我正在使用 phpMyAdmin 创建我的表结构.

我可以从 MySQL 的文档页面中阅读有关整数类型大小限制的信息:

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)

解决方案

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 28 distinct values.
  • SMALLINT is always 16 bits and can store 216 distinct values.
  • INT is always 32 bits and can store 232 distinct values.
  • BIGINT is always 64 bits and can store 264 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)?

相关文章