mysql display size_mysql integer size 大小

标签:

I was always wondering what the size of numeric columns in MySQL was. Forgive me if this is obvious to someone else. But for me the MySQL manual lacks a great deal in this field.

TL;DR: It‘s about the display width. You only see it when you use ZEROFILL.

Usually you see something like int(11) in CREATE TABLE statements, but you can also change it to int(4).

So what does this size mean? Can you store higher values in a int(11) than in an int(4)?

Let‘s see what the MySQL manual says:

INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

No word about the M. The entry about BOOL suggests that the size is not there for fun as it is a synonym for TINYINT(1) (with the specific size of 1).

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

BOOL, BOOLEAN

These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true: […]

So TINYINT(1) must be different in some way from TINYINT(4) which is assumed by default when you leave the size out1. Still, you can store for example 100 into a TINYINT(1).

Finally, let‘s come to the place of the manual where there is the biggest hint to what the number means:

Several of the data type descriptions use these conventions:

M indicates the maximum display width for integer types. For

floating-point and fixed-point types, M is the total number of digits

that can be stored. For string types, M is the maximum length. The

maximum allowable value of M depends on the data type.

It‘s about the display width. The weird thing is, though2,

that, for example, if you have a value of 5 digits in a field with a

display width of 4 digits, the display width will not cut a digits off.

If the value has less digits than the display width, nothing happens

either. So it seems like the display doesn‘t have any effect in real

life.

Now2 ZEROFILL comes into play. It is a neat feature that pads values that are (here it comes) less than the specified display width with zeros, so that you will always receive a value of the specified length. This is for example useful for invoice ids.

So, concluding: The size is neither bits nor bytes. It‘s just the display width, that is used when the field has ZEROFILL specified.

If you see any more uses in the size value, please tell me. I am curious to know.

1 See this example:

mysql> create table a ( a tinyint );

Query OK, 0 rows affected (0.29 sec)

mysql> show columns from a;

+-------+------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| a | tinyint(4) | YES | | NULL | |

+-------+------------+------+-----+---------+-------+

1 row in set (0.26 sec)

mysql> alter table a change a a tinyint(1);

Query OK, 0 rows affected (0.09 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into a values (100);

Query OK, 1 row affected (0.00 sec)

mysql> select * from a;

+-----+

| a |

+-----+

| 100 |

+-----+

1 row in set (0.00 sec)

2 Some code to better explain what I described so clumsily.

mysql> create table b ( b int (4));

Query OK, 0 rows affected (0.25 sec)

mysql> insert into b values (10000);

Query OK, 1 row affected (0.00 sec)

mysql> select * from b;

+-------+

| b |

+-------+

| 10000 |

+-------+

1 row in set (0.00 sec)

mysql> alter table b change b b int(11);

Query OK, 1 row affected (0.00 sec)

Records: 1 Duplicates: 0 Warnings: 0

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)

mysql> alter table b change b b int(4) zerofill;

Query OK, 1 row affected (0.08 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from b;

+-------+

| b |

+-------+

| 10000 |

+-------+

1 row in set (0.00 sec)

mysql> alter table b change b b int(6) zerofill;

Query OK, 1 row affected (0.01 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from b;

+--------+

| b |

+--------+

| 010000 |

+--------+

1 row in set (0.00 sec)

标签:

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值