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

This article introduces the relevant knowledge of "what is the difference between varchar and char types in MySQL". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

VARCHAR Typ

The VARCHAR type is used to store variable-length strings and is the most common string data type. It saves more space than a fixed-length type because it uses only the necessary space (for example, shorter strings use less space). With one exception, if the MySQL table is created using ROW_FORMAT=FIXED, each row uses fixed-length storage, which is a waste of space.

VARCHAR needs to use 1 or 2 extra bytes to record the length of the string: if the maximum length of the column is less than or equal to 255 bytes, only 1 byte is used, otherwise 2 bytes are used. Assuming the latin1 character set, an VARCHAR (10) column requires 11 bytes of storage. The column of VARCHAR (1000) requires 1002 bytes because 2 bytes are required to store length information.

VARCHAR saves storage space, so it is also good for performance. However, because the row is longer, it may make the row longer than it used to be in UPDATE, which results in extra work. If the space occupied by a row grows and there is no more space to store on the page, different storage engines handle it differently. For example, MyISAM splits rows into different fragments to store, while InnoDB needs to split the page so that rows can be put into the page. Some other storage engines may never update data in the original data location.

Application of VARCHAR

VARCHAR is appropriate in the following situations:

The maximum length of a string column is much larger than the average length.

Column updates are rare, so fragmentation is not a problem

Complex character sets such as UTF-8 are used, and each character is stored with a different number of bytes

CHAR Typ

The CHAR type is fixed-length: MySQL always allocates enough space based on the defined string length. When storing the Char value, MySQL removes all trailing spaces. The CHAR value is populated with spaces as needed to facilitate comparison.

CHAR is suitable for storing very short strings, or all values are close to the same length. For example, CHAR is ideal for storing the MD5 value of a password because it is a fixed-length value. CHAR is also better than VARCHAR for data that changes frequently, because fixed-length CHAR types are not prone to fragmentation. For very short columns, CHAR is also more efficient in storage space than VARCHAR. For example, using CHAR (1) to store only Y and N values requires only one byte if you use a single-byte character set, but VARCHAR (1) requires two bytes because there is an extra byte of record length.

test

Here's an example to illustrate the difference in behavior between CHAR and VARCHAR. First, let's create a table with only one CHAR (10) field and insert some values into it:

CREATE TABLE char_test (char_col CHAR (10)); INSERT INTO char_test VALUES ('string1'). ('string2'). ('string3')

When we retrieve these values, we find that the space at the end of the string3 is truncated.

SELECT CONCAT ("', char_col,"'") FROM char_test

Execution result

If you use the VARCHAR (10) field to store the same value, you can get the following results:

CREATE TABLE varchar_test (varchar_col VARCHAR (10)); INSERT INTO varchar_test VALUES ('string1'). ('string2'). ('string3'); SELECT CONCAT ('', varchar_col,'') FROM varchar_test

Execution result

The difference between VARCHAR (5) and VARCHAR

If we use VARCHAR (5) and VARCHAR (200) to store 'hello', we know that the space overhead is the same. So can we keep the length of the VARCHAR large all the time? What are the advantages of using shorter columns?

It turns out to have a great advantage. Longer columns consume more memory because MySQL usually allocates fixed-size blocks of memory to hold internal values. It can be especially bad when sorting or manipulating with temporary tables in memory. It's just as bad when sorting using disk temporary tables.

So the best strategy is to allocate only the space you really need.

This is the end of the content of "what's the difference between varchar and char types in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.