8.3.4-mysql string

Character type

#官网: https://dev.mysql.com/doc/refman/5.7/en/char.html#注意: The arguments in char and varchar brackets refer to the length of the character #char type: fixed length, simple rough, wasted space, fast access speed range of character length:0-255(a Chinese is a character that is a UTF8 encoded 3 bytes) Store: When a value of type char is stored, a space is padded to the right to satisfy the length, for example: Specify a length of 10, save>10 characters are error, deposit <10 characters are padded with spaces until 10 characters are stored for retrieval: When retrieving or saying a query, the detected results automatically delete trailing spaces unless we open pad_char_to_full_length SQL mode (SET sql_mode='Pad_char_to_full_length';) #varchar类型: variable length, precision, space saving, slow access speed range of characters:0-65535(If greater than 21845 will prompt for other types.) MySQL line maximum limit is 65535 bytes, character encoding is utf-8: https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html)storage: The varchar type stores the actual contents of the data and is not populated with spaces, if'AB', the trailing spaces will be saved. Emphasize: varchar type will add 1 before real data-2bytes prefix that is used to represent the number of bytes bytes of real data (1-2Bytes Maximum represents 65,535 digits, exactly as MySQL has a maximum byte limit on row, which is sufficient to use if the actual dataThe <255bytes requires a 1Bytes prefix (1bytes=8bit2**8 The maximum representation of the number is 255) if the real dataThe >255bytes requires a 2Bytes prefix (2bytes=16bit2**16 The maximum representation of the number is 65535) search: The trailing space will be saved, in the search or query, will also display the contents of the text, including spaces

Basic Knowledge

The official website is explained below

Learn about two functions before testing

Length: View number of bytes Char_length: viewing characters

1. Char fills a space to accommodate a fixed length, but when queried, it does not delete the trailing spaces (pretending that you have not wasted space), and then modifies the sql_mode to make it look like a prototype.

mysql> CREATE TABLE T1 (xChar(5), Y varchar (5)); Query OK,0Rows Affected (0.26sec) #char存5个字符, while varchar saves 4 characters of MySQL> INSERT into T1 values ('What are you talking about?','What are you talking about?'); Query OK,1Row affected (0.05sec) MySQL> SET sql_mode="'; Query OK,0Rows affected,1Warning (0.00sec) #在检索时char很不要脸地将自己浪费的2个字符给删掉了, pretending to be like you haven't wasted space, and varchar is honest, how much is saved, how much MySQL is shown>SelectX,char_length (x), Y,char_length (y) fromT1;+-----------+----------------+------------+----------------+| x | Char_length (x) | y | Char_length (y) |+-----------+----------------+------------+----------------+| What do you look at |3| What do you look at |4|+-----------+----------------+------------+----------------+Rowinch Set(0.00sec) #略施小计, let char show the original MySQL> SET Sql_mode ='Pad_char_to_full_length'; Query OK,0Rows Affected (0.00sec) #这下子char原形毕露了 ... MySQL>SelectX,char_length (x), Y,char_length (y) fromT1;+-------------+----------------+------------+----------------+| x | Char_length (x) | y | Char_length (y) |+-------------+----------------+------------+----------------+| What do you look at |5| What do you look at |4|+-------------+----------------+------------+----------------+Rowinch Set(0.00sec) #char类型: 3 characters in Chinese characters+ 2 spaces =11bytes#varchar Type: 3 Chinese characters+ 1 spaces =10Bytesmysql>SelectX,length (x), Y,length (y) fromT1;+-------------+-----------+------------+-----------+| x | Length (x) | y | Length (y) |+-------------+-----------+------------+-----------+| What do you look at | One| What do you look at |Ten|+-------------+-----------+------------+-----------+Rowinch Set(0.00Sec

View Code

2. Although char and VARCHAR are stored differently, the comparison of two strings compares only their values, ignoring the right padding for the existence of a char value, even if SQL _mode is set to Pad_char_to_full_ LENGTH, but this does not apply to Like

ValuesinchCHAR and VARCHAR columns is sorted and compared according to the characterSetcollation assigned to the column. All MySQL collations is of type PAD SPACE. This means is all CHAR, VARCHAR, and TEXT values is compared without regard to any trailing spaces. "Comparison"inch  ThisContext does not include the like pattern-matchingoperator, forwhich trailing spaces is significant. For Example:mysql> CREATE TABLE names (MyName CHAR (Ten)); Query OK,0Rows Affected (0.03sec) MySQL> INSERT into Names VALUES ('Monty'); Query OK,1Row affected (0.00sec) MySQL> SELECT myname ='Monty', myname ='Monty'from names;+------------------+--------------------+| MyName ='Monty'| MyName ='Monty'|+------------------+--------------------+|1|1|+------------------+--------------------+Rowinch Set(0.00sec) MySQL> SELECT myname like'Monty', myname like'Monty'from names;+---------------------+-----------------------+| MyName like'Monty'| MyName like'Monty'|+---------------------+-----------------------+|1|0|+---------------------+-----------------------+Rowinch Set(0.00Sec

View Code

3. Summary

#常用字符串系列: char vs. varchar NOTE: Although varchar is more flexible to use, the char data type is processed faster and sometimes exceeds the varchar processing speed from the overall system performance perspective%. Therefore, the user should design the database in a comprehensive consideration of various factors in order to achieve the best balance # Other string series (efficiency:Char>varchar>Text ) Text series Tinytext text Mediumtext Longtextblob series Tinyblob BLOB mediumblob longblob binary series binary Varbinarytex The T:text data type is used to hold large, variable-length strings that can be set to 65535 (2** -−1) characters. Mediumtext:a TEXT column with A maximum length of -,777,215(2** -−1) characters.longtext:a TEXT column with A maximum length of4,294,967,295or 4GB (2** +−1) characters.

View Code

8.3.4-mysql string