MySQL 在 VarChar(255) 和 VarChar(65536) 之间的不同处理
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5898904/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL different treatment between VarChar(255) and VarChar(65536)
提问by TheSquad
Does anyone know the difference between using VarChar(255) and VarChar(65536) ?
有谁知道使用 VarChar(255) 和 VarChar(65536) 之间的区别吗？
Here's what I know so far :
这是我目前所知道的：
- VarChar(255) will only use one byte for size
- VarChar(65536) will use two bytes for size
- VarChar(65536) exists only since MySQL 5.0.3
- MySQL use a different treatment between 255 and 65536 (don't know the difference though)
- VarChar(255) 将只使用一个字节的大小
- VarChar(65536) 将使用两个字节作为大小
- VarChar(65536) 只存在于 MySQL 5.0.3
- MySQL 在 255 和 65536 之间使用不同的处理（虽然不知道区别）
What I'm not sure of if is it any performance difference between those varchars.
我不确定这些 varchars 之间是否有任何性能差异。
Let's say I would like to create a table with a multiple type of row. Using an enum with 'data_type1' and 'data_type2'
假设我想创建一个包含多种行类型的表。使用带有“data_type1”和“data_type2”的枚举
data_type1 will never have more than 255 characters in the varchar data_type2 will mostly have more than 255 characters in the varchar
data_type1 在 varchar 中永远不会超过 255 个字符 data_type2 在 varchar 中通常会超过 255 个字符
Which solution table would be better ?
哪个解表会更好？
id (int) - autoincrement
type (enum : data_type1, data_type2)
msg (varchar(255))
data (TEXT)
Using only the 'data' column when type == data_type2 ?
当 type == data_type2 时仅使用“数据”列？
or
或者
id (int) - autoincrement
type (enum : data_type1, data_type2)
msg (varchar(65536))
Using 'msg' column whatever type is present ?
使用“msg”列，无论存在何种类型？
The type column is actually wanted on both solution for treatment purpose.
出于处理目的，两种解决方案实际上都需要类型列。
EDIT :
编辑 ：
When type == data_type2, the data stored will actually never be over 10000 characters
当type == data_type2时，存储的数据实际上永远不会超过10000个字符
EDIT 2 :
编辑 2：
I'm not looking to search in columns msg and data
我不想在 msg 和 data 列中搜索
This is a simple question about storage performance, not indexes or search perf...
这是一个关于存储性能的简单问题，而不是索引或搜索性能...
回答by Michael J.V.
You mentioned some things that are true, I'll explain how VARCHAR works.
您提到了一些正确的事情，我将解释 VARCHAR 的工作原理。
If you specify VARCHAR(60) that means it can hold up to 60 characters. If it contains less characters, let's say 50 - then MySQL uses 50 bytes to store the data instead of 60.
如果您指定 VARCHAR(60)，则意味着它最多可容纳 60 个字符。如果它包含较少的字符，假设为 50 - 那么 MySQL 使用 50 个字节而不是 60 个字节来存储数据。
It's quite the opposite with CHAR(60) - it reserves 60 bytes regardless of the length of the string you want to save.
CHAR(60) 正好相反——它保留 60 个字节，而不管您要保存的字符串的长度如何。
Now, how does VARCHAR work? If you specify it as VARCHAR(255), that means that the column will reserve 1 byte + length of the string bytes. That 1 byte indicates how long the string is. 1 byte = you can have from 0 - 255 values stored (2 to the power of 8 = 256).
现在，VARCHAR 是如何工作的？如果将其指定为 VARCHAR(255)，则表示该列将保留 1 个字节 + 字符串字节的长度。该 1 个字节表示字符串的长度。1 个字节 = 您可以存储 0 - 255 个值（2 的 8 次方 = 256）。
As for VARCHAR that is above 255, you need to somehow store how many bytes are being used. Since 1 byte can store only up to 256 distinct values (0 - 255), you need to use two bytes. Two to the power of 16 = 65536, which means you can store any string up to that size and then it adds up 2 bytes to indicate how long the string is.
对于大于 255 的 VARCHAR，您需要以某种方式存储正在使用的字节数。由于 1 个字节最多只能存储 256 个不同的值 (0 - 255)，因此您需要使用两个字节。2 的 16 次方 = 65536，这意味着您可以存储任何大小的字符串，然后加起来 2 个字节来表示字符串的长度。
So, to cut it short - performance difference is that if you have VARCHAR(65536) and you use 200 bytes to store the text - you are wasting that 1 extra byte that VARCHAR(65536) will use. One might think "oh, but that's just 1 byte, who cares about that". A lot of people do actually - imagine having several VARCHAR columns on a table that's got 50 million records. Say you got 3 varchar columns, each wastes additional byte - that's 3 bytes * 50 million ~ 144 megabytes of wasted space. The funny thing is, it's not just wasted space. It also contributes to processing overhead and using extra RAM when you want to read something. And who says there's going to be only 1 table in your DB that's going to get large?
因此，简而言之 - 性能差异在于，如果您有 VARCHAR(65536) 并且您使用 200 个字节来存储文本 - 您正在浪费 VARCHAR(65536) 将使用的 1 个额外字节。有人可能会想“哦，但这只是 1 个字节，谁在乎呢”。很多人实际上是这样做的 - 想象一下在一个有 5000 万条记录的表上有几个 VARCHAR 列。假设您有 3 个 varchar 列，每个列都浪费了额外的字节 - 即 3 字节 * 5000 万~144 兆字节的浪费空间。有趣的是，这不仅仅是浪费空间。当您想要阅读某些内容时，它还有助于处理开销和使用额外的 RAM。谁说数据库中只有 1 个表会变大？
Knowing this information might help you deciding yourself what's the best to use.
了解这些信息可能会帮助您决定最好使用什么。
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.htmlif you didn't check it before, it explains how every data type is stored and how much space it requires.
http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html如果您之前没有检查它，它解释了每种数据类型的存储方式以及它需要多少空间。
回答by PachinSV
IMO it's better to use VARCHAR over any other string type, because TEXT has a size limit and CHAR reserves space on disk. VARCHAR only uses space for the character you have entered.
IMO 最好在任何其他字符串类型上使用 VARCHAR，因为 TEXT 有大小限制，而 CHAR 在磁盘上保留空间。VARCHAR 只为您输入的字符使用空格。
