A string can be used to represent any value, so it is one of the most basic types. We can use string types to store binary data such as images or sounds, or to store data that is compressed with gzip. The following table describes the various string types:
Table 1.3. String class data column type
char[(M)] |
M bytes |
M bytes |
varchar[(M)] |
M bytes |
L+1 bytes |
Tinyblod,tinytext |
2^8-1 bytes |
L+1 bytes |
Blob,text |
2^16-1 bytes |
L+2 |
Mediumblob,mediumtext |
2^24-1 bytes |
L+3 |
Longblob,longtext |
2^32-1 bytes |
L+4 |
ENUM (' value1 ', ' value2 ',...) |
65,535 Members |
1 or 2 bytes |
SET (' value1 ', ' value2 ',...) |
64 members |
1,2,3,4 or 8 bytes |
L+1, l+2 is to indicate that the data column is variable length, it occupies the space according to the data row of the increase and decrease of the surface will change. The total length of the data rows depends on the length of the data values stored in those data columns. Bytes in l+1 or l+2 are used to hold the length of the data value. When dealing with variable-length data, MySQL saves both the data content and the length of the data.
If you put data that exceeds the maximum length of the string in the character class data column, MySQL automatically truncates the truncation process.
There is a list in the data column definition for the enum and set type, and the element in the list is the legal value of the data column. If you attempt to place a value that is not in the list into a data column, it is converted to an empty string ("").
The value of a string type is saved as a contiguous sequence of bytes, and is treated as either a byte or a character based on whether they hold a binary string or a non-binary character:
Binary strings are treated as a contiguous sequence of bytes, regardless of the character set. MySQL treats BLOB data columns and data in char and varchar data columns with binary attributes as binary values.
A non binary string is treated as a sequential sequence of characters. is related to the character set. MySQL treats the text column with the data in char and varchar data columns without the binary attribute as a binary value.
In later versions of the MySQL4.1, different data columns can use different character sets. Prior to the MySQL4.1 version, MySQL used the server's character set as the default character set.
Non-binary strings, which are commonly referred to as strings, are compared and sorted by character in sequence in the character set. Binary strings, which are independent of the character set, are not sorted in character order, but are based on the binary value of the byte as the basis for comparison and sorting. The following describes the comparison of two strings:
The binary string is compared by a byte of a byte, and the comparison is based on a binary value of two bytes. That is, it is case-sensitive because the numeric encoding of the case of the same letter is different.