VarChar length setting method in Mysql _mysql

If a varchar is set in an item (50)

Well, that's 50 for English.

So what about Chinese?

Utf-8 Chinese occupies 3 bytes

So, is this varchar (50) Only save 16 characters?

MySQL varchar (50) in both Chinese and English, there are 50.

MySQL5 document that describes the varchar field type: varchar (m) variable length string. M represents the maximum column length. The range of M is 0 to 65,535. (The maximum actual length of the varchar is determined by the longest row size and the character set used, with a maximum effective length of 65,532 bytes).

Why does it change so? It really feels like the MySQL manual is too unfriendly, because you have to read it carefully before you find this description: MySQL 5.1 complies with the standard SQL specification and does not remove trailing spaces from the varchar value. varchar the prefix + data with a byte or two byte long when saving. If the length of the varchar column declaration is greater than 255, the length prefix is two bytes.

Well, it seems to understand a little. But he said the length of more than 255 when the use of 2 byte length prefix, primary subtraction: 65535-2 = 65533 AH. I do not know how to calculate these Daniel, leave aside the question?

Note: I tested the use of UTF8 encoding, the maximum length of varchar is 21854 bytes.

In MySQL version 5.0.45, UTF8 is tested under the database encoding: varchar is defined as the maximum length of 21785. In other words, no matter the letter, the number, the Chinese character, can only put 21,785.

Supposition: The varchar byte maximum 65535,utf8 encodes a character 3 byte 65535/3=21785.

Supplementary knowledge:

1.varchar Types of Changes

The varchar type of the MySQL database has a maximum length limit of 255 in the version below 4.1, and its data range can be 0~255 or 1~255 (based on different versions of the database). In versions above MySQL5.0, the length of the varchar data type is supported to 65535, that is, 65,532 bytes of data can be stored, and the start and end bits take up 3 bytes, i.e. Data that needs to be stored in a fixed text or BLOB format in version 4.1 or below can be stored with a variable length of varchar, which can effectively reduce the size of the database file.

The varchar type of the MySQL database is in the version below 4.1, nvarchar (the character that stores the Unicode data type), whether it is a character or a Chinese character, is stored as 2 bytes, generally used in Chinese or other language input, so it is not easy to garbled; varchar: The Chinese character is 2 bytes, the other word Fu Cun is 1 bytes, varchar is suitable for inputting English and numeral.

4.0 below, varchar (20), refers to 20 bytes, if stored UTF8 Chinese characters, can only save 6 (each Chinese character 3 bytes), 5.0 version, varchar (20), refers to 20 characters, regardless of the number, Letters or UTF8 Chinese characters (3 bytes per character), can be stored in 20, the maximum size is 65532 bytes, varchar (20) in the MYSQL4 is the largest is only 20 bytes, but MYSQL5 depending on the encoding, the storage size is different, the following rules:

A) storage limits

The varchar field is to store the actual content separately from the clustered index, where the content begins with 1 to 2 bytes representing the actual length (2 bytes in length over 255), so the maximum length cannot exceed 65535.

b Coding Length Limit

If the character type is GBK, each character is up to 2 bytes and the maximum length cannot exceed 32766;

If the character type is UTF8, each character is up to 3 bytes, and the maximum length cannot exceed 21845.

If the definition exceeds the above limit, the varchar field is forcibly converted to the text type and produces warning.

c) The length of the limit

The length of a row definition is the limit of the varchar length in the actual application. MySQL requires a row to have a defined length of not more than 65535. If the defined table length exceeds this value, the prompt

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

2.CHAR (M), VARCHAR (m) different

The length of the column defined by CHAR (m) is fixed, and M can be between 0~255, and when the char value is saved, a space is padded to the right of the value to reach the specified length. When a char value is retrieved, the trailing space is removed. No case conversion is made during storage or retrieval. Char stores fixed-length data is convenient, char field index efficiency level, such as the definition of char (10), then whether you store data to reach 10 bytes, will take up 10 bytes of space, insufficient automatically filled with space.

The length of the column defined by VARCHAR (m) is a variable-length string, and the M value can be between 0~65535, (the maximum valid length of the VARCHAR is determined by the maximum row size and the character set used). The overall maximum length is 65,532 bytes). The varchar value saves only the number of characters required, plus a single byte to record the length (two bytes if the length of the column declaration exceeds 255). The varchar value is saved without padding. The trailing blanks are still retained when the value is saved and retrieved, conforming to standard SQL. VARCHAR store variable length data, but storage efficiency is not high char. If the possible value of a field is an unfixed length, we only know that it cannot exceed 10 characters, and it is most cost-effective to define it as VARCHAR (10). The actual length of the varchar type is the actual length of its value +1. Why "+1"? This byte is used to save how much length is actually used. Consider from the space, use varchar suitable, consider from the efficiency, use char suitable, the key is according to the actual situation to find the tradeoff point.

The biggest difference between char and varchar is that one is fixed length and one is variable length. Because it is a variable length, the actual string is actually stored, plus a byte of the length of the record string (two bytes if more than 255 is required). If the value assigned to a char or varchar column exceeds the maximum length of the column, the value is cropped to fit. If the character being trimmed is not a space, a warning is generated. If you crop a non-space character, it causes an error (not a warning) and disables the insertion of the value by using strict SQL mode.

3. Differences between varchar and text, BLOB types

The Varchar,blob and text types are variable-length types, and their storage requirements depend on the actual length of the column value (expressed in the previous table with L), not on the maximum possible size of the type. For example, a varchar (10) column can hold a string with a maximum length of 10 characters, and the actual storage need is the length of the string, plus 1 bytes to record the length of the string. For the string ' ABCD ', L is 4 and the storage requirement is 5 bytes.

The blob and text types require 1,2,3 or 4 bytes to record the length of the column value, depending on the maximum possible length of the type. varchar need to define size, with a maximum limit of 65535 bytes, and text is not required. If you assign a value that exceeds the maximum length of a column type to a BLOB or text column, the value is truncated to fit it.

A blob is a large binary object that can hold a variable number of data. 4 blob types Tinyblob, blobs, Mediumblob, and Longblob differ only in terms of the maximum length at which they can hold the value.

BLOBs can store pictures, text is no good, text can only store plain text files. 4 text types Tinytext, text, Mediumtext, and Longtext correspond to 4 blob types and have the same maximum length and storage requirements. The only difference between a blob and a text type is that the sort and comparison of BLOB values is done in a case-sensitive manner, while the text value is not case sensitive. In other words, a text is a case insensitive blob.

4. Summarizing Char,varchar,text Differences

The difference in length, char range is 0~255,varchar maximum is 64k, but note that the 64k here is the length of the entire row, to consider the other column, and if there is not NULL will occupy a bit, for different character sets, effective length is not the same, For example, the UTF8, up to 21845, but also to remove the other column, but varchar in general the storage is enough. If you encounter a large text, consider using text, maximum to 4G.

Efficiency is basically char>varchar>text, but if you're using a InnoDB engine, it's recommended to use varchar instead of char.

Char and varchar can have default values, text cannot specify default values

It is necessary for the database to choose the appropriate data type storage, which has a certain impact on performance. Here in the fragmentary record two pens, for type int, if you do not need to access negative values, it is best to add unsigned; For fields that often appear in the Where statement, consider indexing, which is especially appropriate for indexing.

The above is a small series to introduce the MySQL varchar length set method, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!