As far as I can tell, VARCHAR(255) does everything TINYTEXT can do. VARCHAR has the same storage size, it's part of the ISO standard, it allows default values other than null. I can't see any reason to ever use TINYTEXT. But it exists, so maybe there's a reason.
据我所知,VARCHAR(255)可以完成TINYTEXT所能做的一切。 VARCHAR具有相同的存储大小,它是ISO标准的一部分,它允许除null之外的默认值。我看不出任何使用TINYTEXT的理由。但它存在,所以也许是有原因的。
Is there any situation where TINYTEXT would be the preferred data type for size, speed, or other considerations?
是否有任何情况下TINYTEXT将成为尺寸,速度或其他考虑因素的首选数据类型?
2 个解决方案
#1
5
The only situation I can think of where TINYTEXT might be helpful is if you have really large rows. The contents of VARCHAR columns counts against the maximum row size of 65,535 bytes, but TEXT and BLOB data does not; a TINYTEXT column only adds 1 byte to the row size.
我能想到TINYTEXT可能有用的唯一情况是你有非常大的行。 VARCHAR列的内容计入最大行大小65,535字节,但TEXT和BLOB数据不计; TINYTEXT列只向行大小添加1个字节。
Actually, for InnoDB tables, things are somewhat more complicated. InnoDB storage is described in the following pages:
实际上,对于InnoDB表,事情有点复杂。 InnoDB存储在以下页面中描述:
https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic.html https://dev.mysql.com/doc/refman/5.7/en/innodb-compression-internals.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic .html https://dev.mysql.com/doc/refman/5.7/en/innodb-compression-internals.html
To summarize, the data stored in the part of the table subject to the above limit for TINYTEXT consists of a 1-byte length field and a 20-byte pointer to the external data, so a TINYTEXT adds 21 bytes to the row length. However, when an external value is less than 40 bytes, the data is stored inline in the row. So for a TINYTEXT less than 40 bytes, the storage is similar to VARCHAR(255), and it's all counted against the row length limit.
总而言之,存储在受TINYTEXT上述限制的表格部分中的数据由1字节长度字段和20字节指向外部数据的指针组成,因此TINYTEXT在行长度上增加了21个字节。但是,当外部值小于40个字节时,数据将内联存储在行中。因此,对于小于40字节的TINYTEXT,存储类似于VARCHAR(255),并且它全部计入行长度限制。
#2
3
TINYTEXT is essentially useless. It probably exists for consistency (4 sizes of TEXT and BLOB). TINYTEXT existed in MySQL long before VARCHAR could have more than 255 characters and before a character could be more than one byte.
TINYTEXT基本没用。它可能存在一致性(4种尺寸的TEXT和BLOB)。早在VARCHAR可能有超过255个字符之前和一个字符可能超过一个字节之前,TINYTEXT就存在于MySQL中。
TINYTEXT actually has disadvantages over VARCHAR. A complex SELECT may need to create a tmp table (eg, for ORDER BY); the first preference is to use MEMORY. The fallback is the less efficient MyISAM. Any size TEXT and BLOB forces going straight to MyISAM.
TINYTEXT实际上比VARCHAR有缺点。复杂的SELECT可能需要创建一个tmp表(例如,对于ORDER BY);第一个偏好是使用MEMORY。后备是效率较低的MyISAM。任何大小的TEXT和BLOB都会直接进入MyISAM。
A difference is that the number in VARCHAR is characters; the TEXT sizes are measured in bytes. A utf8 character can occupy up to 3 bytes.
区别在于VARCHAR中的数字是字符; TEXT大小以字节为单位。 utf8字符最多可占用3个字节。
Bottom line: Don't use TINYTEXT. (Or TINYBLOB, which can be replaced by VARBINARY(...).)
底线:不要使用TINYTEXT。 (或者TINYBLOB,可以用VARBINARY(...)代替。)
#1
5
The only situation I can think of where TINYTEXT might be helpful is if you have really large rows. The contents of VARCHAR columns counts against the maximum row size of 65,535 bytes, but TEXT and BLOB data does not; a TINYTEXT column only adds 1 byte to the row size.
我能想到TINYTEXT可能有用的唯一情况是你有非常大的行。 VARCHAR列的内容计入最大行大小65,535字节,但TEXT和BLOB数据不计; TINYTEXT列只向行大小添加1个字节。
Actually, for InnoDB tables, things are somewhat more complicated. InnoDB storage is described in the following pages:
实际上,对于InnoDB表,事情有点复杂。 InnoDB存储在以下页面中描述:
https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic.html https://dev.mysql.com/doc/refman/5.7/en/innodb-compression-internals.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html https://dev.mysql.com/doc/refman/5.7/en/innodb-row-format-dynamic .html https://dev.mysql.com/doc/refman/5.7/en/innodb-compression-internals.html
To summarize, the data stored in the part of the table subject to the above limit for TINYTEXT consists of a 1-byte length field and a 20-byte pointer to the external data, so a TINYTEXT adds 21 bytes to the row length. However, when an external value is less than 40 bytes, the data is stored inline in the row. So for a TINYTEXT less than 40 bytes, the storage is similar to VARCHAR(255), and it's all counted against the row length limit.
总而言之,存储在受TINYTEXT上述限制的表格部分中的数据由1字节长度字段和20字节指向外部数据的指针组成,因此TINYTEXT在行长度上增加了21个字节。但是,当外部值小于40个字节时,数据将内联存储在行中。因此,对于小于40字节的TINYTEXT,存储类似于VARCHAR(255),并且它全部计入行长度限制。
#2
3
TINYTEXT is essentially useless. It probably exists for consistency (4 sizes of TEXT and BLOB). TINYTEXT existed in MySQL long before VARCHAR could have more than 255 characters and before a character could be more than one byte.
TINYTEXT基本没用。它可能存在一致性(4种尺寸的TEXT和BLOB)。早在VARCHAR可能有超过255个字符之前和一个字符可能超过一个字节之前,TINYTEXT就存在于MySQL中。
TINYTEXT actually has disadvantages over VARCHAR. A complex SELECT may need to create a tmp table (eg, for ORDER BY); the first preference is to use MEMORY. The fallback is the less efficient MyISAM. Any size TEXT and BLOB forces going straight to MyISAM.
TINYTEXT实际上比VARCHAR有缺点。复杂的SELECT可能需要创建一个tmp表(例如,对于ORDER BY);第一个偏好是使用MEMORY。后备是效率较低的MyISAM。任何大小的TEXT和BLOB都会直接进入MyISAM。
A difference is that the number in VARCHAR is characters; the TEXT sizes are measured in bytes. A utf8 character can occupy up to 3 bytes.
区别在于VARCHAR中的数字是字符; TEXT大小以字节为单位。 utf8字符最多可占用3个字节。
Bottom line: Don't use TINYTEXT. (Or TINYBLOB, which can be replaced by VARBINARY(...).)
底线:不要使用TINYTEXT。 (或者TINYBLOB,可以用VARBINARY(...)代替。)