varchar(20)和varchar(50)相同吗? [英] varchar(20) and varchar(50) are same?

问题描述

我看到一条评论:如果在varchar(20)列中有5000万个值，介于10到15个字符之间，而在varchar(50)列中具有相同的5000万个值，则它们将占用完全相同的空间.这就是varchar的整个要点，而不是char.".有人可以告诉我原因吗?请参阅什么是合理的长度限制人姓名"字段?

解决方案

MySQL提供了一种存储引擎选择.数据的物理存储取决于存储引擎.

MyISAM VARCHAR存储

在MyISAM中，VARCHAR通常仅占据字符串的实际长度加上一个或两个字节的长度.这是由于MyISAM的设计限制于表锁定而不是行锁定功能而变得可行.性能后果包括更紧凑的缓存配置文件，还包括更复杂(更慢)的记录偏移量计算.

(实际上，MyISAM为您提供一定程度的选择在固定物理行大小和可变物理行大小表格式之间(取决于整个表中出现的列类型).VARCHAR的出现仅更改了默认方法，但是TEXT blob forces 在同一表中的VARCHAR上也使用可变长度方法.)

物理存储方法对于索引尤其重要，这与表的故事不同. MyISAM对，，CHAR和VARCHAR列都使用空间压缩，这意味着较短的数据在两种情况下都占用较少的索引空间.

InnoDB VARCHAR存储

InnoDB与大多数其他当前的关系数据库一样，使用了更复杂的机制.最大宽度小于768字节的VARCHAR列将内联存储，保留的空间与该最大宽度匹配.更准确地此处:

对于每个非NULL可变长度字段，记录头包含 列的长度，以一或两个字节为单位.两个字节只会是 如果列的一部分存储在外部溢出页面中，则需要 最大长度超过255个字节，实际长度超过127个字节 个字节.对于外部存储的列，两个字节的长度表示 内部存储部分的长度加上指向20字节的指针 外部存储的部分.内部部分是768个字节，因此 长度是768 + 20. 20字节的指针存储了 列.

InnoDB当前不对其索引进行空间压缩，这与如上所述的MyISAM相反.

返回问题

但是，以上所有内容只是一个实现细节，甚至可能在版本之间有所不同. CHAR和VARCHAR之间的真正区别在于语义，VARCHAR(20)和VARCHAR(50)之间的区别也是如此.通过确保无法在VARCHAR(20)中存储30个字符串，该数据库使各种处理器和应用程序的寿命更加轻松和更好地定义，这些处理器和应用程序据称已集成到行为可预测的解决方案中.这很重要.

关于个人姓名，此问题可能会给您一些实用的指导.无论如何，全名超过70个UTF-8字符的人都会遇到麻烦.

I saw comment "If you have 50 million values between 10 and 15 characters in a varchar(20) column, and the same 50 million values in a varchar(50) column, they will take up exactly the same space. That's the whole point of varchar, as opposed to char.". Can Anybody tell me the reason? See What is a reasonable length limit on person "Name" fields?

解决方案

MySQL offers a choice of storage engines. The physical storage of data depends on the storage engine.

MyISAM Storage of VARCHAR

In MyISAM, VARCHARs typically occupy just the actual length of the string plus a byte or two of length. This is made practical by the design limitation of MyISAM to table locking as opposed to a row locking capability. Performance consequences include a more compact cache profile, but also more complicated (slower) computation of record offsets.

(In fact, MyISAM gives you a degree of choice between fixed physical row size and variable physical row size table formats depending on column types occuring in the whole table. Occurrence of VARCHAR changes the default method only, but the presence of a TEXT blob forces VARCHARs in the same table to use the variable length method as well.)

The physical storage method is particularly important with indexes, which is a different story than tables. MyISAM uses space compression for both CHAR and VARCHAR columns, meaning that shorter data take up less space in the index in both cases.

InnoDB Storage of VARCHAR

InnoDB, like most other current relational databases, uses a more sophisticated mechanism. VARCHAR columns whose maximum width is less than 768 bytes will be stored inline, with room reserved matching that maximum width. More accurately here:

For each non-NULL variable-length field, the record header contains the length of the column in one or two bytes. Two bytes will only be needed if part of the column is stored externally in overflow pages or the maximum length exceeds 255 bytes and the actual length exceeds 127 bytes. For an externally stored column, the two-byte length indicates the length of the internally stored part plus the 20-byte pointer to the externally stored part. The internal part is 768 bytes, so the length is 768+20. The 20-byte pointer stores the true length of the column.

InnoDB currently does not do space compression in its indexes, the opposite of MyISAM as described above.

Back to the question

All of the above is however just an implementational detail that may even change between versions. The true difference between CHAR and VARCHAR is semantic, and so is the one between VARCHAR(20) and VARCHAR(50). By ensuring that there is no way to store a 30 character string in a VARCHAR(20), the database makes the life easier and better defined for various processors and applications that it supposedly integrates into a predictably behaving solution. This is the big deal.

Regarding personal names specifically, this question may give you some practical guidance. People with full names over 70 UTF-8 characters are in trouble anyway.

这篇关于varchar(20)和varchar(50)相同吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！