mysql 列数据类型对于保持效率很重要吗？
我是否需要仔细选择 mysql 数据库列的数据类型，或者我可以将内容设置为 varchar(99999) 没有大小限制而不损害效率？
谢谢 ：）
do i need to pick the datatype of my mysql database columns carefully, or can i set things to varchar(99999) without a size limit without damaging efficency?
thanks :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
选择代表您要存储的数据的数据类型。如果要存储数字，请使用专为存储数字而设计的数据类型。如果要存储日期，请使用专为存储日期而设计的数据类型。
如果所有数据确实都是文本，则对于长度可能变化的列选择 VARCHAR，对于长度永远不会变化或尾随空格不重要的列选择 CHAR。 VARCHAR 需要一个额外的字节来存储字段的长度，并且由于长度是可变的而带来非常小的性能损失，这在提取或存储数据时会产生更多步骤。
请注意，如果将 VARCHAR 列用作索引的一部分，则可能需要对其施加大小限制，因为 MySQL 上索引的最大大小为 1000 字节。因此，如果将所有列设为 VARCHAR(100)，则只能围绕三个此类字段创建复合索引。
（附带说明一下，MySQL 中不存在 VARCHAR(99999) 类型——VARCHAR 字段的最大长度为 255，因为记录中仅使用一个字节来存储每个 VARCHAR 的长度。）
Pick data types that represent the data you are storing. If you are storing numbers, use data types designed for storing numbers. If you are storing dates, use data types designed for storing dates.
If all of your data really is text, pick VARCHAR for columns where the length can vary, and pick CHAR for columns where the length will never vary, or where trailing spaces are not significant. VARCHAR takes one extra byte to store the length of the field, and carries with a very small performance penalty just because the length is variable, and this creates more steps when extracting or storing data.
Note that you may want to impose size limits on the VARCHAR columns if you are using them as part of an index, since the maximum size of an index on MySQL is 1000 bytes. So if you make all your columns VARCHAR(100), you will only be able to create composite indexes around three such fields.
(As a side note, the type VARCHAR(99999) does not exist in MySQL -- the maximum length of a VARCHAR field is 255, since only one byte is used to store the length of each VARCHAR in a record.)
我发现在设计新表时最好准确地声明列类型。例如，它有助于确保仅将数字输入到应仅包含数字的列中，例如价格。如果您的代码尝试使用美元金额，并且由于用户可以输入任何自由格式字符串而检索到“Asd$%*@)”，那么您的代码会做什么？
通过准确声明列类型，您可以确保检索的数据与您请求的类型相同，而无需进行任何繁琐的文本解析和错误检查。
I have found that it is better to accurately declare column types when designing a new table. For example, it helps to ensure that only a number is entered into a column that should only have a number, like in a price. What would your code do if it was trying to work with a dollar amount, and it retrieved 'Asd$%*@)' because the user could enter any free form string in?
By accurately declaring the column type, you can ensure that the data that you retrieve is the same type as you are requesting, without having to do any cumbersome text parsing and error checking.
毫无疑问，对于重要的数据库来说，数据类型非常非常重要。
对于 mysql 功能（例如时间、字符串或聚合函数），选择正确的数据类型非常重要。
对于模式功能（例如连接），选择正确的数据类型很重要。
为了提高性能，每个字段的每个字节都很重要。
所有长 varchar 的数据库将是令人遗憾的。
DBA 的存在是有原因的。 :)
Undoubtedly, for non-trivial databases, datatypes are very important.
For mysql functionality (e.g. time, string, or aggregate functions), choosing the right datatypes is important.
For schema functionality (e.g. joining), choosing the right datatypes is important.
For performance, every byte of every field counts.
A database of all long varchars would be regrettable.
DBA's exist for a reason. :)
取决于你想做什么。出于排序目的，您需要将整数存储为整数，我想说:)
不过，对于小型数据库，将所有内容都设为 varchar 可能不会造成太大损失。
Depends on what you want to do. For sorting purposes you'll want integers to be stored as integer, I'd say :)
For small databases though you probably won't suffer too much from making everything a varchar.
您还可以使用
select * from your_table procedure analysis(2, 5)（根据您的方便调整数字）来了解 MySQL 认为您的表应该是什么样子。You could also use
select * from your_table procedure analyse(2, 5)(adjust numbers to your convenience) to have an idea how MySQL thinks your tables should be.