MySQL之char、varchar和text的设计
本文内容纲要：
最近有表结构设计中出现了varchar（10000）的设计引起了大家的讨论，我们下面就来分析分析。
首先我们先普及一下常识：
1、char（n）和varchar（n）中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入m个中文，但是实际会占用m*3个字节。
2、同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n。
3、超过char和varchar的n设置后，字符串会被截断。
4、char的上限为255字节，varchar的上限65535字节，text的上限为65535。
5、char在存储的时候会截断尾部的空格，varchar和text不会。
6、varchar会使用1-3个字节来存储长度，text不会。
下图可以非常明显的看到结果：
CHAR(4)
StorageRequired
VARCHAR(4)
StorageRequired
''
' '
''
1byte
'ab'
'ab '
4bytes
'ab'
3bytes
'abcd'
'abcd'
4bytes
'abcd'
5bytes
'abcdefgh'
'abcd'
4bytes
'abcd'
5bytes
总体来说：
1、char，存定长，速度快，存在空间浪费的可能，会处理尾部空格，上限255。
2、varchar，存变长，速度慢，不存在空间浪费，不处理尾部空格，上限65535，但是有存储长度实际65532最大可用。
3、text，存变长大数据，速度慢，不存在空间浪费，不处理尾部空格，上限65535，会用额外空间存放数据长度，顾可以全部使用65535。
接下来，我们说说这个场景的问题：
当varchar（n）后面的n非常大的时候我们是使用varchar好，还是text好呢？这是个明显的量变引发质变的问题。我们从2个方面考虑，第一是空间，第二是性能。
首先从空间方面：
从官方文档中我们可以得知当varchar大于某些数值的时候，其会自动转换为text，大概规则如下：
-
- 大于varchar（255）变为tinytext
- 大于varchar（500）变为text
- 大于varchar（20000）变为mediumtext
所以对于过大的内容使用varchar和text没有太多区别。
其次从性能方面：
索引会是影响性能的最关键因素，而对于text来说，只能添加前缀索引，并且前缀索引最大只能达到1000字节。
而貌似varhcar可以添加全部索引，但是经过测试，其实也不是。由于会进行内部的转换，所以longvarchar其实也只能添加1000字节的索引，如果超长了会自动截断。
localhost.test>createtabletest(avarchar(1500));
QueryOK,0rowsaffected(0.01sec)
localhost.test>altertabletestaddindexidx_a(a);
QueryOK,0rowsaffected,2warnings(0.00sec)
Records:0Duplicates:0Warnings:2
localhost.test>showwarnings;
+---------+------+---------------------------------------------------------+
|Level|Code|Message|
+---------+------+---------------------------------------------------------+
|Warning|1071|Specifiedkeywastoolong;maxkeylengthis767bytes|
|Warning|1071|Specifiedkeywastoolong;maxkeylengthis767bytes|
+---------+------+---------------------------------------------------------+
从上面可以明显单看到索引被截断了。而这个767是怎么回事呢？这是由于innodb自身的问题，使用innodb_large_prefix设置。
从索引上看其实longvarchar和text也没有太多区别。
所以我们认为当超过255的长度之后，使用varchar和text没有本质区别，只需要考虑一下两个类型的特性即可。（主要考虑text没有默认值的问题）
CREATETABLE`test`(
`id`int(11)DEFAULTNULL,
`a`varchar(500)DEFAULTNULL,
`b`text
)ENGINE=InnoDBDEFAULTCHARSET=utf8
+----------+------------+-----------------------------------+
|Query_ID|Duration|Query|
+----------+------------+-----------------------------------+
|1|0.01513200|selectafromtestwhereid=10000|
|2|0.01384500|selectbfromtestwhereid=10000|
|3|0.01124300|selectafromtestwhereid=15000|
|4|0.01971600|selectbfromtestwhereid=15000|
+----------+------------+-----------------------------------+
从上面的简单测试看，基本上是没有什么区别的，但是个人推荐使用varchar（10000），毕竟这个还有截断，可以保证字段的最大值可控，如果使用text那么如果code有漏洞很有可能就写入数据库一个很大的内容，会造成风险。
故，本着shortisbetter原则，还是使用varchar根据需求来限制最大上限最好。
附录：各个字段类型的存储需求
StorageRequired |
|
TINYINT |
1byte |
SMALLINT |
2bytes |
MEDIUMINT |
3bytes |
INT,INTEGER |
4bytes |
BIGINT |
8bytes |
FLOAT(p) |
4bytesif0<=p<=24,8bytesif25<=p<=53 |
FLOAT |
4bytes |
DOUBLE[PRECISION],REAL |
8bytes |
DECIMAL(M,D),NUMERIC(M,D) |
Varies;seefollowingdiscussion |
BIT(M) |
approximately(M+7)/8bytes |
DataType |
StorageRequiredBeforeMySQL5.6.4 |
StorageRequiredasofMySQL5.6.4 |
YEAR |
1byte |
1byte |
DATE |
3bytes |
3bytes |
TIME |
3bytes |
3bytes+fractionalsecondsstorage |
DATETIME |
8bytes |
5bytes+fractionalsecondsstorage |
TIMESTAMP |
4bytes |
4bytes+fractionalsecondsstorage |
DataType |
StorageRequired |
CHAR(M) |
M×wbytes,0<=M<=255,wherewisthenumberofbytesrequiredforthemaximum-lengthcharacterinthecharacterset |
BINARY(M) |
Mbytes,0<=M<=255 |
VARCHAR(M),VARBINARY(M) |
L+1bytesifcolumnvaluesrequire0–255bytes,L+2bytesifvaluesmayrequiremorethan255bytes |
TINYBLOB,TINYTEXT |
L+1bytes,whereL<28 |
BLOB,TEXT |
L+2bytes,whereL<216 |
MEDIUMBLOB,MEDIUMTEXT |
L+3bytes,whereL<224 |
LONGBLOB,LONGTEXT |
L+4bytes,whereL<232 |
ENUM('value1','value2',...) |
1or2bytes,dependingonthenumberofenumerationvalues(65,535valuesmaximum) |
SET('value1','value2',...) |
1,2,3,4,or8bytes,dependingonthenumberofsetmembers(64membersmaximum) |
本文内容总结：
原文链接：https://www.cnblogs.com/billyxp/p/3548540.html