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

This article will explain in detail the difference between char and varchar in mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

The varchar type, which is used to store variable-length strings, is a common string data type.

Varchar saves more space when the stored string is longer. Because the varchar is longer, when using update, the line may become longer than it used to be, which results in extra work.

If a row takes up more space and there is no extra space to store in the page, innoDB's storage engine needs to split the page so that the row can be put into the page.

The char type is used to store strings of fixed length.

When storing data, MySQL will delete all the spaces at the end of the text, so even if you store: 'abc', notice that there is a space at the end of the string.

The difference between char and varchar data types in mysql:

The length of 1.CHAR is fixed, while the length of VARCHAR2 can be changed. For example, the storage string "abc" means that the characters you store will account for 10 bytes (including 7 empty characters), while the same VARCHAR2 (10) will only take up 3 bytes, with 10 being the maximum. When you store characters less than 10:00, store them according to the actual length.

2.CHAR is slightly more efficient than VARCHAR2.

At present, VARCHAR is synonymous with VARCHAR2. The industry-standard VARCHAR type can store empty strings, but oracle does not, although it reserves the right to do so later. Oracle has developed its own data type VARCHAR2, which is not a standard VARCHAR, which changes the feature that varchar columns can store empty strings in the database to null values. If you want backward compatibility, Oracle recommends using VARCHAR2 instead of VARCHAR.

When should I use CHAR and when should I use varchar2?

CHAR and VARCHAR2 are a pair of contradictory unity, and they complement each other.

VARCHAR2 saves more space than CHAR, and is slightly less efficient than CHAR, that is, if you want to achieve efficiency, you have to sacrifice a certain amount of space, which is what we often say in database design: "space for efficiency".

Although VARCHAR2 saves more space than CHAR, if a VARCHAR2 column is modified frequently, and the length of the data to be modified is different each time, this will lead to a "Row Migration" phenomenon, which results in extra Icano, which should be avoided in database design and adjustment, in which case it would be better to use CHAR instead of VARCHAR2.

Use the scene:

Scenarios applicable to varchar:

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

String columns are rarely updated because there is no or little memory fragmentation problem

Using a complex character set such as UTF-8, each character is stored with a different number of bytes

Scenarios applicable to char:

It is suitable when the length of the column is fixed, such as MD5 ciphertext data.

The difference between char and varchar in mysql is shared here. I hope the above content can be helpful to everyone and 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.