Usage differences of char, varchar, and nvarchar Data Types in mysql

In mysql, the usage of char, varchar, and nvarchar data types is different.

In mysql, the usage of char, varchar, and nvarchar data types is different.

Note:
1. char:
A fixed length of non-Unicode characters. The maximum length is 8,000 characters.
2. varchar:
Variable-length non-Unicode data, which can contain a maximum of 8,000 characters.
3. nvarchar:
A variable-length Unicode data with a maximum length of 4,000 characters.
4. nchar
Unicode data of a fixed length. The maximum length is 4,000 characters.
5. char and varchar are both string types.
A string encoded in Unicode. The result is an integer of the character.

The following data structures are available:

Employee ID, name, Department
--------
1 Zhang San Finance
2 Li Si personnel
3 Wang Wu sales
........

When we define "name" as char (10) (static), we simply use php code:
Simple simulation of underlying data storage linked list $ data

The Code is as follows:
$ Col_num_len = 1; // The employee ID is 1 $ Col_name_len = 10; // The name length is 10. $ Col_unit_len = 4; // The Department length is 4 $ Col_len = $ col_num_len + $ col_name_len + $ col_unit_len + 3;

// Indicates the total length of each record, including three delimiters
The implementation is as follows:

The Code is as follows:

$ Data = "1 | Zhang San | finance | 2 | Li Si | personnel | 3 | Wang Wu | sales |..."; // simulate the underlying data storage linked list // Assume that the data of the "name" Field of the first 2nd records is queried. $ Record_start = $ col_len * 1 + 1; // obtain the starting position of row 2nd $ Record = substr ($ data, $ record_start, $ col_len); // get 2nd records $ Col_name_start = $ col_num_len + 2; // obtain the start position of the "name" Field $ Col_name = substr ($ record, $ col_name_start, $ col_name_len); // obtain the data of the "name" Field Echo $ col_name;

-----------

The Code is as follows:

// Assume that the "name" field data of the 2nd records is updated to "Li xiaosi" $ Update_info = "Li xiaosi "; $ Data = substr_replace ($ data, $ update_info, $ col_name_start, $ col_name_len); // update the field. The process ends. However, if we define the "name" field as varchar (10) (dynamic), it will be complicated: Note that the field storing "name" has no space, which is the storage difference between char and varchar. $ Col_num_len = 1; // The employee ID is 1 $ Col_name_len = 10; // The name length is 10. $ Col_unit_len = 4; // The Department length is 4 $ Col_len = $ col_num_len + $ col_name_len + $ col_unit_len + 3;

The implementation is as follows:

The Code is as follows:

// The starting position for dynamically storing data rows. The data is generated (re-generated) when the data is updated) $ Record_1_start = 1; $ record_1_name_dynamic_len = 4; // $ col_1_name_dynamic_len records the length of the "name" dynamic field $ Record_2_start = 13; $ record_2_name_dynamic_len = 4; $ Record_3_start = 26; $ record_3_name_dynamic_len = 6; ... $ Data = "1 | Zhang San | finance | 2 | Li Si | personnel | 3 | Wang Xiaoming | sales |... "; // simulate the underlying data storage linked list. Note that there is no space in the field storing" name ". // Assume that the data of the "name" Field of the first 2nd records is queried. $ Record_2_end = $ record_3_start-1; // gets the end position of row 2nd $ Record = substr ($ data, $ record_2_start, $ record_2_end); // obtain 2nd records $ Col_name_start = $ col_num_len + 2; // obtain the start position of the "name" Field $ Col_name = substr ($ record, $ col_name_start, $ record_2_name_dynamic_len); // obtain the data of the "name" Field Echo $ col_name; // Assume that the data in the "name" field updated in 2nd records is "Li xiaosi", which is much more complex than static data. $ Update_info = "Li xiaosi "; $ Update_len = strlen ($ update_info); // get the length of the updated content If ($ diff_len = $ update_len-$ record_2_name_dynamic_len) { $ Data = substr_replace ($ data, "", $ col_name_start, $ record_2_name_dynamic_len); // clear the original data $ Record_2_name_dynamic_len = $ update_len; // update the field length (and store new values) // Assume that the total number of records is n For ($ I = 2; $ I <= n; $ I ++) { $ {'Record _'. $ I. '_ start' }=$ {'record _'. $ I. '_ start'} + $ diff_len; // re-update the starting position of each row (and store new values). The system overhead is high (actually there are different solutions) } } $ Data = substr_replace ($ data, $ update_info, $ col_name_start, 0 );

In this article, "substr_replace" is used directly. When the data volume is large, the underlying implementation overhead is not small. In mysql, the result is a (Row Migration) phenomenon. We will not repeat it here.

Based on the above rough implementation:
1. The system overhead of the varchar type in the update phase is much larger than that of the char type.
2. The search performance between the two is comparable.
3. In the data volume ($ data) link between the two, char must be displayed greater than varchar.
4. When extracting large data volumes, the varchar disk IO consumption is lower, which means that the varchar comprehensive query performance is better.
5. No.

Conclusions In practical application (for example, in mysql ):
1. char is suitable for applications that frequently update fields.
2. varchar saves more disk space.
3. In actual application, a large amount of data (multiple rows) is returned, and the query performance of varchar is much better than that of char.
4. Selecting char and varchar will change the algorithm and storage method of the overall data structure. If the varchar field already exists in the mysql application, all other char fields will be stored as varchar.
5. No.

(The above algorithm is described simply in PHP. You are welcome to give me some suggestions)

Note: the original author of this article has been writing for a long time, so there are some differences between some places and the present, as shown in:

1. In the innodb engine, the implementation of char and varchar is no different, and the efficiency is not much different.
2. Selecting char and varchar does not change the algorithm and storage method of the overall data structure. (I remember this is a feature in MYSQL4. The old article on the Internet tells me that this feature is no longer available in MYSQL5)

Summary:
If the length of a text field is fixed, such as the ID card number, do not use varchar or nvarchar. char or nchar should be used.
For websites that support multiple languages, Unicode nchar or nvarchar data types should be considered to minimize character conversion issues.
If the length of a text field is not fixed, such as an address, varchar or nvarchar is used. In addition to saving storage space, it is more efficient to access the hard disk.