MySQL study NOTE _ summary of MySQL character type VARCHAR length knowledge bitsCN.com
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 ). So 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 definition 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 cannot be created.
II. VARCHAR length encoding restrictions
1. Overview
If the character type is gbk, each character occupies a maximum of 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 division 2 is that the character encoding is gbk.
(2) If a table is defined
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
Subtraction 1 and subtraction 2 are the same as those in the previous example;
The reason for 4 reduction is that int type c occupies 4 bytes;
The reason for the decrease of 30*3 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.
III. 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.
View when SQL Mode
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 '', a table can be created, but a warning message is displayed,
Mysql> set session SQL _mode = '';
Mysql> create table test4 (a varchar (25000) charset = utf8;
Mysql> show warnings;
The warning message indicates that the VARCHAR can be created because MySQL automatically converts the VARCHAR to the Text type.
Mysql> show create table test4;
Appendix:
1. UTF8 encoding
Http://zh.wikipedia.org/wiki/UTF8
2. Methods for viewing character sets:
(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 (column.
Show full columns from tableName;
Reference: http://dinglin.iteye.com/blog/914276
BitsCN.com