Take char and varchar in the database seriously

After the student management system is completed, a problem will be found. After the username is added with a few blank fields, you can log on to the system. This is what we want. Let's talk about the difference between char and varchar. Let's talk about their working method. Char is a fixed-length character type, and varchar is a variable-length character. For example, set two fields: Car

After the student management system is completed, a problem will be found. After the username is added with a few blank fields, you can log on to the system. This is what we want. Let's talk about the difference between char and varchar. Let's talk about their working method. Char is a fixed-length character type, and varchar is a variable-length character. For example, set two fields: Car

After the student management system is completed, a problem will be found. A few spaces are added after the user name. Sometimes you can log on to the system. This is what we want. Let's talk about the difference between char and varchar.

Let's talk about how they work.

Char is a fixed-length character type, and varchar is a variable-length character. For example, set two fields.

CardID char (10), StuID varchar (10). When inserting data for this field,

Input data	Char field content	Varchar field content
Aaa	Aaa (There are 7 spaces behind aaa)	Aaa (There is no space behind aaa)
Aaabbb	Aaabbb	Aaabbb
Aaabbbcccccc	Error. The string or binary data is truncated.	Error. The string or binary data is truncated.

From the table above, we can see that for char-type data, if the free length is not enough, SQL will automatically fill in space, while varchar will not automatically fill in space.

That is to say, the Data Length of the char type = the defined length, while the data length of the varchar type <= the defined maximum length.

Char type, fixed character length, high efficiency, but will occupy more space than varchar; varchar type, variable character length, save space, but efficiency is slightly worse than char.

Their features determine their usage.

Char is used to avoid varchar. Thanks, we are not too limited, and the efficiency will increase the acceptance rate of our products. This is the "space-for-efficiency" commonly used in database design"

In addition, although varchar saves space than char, if a varchar column is often modified and the length of the modified data is different each time, this will cause Row Migration) phenomenon (obvious in Oracle), and this causes redundant I/O, which should be avoided in database design and adjustment. In this case, replacing varchar with char would be better.

But when using char, always pay attention to the space issue. Because char automatically fills in spaces, when the input data length is smaller than the defined maximum length, the input "a" and the input "a" are the same. Because they will eventually be filled with spaces and become the same content. If this column has a primary key constraint or a uniqueness constraint, an error occurs because it violates the "entity integrity" constraint. Of course, if you do not have these constraints, errors will also occur. Take the login module of the charging system of the data center as an example. If the password is defined as char type, set the password to "admin ", but I can log on smoothly using "admin", which is clearly not what the user wants to see. The pursuit of efficiency is correct, but the premise is to ensure its correctness. Therefore, I think varchar, such as a password, must be used for some specific purposes.