MySQL：为什么不使用zerofill指定显示宽度 [英] MySQL: Why specify display width without using zerofill

问题描述

我最近深入了SQL的令人兴奋的世界。我仍然试图把我的头围绕着这些概念。我一直在下面的教程在线。许多这些教程都包含用于创建这样的表的SQL。

I have recently delved into the exciting world of SQL. I am still trying to wrap my head around the concepts. I have been following tutorials online. Many of these tutorials contain SQL for making a table like this.

CREATE TABLE `users` (
     `id` tinyint(4) NOT NULL AUTO_INCREMENT,
     `username` varchar(10) NOT NULL,
     `password` varchar(100) NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

看到 id tinyint（4）我想知道什么参数传递给数据类型。我想这是不是意味着id可以是-128到127（不超过4个字符）之间的任何整数？

After seeing lines like id tinyint(4)I wondered what the parameter passed to the data type. I thought "does that mean id can be any integer between -128 and 127 (no more than 4 characters)?"

所以我查阅了文档。这是 MySQL文档必须说明的数字类型属性。

So I consulted the docs. This is what MySQL docs have to say about number type attributes.

显示宽度不限制列中存储的值
的值范围。它也不防止宽度大于列
的值显示宽度正确显示。例如，指定为SMALLINT（3）的列
的SMALLINT范围通常为-32768至
32767，超出三位数允许范围的值为
，使用超过三个数字

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

与可选（非标准）属性
ZEROFILL配合使用时，空格的默认填充将替换为零。对于
示例，对于声明为INT（4）ZEROFILL的列，值为5是以0005检索的
。

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.

所以，如果我读这个权利，声明像 INT（255）是无用的，除非你使用zerofill。 OK有意义，你声明一个数据库为数据库分配足够的空间数据库。

So if I'm reading this right, declaring things like INT(255) are useless unless you are using zerofill. Ok makes sense you declare a datatype the database allocates enough space for that database.

那么，为什么人们写这样的代码？它有用吗？我完全误会了吗？

So why do people write code like this? Does it serve a purpose? Am I completely misunderstanding?

推荐答案

这个额外的功能显示宽度很混乱，因为在其他列类型 CHAR 指定长度。

This additional "feature" display width is quite confusing, because in other column types like CHAR is specifies the length.

这里是一个简短的细目：

Here is a short breakdown:

• 最重要的是：不指定存储空间或位数。它只是说明此列中的数据在返回之前是否已格式化。 INT（5）可以存储相同值，例如INT（16）或INT（255） - 所有三个都可以存储对INT有效的所有（且仅有）值。 INT（255）不能存储具有255个数字的数字。


• 如果在具有显示宽度的列上使用 ZEROFILL ，那么所有的存储空间都是INT占用的空间。存储数字的字符串表示比显示宽度短，它将用零填充。如果它更长，没有发生。如果选择INT（5）并且值为13，则将返回为00013.如果值为123456，则将返回为123456.


• 如果不使用 ZEROFILL ，将不会有填充（无空格等）


• 如果使用 ZEROFILL ，您必须知道该列也将 UNSIGNED


• 在任何情况下，查询表元数据。因此，应用程序可以知道数据的格式。

• Most important: It is not specifying the "storage space" or "number of digits". It is just saying how the data in this column is formatted before it is returned. INT(5) can store the same values like INT(16) or INT(255) - all three can store all (and only) values that are valid for INT. INT(255) can not store a number with 255 digits. The storage space for all of them is the space an INT occupies.
• If you use ZEROFILL on a column with display width, and the string representation of the stored number is shorter than the display width, it will be left-padded with zeros. If it is longer, nothing happens. If you choose INT(5) and the value is 13, it will be returned as 00013. If the value is 123456 it will be returned as 123456.
• If you do not use ZEROFILL, there will be no padding at all (no spaces and such)
• If you use ZEROFILL, you must be aware that the column also will be UNSIGNED
• In any case, the display width is returned when the table meta data is queried. So an application could know how the data should be formatted.

我不喜欢显示宽度，因为存储层是知道视觉呈现所存储的数据。除此之外，我没有意识到的用途。

I dislike the display width, because the storage layer is "knowing" about visual presentation of the stored data. Besides this, there is no use that I am aware of.

这篇关于MySQL：为什么不使用zerofill指定显示宽度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！