Analyze MySQL's method of dynamically modifying the length of varchar

Although this situation should not happen, usually like our relational database, we should have designed it in advance and cannot change it later, but due to the negligence of the previous work, in fact, To be honest, it's not just my personal negligence, it's mainly due to communication reasons. Of course, I designed the database after all, so I still have to criticize myself.

Let’s talk about the situation: MySQLThe field has a varchar value field and the field is set too short, 30 are set. (I vaguely remember that varchar is extensible, but of course the reality does not tolerate me. vaguely), so I could only find a way to dynamically modify the length of the varchar field while ensuring that the database data remained unchanged. After searching for a while, I finally found it.

alter table 表名 modify column 字段名 varchar(数量);

This function is quite powerful, but I still remind everyone that it is best to avoid this kind of problem when designing.

PS: The problem of setting the varchar length in mysql

If a certain item is set to varchar(50)

then of course it is for English 50

What about Chinese

utf-8 Chinese occupies 3 bytes

So, can this varchar(50) only store 16 Chinese characters?

mysql varchar(50) No matter Chinese or English, 50 are stored.

MySQL5 document, which describes the varchar field type as follows: varchar(m) variable lengthString . M represents the maximum column length. The range of M is 0 to 65,535. (The maximum actual length of a VARCHAR is determined by the size of the longest line and the character set used; the maximum effective length is 65,532 bytes).

Why does it change like this? I really feel that the MySQL manual is too unfriendly, because you have to read carefully to find this description: MySQL 5.1 complies with the standard SQL specification and does not delete the trailing spaces of VARCHAR values. VARCHAR is saved with a one-byte or two-byte long prefix + data. If the declared length of a VARCHAR column is greater than 255, the length prefix is ​​two bytes.

Okay, I seem to understand a little bit. But specifically, he said that when the length is greater than 255, a 2-byte length prefix is ​​used. Elementary school subtraction problem: 65535 - 2 = 65533. I don’t know how these experts calculated it, so I’ll keep my doubts for now?

Note: I tested using UTF8 encoding, and the maximum length of varchar is 21854 bytes.

Tested under mysql version 5.0.45 and database encoding utf8: the longest varchar definition is 21785. In other words, no matter whether letters, numbers, or Chinese characters, only 21,785 can be placed.

Conjecture: The maximum varchar bytes are 65535, and utf8 encodes one character with 3 bytes 65535/3=21785.

The above is the detailed content of Analyze MySQL's method of dynamically modifying the length of varchar. For more information, please follow other related articles on the PHP Chinese website!