Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief account of the specific differences between varchar and text in mysql. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on the specific differences between varchar and text in mysql can bring you some practical help.

Size comparison

VARCHAR:varchar must satisfy the maximum line width limit in mysql, that is, 65535 (64k) bytes, while varchar itself is defined by the number of strings. One character of uft-8 character set occupies three bytes in mysql, so the actual maximum length of single table varchar is as follows.

1. The maximum length of varchar using the utf-8 character encoding set is (65535-2) / 3 characters 21844 characters (more than 255bytes will have an additional footprint overhead of 2 bytes, so minus 2, or minus 1 if it is less than 255).

two。 Using the utf-8mb4 character set, using the utf-8mb4 character set in mysql takes up 4 bytes, so the maximum length of the varchar is (65535-2) / 4 bytes 16383 characters (more than 255bytes will have an additional footprint of 2 bytes, so minus 2, or minus 1 if it is below 255).

Note: if you use the utf-8mb4 character set, when you need to store utf-8 characters, you will still occupy only 3 bytes, so sometimes you can save more characters than this calculated value, because utf-8mb4 is a superset of utf-8.

TEXT: the maximum limit is 64k bytes, but the essence is overflow storage. By default, innodb will only store the first 768 bytes in the data page, and the rest of the data will be stored in the overflow section. Although it is also limited by the maximum row width of a single table 65535, each BLOB and TEXT column in the mysql table actually accounts for only 5 to 9 bytes, and other parts will be overflowed. So the maximum row width of the actual occupied table is 9 / 2 bytes, plus extra overhead, which has nothing to do with the actual width of the table.

1. If you use the utf-8 character set, the maximum length of a single field is 21844 characters.

two。 However, a single table can set multiple text fields, which breaks through the limit of 65535 of the maximum row width of a single table.

Note: if the new row format type Barracuda (barracuda) is adopted, the file format has two new row formats: compressed and dynamic, which completely overflow the blob/text field, with only 20 bytes in the data page and the rest in the overflow section.

Other text:

Text fields are divided into long, medium and short types, unlike varchar, there is only one, in addition to the above text, there are the following three.

TinyText: the maximum length is 255 bytes, which is actually a meaningless type.

MEDIUMTEXT: the maximum length is limited to 16m bytes. Overflow storage is also supported like normal text, so the maximum row width of the actual occupied table is 9 bytes, plus additional overhead.

LONGTEXT: the maximum length is limited to 4G bytes. Overflow storage is also supported like normal text, so the maximum row width of the actual occupied table is 9 bytes 4 bytes, plus additional overhead

-

Example:

# VARCHAR single form field length cannot exceed 21844CREATE TABLE test (va VARCHAR (21845)) DEFAULT CHARSET=utf8; [Err] 1118-Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs# this will do CREATE TABLE test (va VARCHAR (21844)) DEFAULT CHARSET=utf8; affected lines: 0 time: 0.155s# although each BLOB and TEXT column account accounts for only 5 to 9 bytes. But not enough for CREATE TABLE test (va VARCHAR (21841), tx text) DEFAULT CHARSET=utf8; [Err] 1118-Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs# then 9: 2 will be fine. CREATE TABLE test (va VARCHAR (21840), tx text) DEFAULT CHARSET=utf8; affected lines: 0 time: 0.170s

Additional footprint overhead description:

Varchar is less than 255byte 1byte overhead

Varchar is greater than 255byte 2byte overhead

Tinytext 0-2551 byte overhead

Text 0-65535 byte 2 byte overhead

Mediumtext 0-16m 3 byte overhead

Longtext 0-4Gb 4byte overhead

Note:

Although the text field will overflow most of the data more than 768 bytes to other space on the hard disk, it seems to increase the pressure on the disk. However, in terms of processing form, when varchar is greater than 768 bytes, there is not much difference between storage and text. Because overlong varchar will also use overflow storage, to read the row is to read the hard disk and load it into memory, which is basically the same.

In addition, from the 8000byte point to explain, mysql innodb data page default a data page is 16K, to save two rows of data, so for varcahr, text if a row of data does not exceed 8000byte, overflow will not be saved to another page.

-

Differences:

Text field, MySQL does not allow default values. To build an index, you must give a prefix index length.

Varchar allows default values and has no limit on index length

Note:

The default length of the InnoDB engine single-field index is 767 bytes, and the myisam is 1000 bytes. For example, if the character encoding is utf8, then the maximum index length of varchar is 256 characters. Exceeding the limit results in unsuccessful index creation and requires prefix index creation instead. Setting innodb_large_prefix=1 increases the limit to allow indexes to use dynamic compression, but the row_format of the table must be compressed or dynamic. You can make the index column length greater than 767bytes, but the total length cannot be greater than 3072 bytes.

-

Summary:

According to the implementation of storage: you can consider using varchar instead of text, because varchar storage is more flexible and has higher performance if you store less data.

If you need a non-empty default value, you must use varchar

If the stored data is greater than 64K, you must use mediumtext, longtext, because varchar can no longer store it.

If the storage mechanism of varchar (255 +) is the same as that of text, the performance is about the same.

It is important to note that varchar (255) is not just 255byte, but is actually more likely to take up more.

Mysql varchar and text specific differences will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.