INT（3）列没有将值剪切为适当的长度并允许插入完整值 - MySQL [英] INT(3) column not cliping the value to appropriate length and allowing the full value to be inserted - MySQL

问题描述

我有一个INT（3）UNSIGNED列。如果我插入一个字符长度超过3的值，它不会剪切该值而是插入它。

I have an INT(3) UNSIGNED column. If I insert a value with character length more that 3, it doesn't clip that value but inserts it.

发生什么事？

推荐答案

FROM MySQL的int（size）中的size是什么意思？

最后，让我们来到手册的位置，其中有
最大提示数字的含义：

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表示整数类型的最大显示宽度。对于
浮点和定点类型，M是可以存储的
的总位数。对于字符串类型，M是最大长度。
M的最大允许值取决于数据类型。

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.

它与显示宽度有关。奇怪的是，尽管如此，对于
的例子，如果你在一个显示
宽度为4位的字段中有5位数值，显示宽度不会减少数字。

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开始发挥作用。这是一个简洁的功能，填充值
（这里它来）小于指定的显示宽度
零，因此您将始终收到指定
长度的值。例如，这对发票ID很有用。

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.

因此，结论：大小既不是位也不是字节。它只是
显示宽度，当字段指定了ZEROFILL时使用。

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

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)

这篇关于INT（3）列没有将值剪切为适当的长度并允许插入完整值 - MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！