Shulou(Shulou.com)05/31 Report--

This article is about the differences between varchar and char in Mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The difference between varchar and char in mysql and the meaning of 30 in varchar (30)

(1) the difference between varchar and char

Varchar stores variable length strings, requiring 1 extra byte when less than 255bytes (2 extra bytes greater than 255bytes), up to a maximum of 65532 bytes (sum of all columns)

Char stores fixed length (right padding), truncating trailing spaces when reading, with a maximum length of 255characters.

Both char and varchar are used to store strings, but they are saved differently. Char has a fixed length, while varchar is a variable-length character type.

Char is a fixed-length type and varchar is a variable-length type.

In the data column of type char (M), each value takes up M bytes, and if a length is less than M ~ (M), MySQL will fill it with a space character on the right. (those padded white space characters will be removed during the retrieval operation.) in varchar (M) type data columns, each value occupies just enough bytes plus a byte used to record its length (that is, the total length is 1 bytes).

Recommendations:

The myisam storage engine recommends using fixed-length data columns instead of variable-length data columns.

The memory storage engine currently uses fixed row storage, so it doesn't matter whether you use char,varchar columns.

The Innodb storage engine recommends using the varchar type.

The rule used in MySQL to determine whether a 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.

two。 As long as the length of one data column in the data table is variable, the length of each data row is variable.

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

For MyISAM tables, use char as much as possible, especially for myisam and isam tables that often need to be modified and easily fragmented. Its disadvantage is that it takes up disk space.

For InnoDB tables, because its data row internal storage format makes no distinction between fixed-length rows and variable-length rows (all rows share a header section that holds pointers to each relevant data column), using the char type is not necessarily better than using the varchar type. In fact, because the char type usually takes up more space than the varchar type, it is more advantageous to use the varchar type from the point of view of reducing the space footprint and reducing the disk iBank o.

(2) meaning of 30 in varchar (30)

Store up to 30 characters; varchar (5) and (200) store the same amount of space as hello, but the latter consumes more memory when sorting because order by col uses the same fixed_length col length (memory) engine.

For example, a varchar (255) column can hold a string with a maximum length of 255characters (characters instead of bytes), for latin1, the L of 'abcd' is 4, storage requires 5 bytes; for ucs2 (double-byte characters), 10 bytes of storage is required (the maximum length is 510 > 255, so an additional 2 bytes are required)

(3) the meaning of 20 in int (20)

20 indicates that the maximum display width is 20, but it still accounts for 4 bytes of storage, and the storage range remains the same.

Create table int_test (an int zerofill NOT NULL auto_increment, PRIMARY KEY (a))

Create table int_test_4 (an int (4) zerofill NOT NULL auto_increment, PRIMARY KEY (a))

Select * from int_test

+-+

| | a |

+-+

| | 0000000001 |

| | 0000000002 |

| | 0000000003 |

| | 2147483648 |

+-+

Select * from int_test_4

+-+

| | a |

+-+

| | 0001 |

| | 0002 |

| | 0003 |

| | 2147483648 |

+-+

(4) Why is MySQL designed in this way?

It doesn't make sense for most applications, just specify the number of characters that some tools use to display; int (1) and int (20) store and settle the same.

Thank you for reading! This is the end of the article on "what is the difference between varchar and char in Mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.