varchar(255)vs tinytext/tinyblob和varchar(65535)vs blob/text [英] varchar(255) vs tinytext/tinyblob and varchar(65535) vs blob/text

问题描述

按定义:

VARCHAR:Length的范围是1到255个字符.除非指定BINARY关键字，否则以不区分大小写的方式对VARCHAR值进行排序和比较. x + 1个字节
TINYBLOB，TINYTEXT:BLOB或TEXT列，最大长度为255(2 ^ 8-1)个字符x + 1个字节

因此，基于此，我创建了下表:

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255),
  `lastname` tinytext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

还是创建varchar或tinytext并为什么?

是否相同:

VARCHAR:Length的范围是> 255个字符.除非指定BINARY关键字，否则以不区分大小写的方式对VARCHAR值进行排序和比较. x + 2字节
BLOB，TEXT BLOB或TEXT列，最大长度为65535(2 ^ 16-1)个字符x + 2字节

解决方案

从: http://www.pythian.com/news/7129/text-vs-varchar/

乍一看，看起来TEXT和VARCHAR可以存储相同的内容 信息.但是，两者之间存在根本差异 TEXT字段和VARCHAR字段的工作方式，这很重要 考虑在内.

标准 VARCHAR实际上是ISO SQL:2003标准的一部分； TEXT数据类型(包括TINYTEXT)是非标准的.

存储 TEXT数据类型存储为与表和包含它们的结果集不同的对象.该存储是透明的 —涉及TEXT字段的查询的方式没有区别 书面的与涉及VARCHAR字段的书面.由于未存储TEXT 作为一行的一部分，检索TEXT字段需要额外的[编辑 1/22]的内存开销.

最大VARCHAR长度:VARCHAR的最大行长度受表的最大行长度限制.这是65,535个字节 对于大多数存储引擎(NDB具有不同的最大行值). 理论上，VARCHAR的最大长度为65,536字节. 开销进一步限制了VARCHAR的实际最大大小.

如果VARCHAR，则存储VARCHAR字段的长度需要1个字节 字段的最大长度为0-255个字节；如果大于255 个字节，存储长度的开销为2个字节.如果是VARCHAR 字段允许使用NULL值，这会增加额外的开销-每个表 每组8个允许使用NULL的字段都使用1字节的开销 价值观.如果VARCHAR是表中的唯一行，并且不是 允许NULL值，VARCHAR允许的最大长度为65,532 字节.

请记住，VARCHAR(x)中的数字代表 字符，而不是字节数.因此，您可能会遇到困难 如果字符集尝试定义仅包含VARCHAR(65532)的表 使用多字节字符，例如UTF-8.

如果您尝试定义比允许的长度更长的VARCHAR值， 您将遇到诸如1118或1074之类的错误:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs.

ERROR 1074 (42000): Column length too big for column 'col_name' (max=[max number here]); use BLOB or TEXT instead

最大TEXT长度 TEXT数据类型的最大大小取决于所使用的TEXT数据类型.因为它们被存储 作为对象，表对象中唯一的行开销是指针(8 或16个字节).这是最大TEXT长度的列表， 开销(在TEXT对象中):

TINYTEXT – up to 255 bytes, 1 byte overhead

TEXT – up to 64 Kb, 2 bytes overhead

MEDIUMTEXT – up to 16 Mb, 3 bytes overhead

LONGTEXT – up to 4 Gb, 4 bytes overhead

默认值 MySQL不允许TEXT数据类型具有非NULL的默认值. VARCHAR字段允许为 用DEFAULT值创建的.

结论:由于存在存储问题，因此最好使用VARCHAR而不是TINYTEXT.

如果需要具有非NULL的DEFAULT值，则必须使用 VARCHAR(或CHAR).

如果您需要存储长于大约64 Kb的字符串，请使用 MEDIUMTEXT或LONGTEXT. VARCHAR不支持存储以下值 大.

确保您知道多字节字符集的影响. VARCHAR(255)存储255个字符，可能超过255个字节.

By definition:

VARCHAR: The range of Length is 1 to 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. x+1 bytes
TINYBLOB, TINYTEXT: A BLOB or TEXT column with a maximum length of 255 (2^8 - 1) characters x+1 bytes

So based on this, I creaate the following table:

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255),
  `lastname` tinytext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Or is it better to create a varchar or tinytext and why?

Is it the same for:

VARCHAR: The range of Length is > 255 characters. VARCHAR values are sorted and compared in case-insensitive fashion unless the BINARY keyword is given. x+2 bytes
BLOB, TEXT A BLOB or TEXT column with a maximum length of 65535 (2^16 - 1) characters x+2 bytes

解决方案

FROM: http://www.pythian.com/news/7129/text-vs-varchar/

On first glance, it looks like TEXT and VARCHAR can store the same information. However, there are fundamental differences between the way TEXT fields and VARCHAR fields work, which are important to take into consideration.

Standard VARCHAR is actually part of the ISO SQL:2003 standard; The TEXT data types, including TINYTEXT, are non-standard.

Storage TEXT data types are stored as separate objects from the tables and result sets that contain them. This storage is transparent — there is no difference in how a query involving a TEXT field is written versus one involving a VARCHAR field. Since TEXT is not stored as part of a row, retrieval of TEXT fields requires extra [edited 1/22] memory overhead.

Maximum VARCHAR length The maximum row length of a VARCHAR is restricted by the maximum row length of a table. This is 65,535 bytes for most storage engines (NDB has a different maximum row value). Theoretically the maximum length of a VARCHAR is 65,536 bytes. Overhead further limits the actual maximum size of a VARCHAR.

Storing the length of a VARCHAR field takes 1 byte if the VARCHAR field has a maximum length of 0-255 bytes; if it is greater than 255 bytes, the overhead to store the length is 2 bytes. If the VARCHAR field allows NULL values, that adds additional overhead — every table uses 1 byte of overhead for each set of 8 fields that allow NULL values. If the VARCHAR is the only row in the table, and does not allow NULL values, the maximum length allowed for VARCHAR is 65,532 bytes.

Keep in mind that that the number in VARCHAR(x) represents number of characters, not number of bytes. Therefore, you may have difficulties trying to define a table with only VARCHAR(65532) if the character set uses multi-byte characters, such as UTF-8.

If you attempt to define a VARCHAR value that is longer than allowed, you will run into an error such as 1118 or 1074:

ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs.

ERROR 1074 (42000): Column length too big for column 'col_name' (max=[max number here]); use BLOB or TEXT instead

Maximum TEXT length The maximum size of a TEXT data type depends on which type of TEXT data type is being used. Because they are stored as objects, the only row overhead in the table object is a pointer (8 or 16 bytes). Here is a list of the maximum TEXT length, and the overhead (in the TEXT object):

TINYTEXT – up to 255 bytes, 1 byte overhead

TEXT – up to 64 Kb, 2 bytes overhead

MEDIUMTEXT – up to 16 Mb, 3 bytes overhead

LONGTEXT – up to 4 Gb, 4 bytes overhead

DEFAULT values MySQL does not allow TEXT data types to have a default value other than NULL. VARCHAR fields are allowed to be created with a DEFAULT value.

Conclusions Because of the storage implications, it is preferable to use VARCHAR instead of TINYTEXT.

If you need to have a DEFAULT value that is not NULL, you must use VARCHAR (or CHAR).

If you need to store strings longer than approximately 64 Kb, use MEDIUMTEXT or LONGTEXT. VARCHAR cannot support storing values that large.

Make sure you are aware of the effects of a multi-byte character set. VARCHAR(255) stores 255 characters, which may be more than 255 bytes.

这篇关于varchar(255)vs tinytext/tinyblob和varchar(65535)vs blob/text的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！