MySQL study note _ Summary of MySQL character type VARCHAR Length

MySQL study note _ Summary of MySQL character type VARCHAR length I. VARCHAR storage and row length restrictions
1. in VARCHAR (N), N refers to the length of characters. The VARCHAR type supports a maximum of 65535 characters and 65535 bytes, but does not support varchar with a length of 65535, 65535 should contain the length of all fields, the length of the Variable Length Field identifier, and the accumulation of NULL identifier. The content starts with 1 to 2 bytes to indicate the actual length (2 bytes if the length exceeds 255 ). Therefore, there are other overhead. The actual storage length is 65532. (the maximum length of the MySQL database varchar type in versions earlier than 5.0.3 is 255, and its data range can be 0 ~ 255)

2. Because the null flag occupies one byte, you can remove the not null restriction.
3. MySQL requires that the length of a row cannot exceed 65535, which means that the total length of all columns cannot exceed 65535. If the total length of a column exceeds this length, it is still impossible to create. www.2cto.com
Ii. VARCHAR Length Encoding restrictions
1. if the character type is gbk, each character occupies up to 2 bytes, and the maximum length cannot exceed 32766. If the character type is utf8, each character occupies up to 3 bytes, and the maximum length cannot exceed 21845. If the preceding limits are exceeded during definition, the varchar field is forcibly converted to the text type and generates a warning. 2. example (1) If a table has only one varchar type, for example, create table t4 (c varchar (N) charset = gbk; the maximum value of N is (65535-1-2)/2 = 32766. The reason for the decrease of 1 is that the actual Row Storage starts from the second byte. The reason for the decrease of 2 is that the two bytes in the varchar header indicate the length. The reason for the decrease of 2 is that the character encoding is gbk. (2) If a table is defined as create table t4 (c int, c2 char (30), c3 varchar (N) charset = utf8; the maximum value of N here is (65535-1-2-4-30*3)/3 = 21812 minus 1 and minus 2 is the same as that in the previous example; the reason for 4 reduction is that c of the int type occupies 4 bytes; the reason for 30*3 reduction is that char (30) occupies 90 bytes and the encoding is utf8. If varchar exceeds the preceding B rule and is forced to be of the text type, each field occupies 11 bytes. Of course, this is no longer a "varchar. Www.2cto.com III. About the SQL mode in MySQL, the SQL mode is often used to solve the following problems:
1. By setting the SQL Mode, you can complete data verification with different strict degrees to effectively ensure data accuracy. 2. you can set the SQL Mode to ANSI to ensure that most SQL statements comply with the standard SQL syntax. Therefore, you do not need to modify the business SQL statements when migrating between different databases. 3. Before migrating data between different databases, you can set the SQL Mode to make it easier for MySQL to migrate data to the target database. Check when the SQL mode is mysql> select @ SQL _mode;

STRICT_TRANS_TABLES (strict mode) implements strict data validation, so that error data cannot be inserted into the table. if you set SQL _mode to '', you may be able to create a table, but there is a warning message, mysql> set session SQL _mode = ''; mysql> create table test4 (a varchar (25000) charset = utf8; mysql> show warnings; The warning message prompts that MySQL can be created because mysql automatically converts VARCHAR to the Text type. Mysql> show create table test4;
Appendix: 1. UTF8 encoding Overview: http://zh.wikipedia.org/wiki/utf82.20.: www.2cto.com (1) view the MySQL database server and MySQL character set. Show variables like '% char %'; (2) view the MySQL Character Set of the MySQL DATA table. Show table status from dbName where name = 'tablename'; (3) view the MySQL Character Set of the MySQL DATA column. Show full columns from tableName; reference: http://www.bkjia.com/database/201206/136787.html
Author: Acacia Forest