改变mysql中varchar的长度有什么影响? [英] What's the impact of changing the length of a varchar in mysql?

问题描述

例如，如果我在 PHP 中有一个代码更新长度，通过更改表更改列并设置新长度，将新长度设置为有史以来最长的条目，如果我有 10000 条记录，这会产生很大影响吗?

For example if I had a code in PHP that updated the length, by alter table change column and setting the new length, to the longest entry there ever was would this affect greatly if I had 10000 records?

即使旧数据更短，MySQL 是否会为每个新大小的记录重新分配内存空间?

Does MySQL reallocate space in memory for each record of the new size even though the old data is shorter?

这个问题是针对 MySQL 的

This question is MySQL specific

推荐答案

varchar 列的长度固定为您在创建表时声明的长度.长度可以是 0 到 255(MySQL 5.0.3 之前)和 0 到 65,535(MySQL 5.0.3 及更高版本)之间的任何值.

The length of a varchar column is fixed to the length you declare when you create the table. The length can be any value from 0 to 255 (before MySQL 5.0.3) and from 0 to 65,535 (in MySQL 5.0.3 and later).

varchar 的存储是数据的字节加上一两个字节来声明字符串的长度.如果最大长度为 255 或更少，则仅添加 1 个字节的长度.

The storage of the varchar is the bytes of data with the addition of one or two bytes to declare the length of the string. If the maximum length is 255 or less then only 1 byte of length will be added.

如果您使用 alter table 并更改最大长度，那么如果定义的最大长度低于 255，则不会影响任何数据存储大小.如果您将最大长度增加到 255 以上，那么这取决于存储引擎是否强制255 以下的值是否为两个字节，在这种情况下，每行增加 1 个字节.

If you use alter table and change the maximum length then no data storage size will be affected if the maximum length defined is below 255. If you're increasing the maximum length above 255 then it's up to the storage engine if it forces two bytes or not for values below 255, in that case it will increase by 1 byte for each row.

char 类型与 varchar 不同，因为 char 总是使用所需的空间，所以如果你有 char(10) 和 varchar(10) 但只在其中存储hello"，char 将使用所有 10 个字节，vharchar 将保留6 个字节(hello 为 5，长度为 1)，因此更改 varchar 列的大小不会像 char 类型那样分配更多的存储空间.

The char type is different to varchar as char always uses the space required, so if you had char(10) and varchar(10) but only stored "hello" in each, char would use all 10 bytes, vharchar would hold 6 bytes (5 for hello and 1 for the length), therefore changing the size of varchar columns won't allocate more storage space like it would if it was a char type.

现在真正的问题是您为什么要让 PHP 操作 varchar 大小?您应该出于某种原因指定大小，如果您想要一个可以容纳大量文本(超过 65,535 字节)并且也是动态的可变长度字段，因此它只使用所需的最小空间，也许 TEXT 类型可能更适合您的情况?

The real question now is why would you want PHP to manipulate the varchar size? You should specify the size for a reason, if you want a variable length field that can hold a lot of text (more than 65,535 bytes) and also dynamic so it only uses the minimum space required maybe the TEXT types might be better for your situation?

这篇关于改变mysql中varchar的长度有什么影响?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！