Knowledge points: char, varchar, text differences in SQL

Char is fixed length, varchar,text is variable length,

• 1, CHAR. Char is convenient for storing fixed-length data, and the index on a char field is highly efficient, such as defining char (10), which takes up 10 bytes of space regardless of whether the data you store is 10 bytes.

• 2, VARCHAR. Store variable-length data, but the storage efficiency is no higher than char. If the possible value of a field is not fixed length, we only know that it cannot exceed 10 characters, it is the most advantageous to define it as VARCHAR (10). The actual length of the varchar type is +1 of the actual length of its value. Why "+1"? This byte is used to hold the length that is actually used.

From the space consideration, with the varchar suitable, from the efficiency consideration, uses the char to be suitable, the key is to find the tradeoff point according to the actual situation.

• 3, TEXT. Text stores non-Unicode data of variable length, with a maximum length of 2^31-1 (2,147,483,647) characters.

Attention

Char,varchar does not resemble a numeric type, has a system default length, so you must define the length in parentheses, you can have a default value

Text can not write the default value, if you specify the length, will not report an error, but this length is not working, meaning that you insert data, more than you specify the length or can be inserted normally (no test in strict mode)

Summary: Char can cause wasted space, but there is a speed advantage, and varchar saves space, but not as fast as char.

(1)经常变化的字段用varchar (2)知道固定长度的用char (3)尽量用varchar (4)超过255字节的只能用varchar或者text (5)能用varchar的地方不用text

Knowledge points: char, varchar, text differences in SQL