varchar(N), N refers to the maximum number of characters, not bytes.
Remember:
(1) MySQL requires that the definition length of a row cannot exceed 65535.
(2) If a single field is greater than 65535, it is converted to TEXT.
(3) The maximum limit for a single line is 65535, excluding TEXT and BLOB.
(4)utf8:
1 character = 3 bytes, 1 Chinese character = 1 character
That is, if a field is defined as varchar(200), it can store 200 Chinese characters or 200 letters.
(5)gbk:
1 character = 2 bytes, 1 Chinese character = 1 character
That is, if a field is defined as varchar(200), it can store 200 Chinese characters or 200 letters.
When creating tables in MySQL, a strange phenomenon is encountered:
root@localhost : test 10:30:54>CREATE TABLE tb_test (
-> recordid varchar(32) NOT NULL,
-> areaShow varchar(10000) DEFAULT NULL,
-> areaShow1 varchar(10000) DEFAULT NULL,
-> areaShow2 varchar(10000) DEFAULT NULL,
-> PRIMARY KEY (recordid)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
report errors
root@localhost : test 10:31:01>CREATE TABLE tb_test (
-> recordid varchar(32) NOT NULL,
-> areaShow varchar(30000) DEFAULT NULL,
-> areaShow1 varchar(30000) DEFAULT NULL,
-> areaShow2 varchar(30000) DEFAULT NULL,
-> PRIMARY KEY (recordid)
-> ) ENGINE=INNODB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 3 warnings (0.26 sec)
Can be created, but the type is converted.
root@localhost : test 10:31:14>show warnings;
+-------+------+----------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------+
| Note | 1246 | Converting column 'areaShow' from VARCHAR to TEXT |
| Note | 1246 | Converting column 'areaShow1' from VARCHAR to TEXT |
| Note | 1246 | Converting column 'areaShow2' from VARCHAR to TEXT |
+-------+------+----------------------------------------------------+
3 rows in set (0.00 sec)
doubt:
Why do small (10000) fields report errors, while large (30000) fields can be created. Why can't small ones be converted directly?
solve:
There was a limitation when MySQL was creating tables: MySQL requires that the definition length of a row should not exceed 65535. The specific reasons can be seen as follows:
http://dev.mysql.com/doc/refman/5.1/en/silent-column-changes.html
(1) If a single field is greater than 65535, it is converted to TEXT.
(2) The maximum limit for a single line is 65535, excluding TEXT and BLOB.
Explain the phenomena according to the limitations summarized above:
The first case is:
Single field length: varchar(10000), number of bytes: 10000*3(utf8) + (1 or 2) = 30000, less than 65535, can be established.
Single line record length: varchar(10000)3, bytes: 300003(utf8) + (1 or 2) = 90000, greater than 65535, cannot be established, so an error is reported:
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs
The second case is:
The length of a single field: varchar(30000), number of bytes: 30000 * 3 + (1 or 2) = 90000, greater than 65535. It can be established only after it is converted to TEXT. Therefore, warnings are reported.
Single line record length: varchar(30000)*3. Because each field is converted to TEXT, and TEXT has no limit, a table can be established.
root@localhost : test 10:31:14>show warnings; +-------+------+----------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------+ | Note | 1246 | Converting column 'areaShow' from VARCHAR to TEXT | | Note | 1246 | Converting column 'areaShow1' from VARCHAR to TEXT | | Note | 1246 | Converting column 'areaShow2' from VARCHAR to TEXT | +-------+------+----------------------------------------------------+
After using MySQL for so long, I'm ashamed that I don't know the basic table building limit..
The reasons are as follows:
I was asked a question: what is the maximum length of varchar in MySQL? This is not a fixed number. This article briefly explains the restriction rules.
1. Restriction rules
The following rules apply to the field definition:
a) Storage limit
The varchar field stores the actual content separately outside the cluster index. The beginning of the content uses 1 to 2 bytes to represent the actual length (2 bytes are required when the length exceeds 255), so the maximum length cannot exceed 65535.
b) Coding length limit
If the character type is gbk, each character can occupy at most 2 bytes, and the maximum length cannot exceed 32766;
If the character type is utf8, each character can occupy up to 3 bytes, and the maximum length cannot exceed 21845.
If the definition exceeds the above limit, the varchar field will be forcibly converted to text type and a warning will be generated.
c) Row length limit
The length limit of varchar in practical application is the length of a row definition. MySQL requires that the definition length of a row cannot exceed 65535. If the defined table length exceeds this value, you will be prompted
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs.
2. Calculation example
Give two examples to illustrate the calculation of the actual length.
a) If a table has only one varchar type, such as defined as
create table t4(c varchar(N)) charset=gbk;
Then the maximum value of N here is (65535-1-2) / 2 = 32766.
The reason for subtracting 1 is that the actual row storage starts from the second byte ';
The reason for subtracting 2 is that the 2 bytes in the varchar header represent the length;
The reason for dividing by 2 is that the character encoding is gbk.
b) If a table is defined as
create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;
Then the maximum value of N here is (65535-1-2-4-30 * 3) / 3 = 21812
Minus 1 and minus 2 are the same as the above example;
The reason for subtracting 4 is that c of type int occupies 4 bytes;
The reason for reducing 30 * 3 is that char(30) occupies 90 bytes and the code is utf8.
If the varchar exceeds the above b rule and is forcibly converted to text type, the definition length of each field is 11 bytes. Of course, this is not "varchar".