MySQL Database selection comparison of char and varchar field type lengths

There are a lot of related to char and varchar on the Internet, but all have a long history, here reprint a piece of information relatively new, personally think to my design field decision help very much.

Modern databases generally support char and varchar character field types, char is used to hold fixed-length characters, the size of the storage space is defined by the length of the field, regardless of the actual character length, when the entered character is less than the length of the definition will be the final complement. varchar is used to preserve variable length characters, the size of the storage space in the database is the actual character length, and does not make up the sky like Char, which takes up less space.

From the above characteristics, varchar than char has a distinct advantage, so most database design should be used varchar type. So why do you need char type, personally think there are several reasons:

1, in order to be compatible with the previous version of the database, because the database only supported the char type, some applications of the business logic is only for the char type design, so the database software also retains the char type.

2, the char type is fixed long, some database can not store the field length information in each record, this can save partial space, also can make some memory alignment to improve performance, but personally think this brings the performance improvement very small, at least the Oracle database is meaningless.

3, there is also the argument that some data is often modified, the length may change, will cause the fragment, uses the char not to produce the fragment, this saying is more, but I thought since the length will change, that uses the varchar to be able to save the memory and the storage space to enhance the performance, as long as the data block reserves the space to have no problem, The use of varchar performance is better.

For Oracle databases, I can't find enough reason to use the char type, and char also brings nasty spaces, and some articles say that MySQL's MyISAM storage engine is better than varchar in the case of a fixed length, which has not been tested and understood.

Since varchar is a variable-length storage, many people will have questions, such as the Status field definition varchar (10) and varchar (1000) What is the difference, anyway, is longer, storage space is the same, after the province to be lengthened and change the definition of the field. Here's what I understand:

1, the length of the database is a constraint, can ensure that the data entered the database to meet the length requirements, the definition of a reasonable length of the field can reduce a part of illegal data entry, such as: Our business status only ' NEW ', ' DELETE ', ' close ' 3 states, using varchar ( 5 save, this can effectively reduce illegal data entry, the definition of reasonable length can also make it easy to understand the use of the field, imagine if all of your character field length is varchar (4000) will be what kind of situation.

2, varchar field length Although not much impact on the data store, but there are some subtle differences in the database, such as MySQL defined in length if less than 255, the field length in 1 bytes, if more than 255, the length of the field will be fixed in 2 bytes. If your business data has a maximum length of only 10, but defines a length of 256, each record will waste a byte to store the length. Oracle does not have such a problem, it will dynamically select the length identification based on the actual length of each record field.

3, the length of the field definition also has a greater impact on the index. Oracle has a certain limit on index length, 8i Official document description A single record index information can not exceed the length of the block size of the 40%,9i is 75%, in fact, can see Jametong http://www.dbthink.com/?p= 20 This document, which has detailed test results. If your block size is 8K, then the index field definition can not be more than 6398, for example, you want to give the table 2 varchar (4000) Word Jianjian combined index, create a direct error. Another index organization table and online rebuild index (because the middle will temporarily create an index organization table) allows the index information length is smaller, only 40% of the block size, the actual 8K block size, to use the online reconstruction index, the definition of length can not exceed 3215. As you can see from the above, the data block size of 8K, when the design of the field to be defined as varchar (4000), then this field can not consider indexing, because even if you can not do online redefinition operations, the DBA to do index maintenance can only stop the application, This will have a significant impact on the availability of the system. The script for the Oracle index length limit test is as follows:

[SQL] View plaincopy

Sql> CREATE TABLE Test1

2 (

3 C1 varchar2 (4000),

4 C2 varchar2 (4000),

5 C3 varchar2 (4000)

6)

7;

Table created

Sql> CREATE index Test1_ind1 on TEST1 (C1);

Index created

sql> ALTER index TEST1_IND1 rebuild online;

Alter index TEST1_IND1 rebuild online

Ora-00604:error occurred at recursive SQL level 1

Ora-01450:maximum key Length (3215) exceeded

Sql> CREATE index Test1_ind2 on TEST1 (C2, C3);

Create INDEX Test1_ind2 on TEST1 (C2, C3)

Ora-01450:maximum key Length (6398) exceeded

Sql>

There are some special rules about Oracle's index length, such as the custom function returns a character definition length of 4000, so you need to pay special attention to using the custom function to do the function index, which may affect the online rebuilding index cannot operate.

The index length of the built-in function is determined by the function, such as upper, which does not change the length is the length of the index field definition, substr this change in length depends on the length of the function intercept.

The Number Type field has a fixed length of 22.

The length of the data type field is fixed to 7.

Indexes are ascending by default, and if the index length to be built in descending order is the field definition length *1.5+1.

MySQL is more complex to index length limitations, each version and storage engine is different, the following is the results of the MYSQL5.1.58 test:

The maximum total length of the InnoDB is 3072 bytes, the single character field is 767 bytes, and the first 767 characters are automatically intercepted if the field length is greater than 767.

The maximum total length of the MyISAM is 1000 bytes, and the single character field is 1000 bytes.

The maximum total length of the memory is 3072 bytes, and the single character field is 3072 bytes.

4, the length of the variable long field definition will not affect the size of the server data space, but the memory of the client has an impact, because the client in SQL from the database reading data, first take the length of the field definition, and then allocate enough memory, that is, if you define a field length of 1K, The actual length is 10 bytes, and to take the 1K record, the client allocates 1MB of memory, but only the 10K valid data is saved. This will be a more serious waste of client memory. In particular, some high concurrency or a large number of data scenarios, easy to generate memory overflow.

5, about the field length alignment problem, some designers like to define the length of a field of 4 or 8 multiples, such as 16,32,64,128, the reason is that can do memory alignment, for this problem I did not in-depth analysis, personally think the necessity is not large, also did not see this optimization can improve the performance of the case. If a varchar (1) is defined as varchar (4) Instead of wasting memory and storage, I actually see all character type data stored in a large char[in the Oracle JDBC driver, putting all number and date types in another char[] , it is not clear how to align the memory after this integration.

To sum up: varchar Type field length can not be arbitrarily defined, not the larger the better, or need to be based on the actual business data to define a suitable length. I personally define the actual length for some of the lengths that can be fully estimated, such as years, status, tags, and so on. Define a reasonable value for information such as VARCHAR, VARCHAR (30) for uncertain length business data such as name, style, and so on. For descriptive or informational information, these fields are also determined not to have an index, and the length is unpredictable, so stay in longer lengths to avoid constant length adjustments, such as varchar (1024), or direct VARCHAR2 (4000).