Character data types in MySQL

varchar types in MySQL

variations of the 1.varchar type
The maximum length of the MySQL database's varchar type is limited to 255 in versions under 4.1, and its data range can be 0~255 or 1~255
according to the different version of the database to decide. In versions above MySQL5.0, the length of the varchar data type is supported to 65535, which means that 65,532 bytes of data can be stored .
The start and end bits take up 3 bytes, that is, data that needs to be stored in a fixed text or blob format in 4.1 or later can be stored using variable-length varchar, which effectively reduces the size of the database file.
The varchar type of the MySQL database is in versions below 4.1, nvarchar (characters that store Unicode data types), whether it is a character or a Chinese character, are stored as 2 bytes, generally used as Chinese or other language input, so it is not easy to garbled; VARCHAR: Chinese characters are 2 bytes, other characters designators are 1 bytes, varchar is suitable for inputting English and numerals.
4.0 versions, varchar (20), refers to 20 bytes, if stored UTF8 kanji, can only save 6 (each Kanji 3 bytes), 5.0 version above, varchar (20), refers to 20 characters, regardless of the number of storage, Letters or UTF8 Kanji (3 bytes per kanji), can be stored 20, the maximum size is 65532 bytes, varchar (20) in MYSQL4 is the largest is only 20 bytes, but MYSQL5 according to the code, the storage size is different, the following rules:
a) storage limits
The varchar field stores the actual content separately from the clustered index, and the content begins with 1 to 2 bytes representing the actual length (2 bytes in length over 255), so the maximum length cannot exceed 65535. ,
b) Encoding length limit
If the character type is GBK, the maximum number of characters per character is 2 bytes, and the max length cannot exceed 32766.
If the character type is UTF8, each character can be up to 3 bytes and the maximum length cannot exceed 21845.
If the limit above is defined, the varchar field is forcibly converted to the text type and generates warning.
c) Limit of the length of the President
The length of a row definition is the limit of the varchar length in the actual application. MySQL requires that a row's definition length cannot exceed 65535. If the defined table length exceeds this value, you are prompted for error 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. Some columns to TEXT or BLOBs

2.CHAR (M), VARCHAR (m) different
CHAR (m) defines the length of the column as fixed, and the M value can be between 0~255, and when the char value is saved, the blank is padded to the right of the cell to reach the specified length. When a char value is retrieved, the trailing space is removed. No case conversions are made during the storage or retrieval process. Char is convenient to store the fixed-length data, the index on the Char field is more efficient, such as the definition of char (10), then regardless of whether you store data reached 10 bytes, take up 10 bytes of space, insufficient to automatically fill with spaces.

varchar (m) defines the length of the column as a variable length string, and the M value can be between 0~65535, (the maximum effective length of varchar is determined by the maximum row size and the character set used. The overall maximum length is 65,532 bytes). The varchar value is saved with only the required number of characters, plus one byte to record the length (two bytes if the column declaration is longer than 255). The varchar value is not populated when it is saved. The trailing spaces on the value save and retrieve are still preserved, conforming to standard SQL. VARCHAR stores 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 it cannot exceed 10 characters, define it as

   varchar (10) is the most cost-effective. 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. The biggest difference between char and varchar is that one is fixed length and one is variable length. Because it is variable length, it is actually stored as the actual string plus a byte of the length of the record string (if more than 255 requires two bytes). If the value assigned to a char or varchar column exceeds the maximum length of the column, the value is cropped to fit. If the character being cropped is not a space, a warning is generated. If you crop a non-whitespace character, it causes an error (rather than a warning) and disables the insertion of values by using strict SQL mode.

3. The difference between varchar and text, blob types
Varchar,blob and text types are variable-length types, For its storage requirements, it depends on the actual length of the column values (denoted by L in the preceding table), rather than depending on the maximum possible size of the type. For example, a varchar (10) column can hold a string with a maximum length of 10 characters, the actual storage needs to be the length of the string, plus 1 bytes to record the length of the string. For the string ' ABCD ', L is 4 and the storage requirement is 5 bytes. The blob and text types require either three-or 4-byte to record the length of the column values, depending on the maximum possible length of the type. varchar requires a defined size, with a maximum limit of 65535 bytes, and the text is not required. If you assign a value that exceeds the maximum length of a column type to a BLOB or text column, the value is truncated to fit.

4. Summarize Char,varchar,text Differences
The length of the difference, Char range is 0~255,varchar the longest is 64k, but note here 64k is the length of the entire row, to consider the other column, and if there is not NULL when it will occupy a bit, for different character sets, effective length is not the same, such as UTF8, up to 21845, but also to remove the other column, but varchar in general, the storage is sufficient. If you encounter a large text, consider using text, which can be up to 4G. Efficiency is basically char>varchar>text, but if you use the InnoDB engine, it is recommended to use varchar instead of charchar and varchar can have default values, Text cannot specify a default value the database chooses the appropriate data type to store or is necessary to have a certain effect on performance. Here in the piecemeal record two, for the int type, if you do not need to access negative values, preferably with unsigned; For fields that often appear in the Where statement, consider indexing, especially for indexing.

Character data types in MySQL