MySQL：字段长度。这真的重要吗？
我正在使用一些数据库抽象层，其中大多数都使用诸如“String”之类的属性，它是 VARCHAR 250 或长度为 11 位的 INTEGER。但例如我有一些长度小于 250 个字符的内容。我应该去减少它吗？它真的有任何有价值的改变吗？
提前致谢！
I'm working with some database abstraction layers and most of them are using attributes like "String" which is VARCHAR 250 or INTEGER which has length of 11 digits. But for example I have something that will be less than 250 characters long. Should I go and make it less? Does it really makes any valuable difference?
Thanks in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
INT 长度没有任何作用。所有 INT 都是 4 个字节。您可以设置的数字仅用于
zerofill（谁使用它！？）。VARCHAR 长度的作用更多。这是字段的最大长度。保存 VARCHAR 以便仅存储实际数据，因此长度并不重要。如今，您可以拥有大于 255 字节（即 256^2-1）的 VARCHAR。区别在于用于字段长度的字节。 VARCHAR(100)、VARCHAR(8)和VARCHAR(255)使用1个字节来保存字段长度。 VARCHAR(1000) 使用 2。
希望有帮助 =)
编辑
我几乎总是将 VARCHAR 设为 250 长。无论如何，应该在应用程序中检查实际长度。对于更大的字段，我使用 TEXT （并且这些字段的存储方式不同，因此可能会更长）。
编辑
我不知道这有多新，但它曾经帮助我（理解）： http:// /help.scibit.com/Mascon/masconMySQL_Field_Types.html
INT length does nothing. All INTs are 4 bytes. The number you can set, is only used for
zerofill(and who uses that!?).VARCHAR length does more. It's the maxlength of the field. VARCHAR is saved so that only the actual data is stored, so the length doesn't mattter. These days, you can have bigger VARCHARs than 255 bytes (being 256^2-1). The difference is the bytes that are used for the field length. VARCHAR(100) and VARCHAR(8) and VARCHAR(255) use 1 byte to save the field length. VARCHAR(1000) uses 2.
Hope that helps =)
edit
I almost always make my VARCHARs 250 long. Actual length should be checked in the app anyway. For bigger fields I use TEXT (and those are stored differently, so can be much much longer).
edit
I don't know how current this is, but it used to help me (understand): http://help.scibit.com/Mascon/masconMySQL_Field_Types.html
首先，请记住，数据库旨在存储事实，并旨在保护自身免受不良数据的影响。因此，您不希望用户输入 250 个字符作为名字的原因是用户会在其中放入除名字之外的各种数据。他们会写上自己的全名、内衣尺码、一本关于他们去年夏天做了什么的小说等等。因此，您希望努力确保数据尽可能正确。假设应用程序是防止不良数据的唯一保护者，这是一个错误。您希望用户告诉您他们在将和平之战填充到给定列时遇到问题。
因此，最重要的问题是，“存储的数据最合适的值是多少？”理想情况下，您将使用 int 和检查约束来确保这些值具有适当的范围（例如大于零、小于十亿等）。不幸的是，这是 MySQL 最大的弱点之一：它不遵守检查约束。这仅仅意味着您必须在触发器中实现这些完整性检查，这无疑是更麻烦的。
int（4 字节）与tinyint（1 字节）之间的差异是否会产生明显差异？显然，这取决于数据量。如果您的行数不超过 10 行，答案显然是否定的。如果您有 100 亿行，答案显然是“是”。然而，在我看来，这是不成熟的优化。最好首先关注确保正确性。对于文本，您应该询问您的数据是否应支持中文、日文或非 ANSI 值（即，您应该使用 nvarchar 还是 varchar）？该值是否代表现实世界的代码，例如货币代码或具有特定规范的银行代码？
First, remember that the database is meant to store facts and is designed to protect itself against bad data. Thus, the reason you do not want to allow a user to enter 250 characters for a first name is that a user will put all kinds of data in there that is not a first name. They'll put their whole name, their underwear size, a novel about what they did last summer and so on. Thus, you want to strive to enforce that the data is as correct as possible. It is a mistake to assume that the application is the sole protector against bad data. You want users to tell you that they had a problem stuffing War in Peace into a given column.
Thus, the most important question is, "What is the most appropriate value for the data being stored?" Ideally, you would use an
intand a check constraint to ensure that the values have an appropriate range (e.g. greater than zero, less than a billion etc.). Unfortunately, this is one of MySQL's greatest weakness: it does not honor check constraints. That simply means you must implement those integrity checks in triggers which admittedly is more cumbersome.Will the difference between an
int(4 bytes) make an appreciable difference to atinyint(1 byte)? Obviously, it depends on the amount of data. If you will have no more than 10 rows, the answer is obviously no. If you will have 10 billion rows, the answer is obviously "Yes". However, IMO, this is premature optimization. It is far better to focus on ensuring correctness first.For text, you should ask whether your data should support Chinese, Japanese or non-ANSI values (i.e., should you use nvarchar or varchar)? Does this value represent a real world code like a currency code, or bank code which has a specific specification?
在 MySQL 中不太确定，但在 MS SQL 中，它只对足够大的数据库产生影响。通常，我喜欢使用较小的字段，因为 a) 节省空间（养成良好的习惯永远不会有坏处）和 b) 进行隐含验证（如果您知道某个字段不应超过 10 个字符，为什么允许 11 个字符，让单独250？）。
Not so sure in MySQL, but in MS SQL it only makes a difference for sufficiently large databases. Typically, I like to use smaller fields for a) the space saving (it never hurts to practice good habits) and b) for the implied validation (if you know a certain field should never be more than 10 characters, why allow eleven, let alone 250?).
我认为 Rudie 是错误的，并不是所有的 INT 都是 4 字节...在 MySQL 中你有：
tinyint = 1 字节，
小整型 = 2 字节，
中型整数 = 3 个字节，
int = 4 字节，
bigint = 8 字节。
我认为 Rudie 指的是“显示”，即创建列时放在括号之间的数字，例如：
age INT(3)
您只是告诉 RDBMS SHOW no超过3个数字。
VARCHAR 是（可变长度字符串），因此如果您声明名称 varchar(5000) 并存储像“Mario”这样的名称，您只使用 7 个字节（5 个字节用于数据，2 个字节用于值的长度）。
I thinks Rudie is wrong, not all INTs are 4 bytes... in MySQL you have:
tinyint = 1 byte,
smallint = 2 bytes,
mediumint = 3 bytes,
int = 4 bytes,
bigint = 8 bytes.
I think Rudie refers to the "display with" that is the number you put between parenthesis when you are creating a column, e.g.:
age INT(3)
You're telling to the RDBMS just to SHOW no more than 3 numbers.
And VARCHARs are (variable length charcter string) so if you declare let's say name varchar(5000) and you store a name like "Mario" you only are using 7 bytes (5 for the data and 2 for the length of the value).
正确的字段大小有助于限制可以输入的错误数据。例如，假设您有一个电话号码字段。如果允许 250 个字符，则通常会在电话字段中出现类似以下内容的内容（不是随机选取的示例）：
因此，首先限制长度是我们执行数据完整性规则的一部分。因此，这一点至关重要。
其次，数据页上只有这么多空间，虽然某些数据库允许您创建潜在记录长于数据页宽度的表，但它们通常不允许您在存储数据时实际超过它。当突然无法保存一条记录时，这可能会导致一些很难发现的错误。我不知道 MySql 以及它是否会这样做，但我知道 SQL Server 会这样做，而且很难找出问题所在。因此，使数据大小正确对于防止错误至关重要。
The correct field size serves to limit the bad data that can be put in. For instance suppose you have a phone number field. If you allow 250 characters, you will often end up with things like the following in the phone field (an example not taken at random):
So first limiting the length is part of how we enforce data integrity rules. As such it is critical.
Second, there is only so much space on a datapage and while some databases will allow you to create tables where the potential record is longer than the width of the data page, they often will not allow you to actually exceed it when storing the data. This can lead to some very hard to find bugs when suddenly one record can't be saved. I don't know about MySql and whether it does this but I know SQL Server does and it is very hard to figure out what is wrong. So making data the correct size can be critical to preventing bugs.