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

The following mainly brings you the string parsing of the mysql data type. I hope the string parsing of the mysql data type can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

Mysql data types-strings char (m) and varchar (m)

Char (m) fixed length string type non-Unicode character

Varchar (m) variable length string type non-Unicode data

Note: M is the maximum number of bytes that can be stored. The maximum number of characters stored is limited by specifying m. Char (20) and varchar (20) can only store up to 20 characters, and characters exceeding it will be truncated. M must be less than the maximum number of characters allowed for this type.

Type-char (m)

Char (m) maximum byte number 255fixed length type m can fill in blanks on the right to achieve the specified length. When the char value is retrieved, the trailing space is deleted (gender password)

The CHAR type is used for fixed-length strings and must be defined with a size modifier within parentheses. The range of this size modifier is 0-255.

Note: values larger than the specified length will be truncated, while values smaller than the specified length will be filled with spaces. So strings stored by the char type cannot have spaces at the end, and varchar is not subject to this restriction.

Two varchar (m) type

Varchar (m) maximum byte 65535 variable length m take 0-65535 only the number of characters needed to save, plus one byte to declare the length (username article title)

The VARCHAR type only uses the length actually required to store the string (adding an extra byte to store the length of the string itself) to store the value. Values larger than the specified length will be truncated.

Change one

The maximum length limit for the varchar type of the MySQL database is 255 in versions less than 4.1, and the data range can be 0x255 or 1x255 (depending on different versions of the database).

In versions above MySQL5.0, the length of the varchar data type is supported to 65535, that is, it can hold 65532 bytes of data, and the start and end bits occupy three bytes.

In other words, the data that needs to be stored in a fixed TEXT or BLOB format in version 4.1 or below can be stored in a variable-length varchar, which can effectively reduce the size of the database file.

Change two

The varchar type of MySQL database is less than 4.1. no matter whether it is a character or a Chinese character, nvarchar (which stores characters of Unicode data type) is saved as 2 bytes, which is generally used for input in Chinese or other languages, so it is not easy to garbled.

Varchar: Chinese characters are 2 bytes, other characters are stored as 1 byte, varchar is suitable for entering English and numbers.

Below version 4.0, varchar (20) refers to 20 bytes. If you store UTF8 Chinese characters, you can only store 6 characters (3 bytes each).

Version 5.0 and above, varchar (20), refers to 20 characters. No matter whether it is storing numbers, letters or UTF8 Chinese characters (3 bytes per Chinese character), it can store 20 characters, with a maximum size of 65532 bytes.

The maximum size of varchar (20) in Mysql4 is only 20 bytes, but the storage size of Mysql5 varies depending on the encoding, according to the following rules:

A) Storage restrictions

The varchar field stores the actual content separately from the clustered index, and the content begins with 1 to 2 bytes to represent the actual length (2 bytes are needed if the length exceeds 255), so the maximum length cannot exceed 65535.

B) Code length limit

If the character type is gbk, the maximum length of each character is 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 will be forcibly converted to text and warning will be generated.

C) Row length limit

It is the length of a row definition that causes the varchar length limit in practical applications. MySQL requires that the defined length of a row cannot exceed 65535. If the defined table length exceeds this value, prompt

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 .

Three differences:

1. Internal storage mechanism:

Char is of fixed length. Char (4), whether saving one character, two characters or four characters (in English), will take up 4 bytes, which is not enough to fill in with spaces.

Varchar is the actual number of characters stored + 1 byte (n255), so varchar (4), storing one character will take 2 bytes, 2 characters will take 3 bytes, and 4 characters will take 5 bytes.

String retrieval of 2.char type is faster than that of varchar type.

3. The choice of type

If the length of the text field is fixed, such as ID card number, do not use varchar or nvarchar, you should use char or nchar.

If the length of the text field is not fixed, such as address, you should use varchar or nvarchar. In addition to saving storage space, accessing the hard disk will also be more efficient.

4 performance selection

(1) the system overhead of varchar type in updating link is much higher than that of char type, so char is suitable for the application of frequent field updates.

(2) varchar saves more disk space.

(3) when extracting a large amount of data, the disk IO consumption of varchar is lower, which means that the comprehensive query performance of varchar will be better, so in practical applications, the query performance of varchar is much better than that of char.

(4) selecting char and varchar will change the algorithm and storage mode of the overall data structure. In mysql applications, if the varchar field already exists, then all other char fields will be stored as varchar.

Rules used in MySQL to determine whether data column type conversion is required

1. In a data table, if the length of each data column is fixed, then the length of each data row will also be fixed.

2. As long as there is a variable length of a data column in the data table, the length of each data row is variable.

3. If the length of a data row in a data table is variable, then, in order to save storage space, MySQL will convert the fixed-length data columns in the data table to the corresponding variable-length type.

Exception: char data columns less than 4 characters in length will not be converted to varchar type

Sites that support multiple languages should consider using Unicode nchar or nvarchar data types to minimize character conversion problems

In terms of efficiency, it is basically char > varchar > text, but if you are using Innodb engine, it is recommended to use varchar instead of char

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

For the above string parsing on the mysql data type, you do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.