A question about the varchar in MySQL

Why do many people like to use VARCHAR (255) in MySQL?

Reply content:

Why do many people like to use VARCHAR (255) in MySQL?

1. Historical reasons

varchar only supports 0-255byte before MySQL 5.0.3, only after MySQL 5.0.3 is supported to 0-65535byte

Here 255 is the length of the character

2, the maximum length of varchar

Look at a piece of code

Mysql> Show variables like '%col% '; +---------------------------+-----------------+| variable_name | Value |+---------------------------+-----------------+| collation_connection | Utf8_general_ci | | Collation_database | Utf8_general_ci | | Collation_server | Utf8_general_ci | | protocol_version | 10 | | Slave_compressed_protocol |  OFF |+---------------------------+-----------------+5 rows in Set (0.00 sec) mysql> Show Char set; +----------+-----------------------------+---------------------+--------+| Charset | Description | Default Collation | MaxLen |+----------+-----------------------------+---------------------+--------+| Big5 | Big5 Traditional Chinese |      Big5_chinese_ci | 2 | | Dec8 | DEC West European |      Dec8_swedish_ci | 1 | | cp850 | DOS West European |      Cp850_general_ci | 1 | | HP8 | HP West European | Hp8_englIsh_ci | 1 | | koi8r | Koi8-r relcom Russian |      Koi8r_general_ci | 1 | | Latin1 | cp1252 West European |      Latin1_swedish_ci | 1 | | latin2 | ISO 8859-2 Central European |      Latin2_general_ci | 1 | | Swe7 | 7bit Swedish |      Swe7_swedish_ci | 1 | | ASCII | US ASCII |      Ascii_general_ci | 1 | | Ujis | EUC-JP Japanese |      Ujis_japanese_ci | 3 | | Sjis | Shift-jis Japanese |      Sjis_japanese_ci | 2 | | Hebrew | ISO 8859-8 Hebrew |      Hebrew_general_ci | 1 | | tis620 | TIS620 Thai |      Tis620_thai_ci | 1 | | Euckr | EUC-KR Korean |      Euckr_korean_ci | 2 | | koi8u | Koi8-u Ukrainian |      Koi8u_general_ci | 1 | | gb2312 | GB2312 Simplified Chinese |      Gb2312_chinese_ci | 2 | | Greek | ISO 8859-7 Greek |      Greek_general_ci | 1 | | cp1250 | Windows CentralEuropean |      Cp1250_general_ci | 1 | | GBK | GBK Simplified Chinese |      Gbk_chinese_ci | 2 | | Latin5 | ISO 8859-9 Turkish |      Latin5_turkish_ci | 1 | | Armscii8 | ARMSCII-8 Armenian |      Armscii8_general_ci | 1 | | UTF8 | UTF-8 Unicode |      Utf8_general_ci | 3 | | UCS2 | UCS-2 Unicode |      Ucs2_general_ci | 2 | | cp866 | DOS Russian |      Cp866_general_ci | 1 | | KEYBCS2 | DOS Kamenicky Czech-slovak |      Keybcs2_general_ci | 1 | | Macce | Mac Central European |      Macce_general_ci | 1 | | Macroman | Mac West European |      Macroman_general_ci | 1 | | cp852 | DOS Central European |      Cp852_general_ci | 1 | | latin7 | ISO 8859-13 Baltic |      Latin7_general_ci | 1 | | UTF8MB4 | UTF-8 Unicode |      Utf8mb4_general_ci | 4 | | cp1251 | Windows Cyrillic |      Cp1251_general_ci | 1 | | Utf16    | UTF-16 Unicode |      Utf16_general_ci | 4 | | Utf16le | Utf-16le Unicode |      Utf16le_general_ci | 4 | | cp1256 | Windows Arabic |      Cp1256_general_ci | 1 | | cp1257 | Windows Baltic |      Cp1257_general_ci | 1 | | Utf32 | UTF-32 Unicode |      Utf32_general_ci | 4 | | binary | Binary Pseudo CharSet |      binary | 1 | | Geostd8 | GEOSTD8 Georgian |      Geostd8_general_ci | 1 | | cp932 | SJIS for Windows Japanese |      Cp932_japanese_ci | 2 | | Eucjpms | Ujis for Windows Japanese |      Eucjpms_japanese_ci | 3 |+----------+-----------------------------+---------------------+--------+40 rows in Set (0.00 sec) mysql> Create Database testdbx DEFAULT CHARACTER SET latin1;mysql> use testdb;database changedmysql> use testdbx;database changed Mysql> drop table if exists testa;create table testa (name varchar (65532));mysql> drop table if exists tesTa;create table Testa (name varchar (65533)); Query OK, 0 rows affected (0.01 sec) ERROR 1118 (42000): 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. Some columns to TEXT or blobsmysql> drop table if exists testa;create table testa (name varchar (655 ) not NULL);

Summarized as follows

MySQL's Vachar field type, although the maximum length is 65535, but is not able to save so much data, up to 65533 (do not allow non-empty fields when), when the non-empty fields are allowed to only 65532

3. VarChar Physical storage

On physical storage, varchar uses 1 to 2 extra bytes to represent the actual stored string length (bytes). If the maximum length of a column is less than 256 bytes, it is represented (identified) in a single byte. If the maximum length is greater than or equal to 256, two bytes are used.

When the character set selected is Latin1, a character occupies a byte

varchar (255) stores a character that stores the actual data length and data values of the data using a 2bytes physical space.

VARCHAR (256) stores a character and uses 2bytes to represent the actual data length, which requires 3bytes of physical storage space altogether.

VARCHAR has no physical storage method for different RDBMS engines, although it has a unified logical meaning. For MySQL's different storage engine, its implementation method and data physical storage method is also different.

4. VarChar in InnoDB

The physical storage of varchar in INNODB is related to the Innodb_file_format used by InnoDB.

• Early Innodb_file_forma = antelope; support for redundant and compact two kinds of Row_format

• 5.5 Start or InnoDB1.1, you can use a new file format = Barracuda;barracuda compatible redundant, plus support for dynamic and compressed two kinds of row_format

when Innodb_file_format=antelope, row_format=redundant or compact. InnoDB's clustered index (cluster index) stores only the first 768 bytes of the varchar, text, and Blob fields, and the extra bytes are stored in a separate overflow page, which is also known as Off-page. The 768-byte prefix is followed by a 20-byte pointer, pointing to the location of overflow pages.

In addition, in the case of Innodb_file_format=antelope, you can store up to 10 large segments in InnoDB (with Off-page storage). The default page size for innodbd cannot exceed 16k/2=8k bytes, (768+20) *10 < 8k for the length of 16kb,innodb lines.

when Innodb_file_format=barracuda, row_format=dynamic or compressed

All varchar, text, and BLOB field data in InnoDB are stored completely off-page, depending on the length of the field and the total length of the entire line. For off-page stored columns, cluster index only stores a 20-byte pointer to the actual overflow page storage location. If the length of a single line is too large to fit fully cluster index PAGE,INNODB will select the longest column as the Off-page store until the length of the row can be adapted to the cluster index page.

5. VarChar in MyISAM

For the MyISAM engine, all data in the varchar field is stored in the data row (In-line). The Row_format of the MyISAM table also affects the physical storage behavior of varchar.

MyISAM's Row_format can be set to fixed and dynamic via the Create or ALTER SQL statement. You can also generate Row_format=compresse storage formats by Myisampack.

When there is no text or blob Type field in the MyISAM table, the Row_format can be set to fixed (or dynamic), otherwise it can only be dynamic.

When there is a varchar field in the table, the Row_format can be set to either fixed or dynamic. Use row_format=fixed to store varchar field data, waste storage space, and varchar will fix long storage at this time. The physical implementation of the Row_format for fixed and Dynamic,varchar is also different (you can view the source code files Field.h and field.cc), so MyISAM Row_ When format transitions between fixed and dynamic, the physical storage of the varchar field will also change.

Summary:

1. Storage 2^8 = 256/overflow Pages/historical reasons

3. VarChar is not necessarily slower than Char

3, if there is a large paragraph using text, please remove the table

4, varchar index, the front 20 characters can be

5, actually how to use also how to use varchar (30), Vachar (300) and so on

Resources:
Http://dev.mysql.com/doc/refman/5.5/en/column-count-limit.html

Anyway, varchar is variable-length, occupies less space than char (), and the maximum is 255 characters (English or Chinese specifically see the character set)

Get used to it, the old system has a 256-byte upper limit, many people have formed this habit. The new system upper limit is much larger than this, so it is recommended to be honest in the new system on the integer count is good, you want the user to enter a character not more than 300 write 300,200 write 200, there is no need to get a 255, make the user very dazed.

