ERROR 1074 (42000): column length too big for Column ' flist ' (max = 21845); Use BLOB or TEXT instead

1. Version

1) Operating system

Cat/etc/issue
Cat/etc/issue
CentOS Release 6.6 (Final)
Kernel \ r on an \m

Cat/proc/version
Cat/proc/version
Linux version 2.6.32-504.el6.x86_64 ([email protected]) (GCC version 4.4.7 20120313 (Red Hat 4.4.7-11) (gcc)) #1 SMP Wed Oct 04:27:16 UTC 2014

2) MySQL database version

MySQL --version

MySQL Ver 14.14 distrib 5.6.26, for linux-glibc2.5 (x86_64) using Editline Wrapper

2. Description of the problem

The Build Table statement reported the following error:

CREATE TABLE test_1 (
Fid bigint(20) unsigned NOT NULL,
Ftype tinyint(4) unsigned NOT NULL,
Flist varchar(65532) DEFAULT NULL,
Fstatus tinyint(3) unsigned DEFAULT '0',
Ftime bigint unsigned DEFAULT '0',
Faddtime bigint unsigned DEFAULT '0',
PRIMARY KEY (Fid,Ftype)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
The following error was reported in the report:
ERROR 1074 (42000): Column length too big for column'Flist' (max = 21845); use BLOB or TEXT instead

3. Problem analysis

3.1 Cause Analysis

Although it is known that MySQL has a 65535 limit on the length of the table, but there is no time to organize the problem, and then tidy up today.

ERROR 1074 (42000): column length too big for Column ' flist ' (max = 21845); Use BLOB or TEXT instead
From the above error we know that the Flist column specified value cannot be greater than 21845 bytes (as defined in the official MySQL manual, the field length for creating a table is limited to 65535 bytes, which refers to the length specified by all columns and, of course, the fields of text and blob types).
Another thing we need to note is that when we define the column length, the specified length is in characters, and the 65535 limit mentioned above is in bytes. Each character in a different character set occupies a different number of bytes, with each character occupying 3 bytes (65535/3=21845) under the UTF8, gbk each character occupies 2 bytes (65535/2=32767), and the next character in the Latin1 character set takes one byte.

3.2 Operation Verification

<span style="color:#333333;">Experiment 1:
CREATE TABLE test_1 (
Flist varchar(21845) DEFAULT NULL
) 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. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
</span><span style="color:#ff0000;">##We found that we assign a column to the table, the column length is 21845 characters (the maximum length limit under utf8), but the table still reports an error. This is because there are some other overheads, so we cannot specify the column length as the maximum limit of 21845 (the test found that the table was successfully built after the specified length was 21844)</span>

<span style="color:#333333;">Experiment 2:

CREATE TABLE test_1 (
Fid bigint(20) unsigned NOT NULL,
Ftype tinyint(4) unsigned NOT NULL,
Flist varchar(21844) DEFAULT NULL,
Fstatus tinyint(3) unsigned DEFAULT '0',
Ftime bigint unsigned DEFAULT '0',
Faddtime bigint unsigned DEFAULT '0',
PRIMARY KEY (Fid,Ftype)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Report the following error:
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
</span><span style="color:#ff0000;">##Although the Flist length is specified as 21844, because there are other non-TEXT and BLOB fields, an error is reported. This is because the 65535 length limit is the maximum limit for the sum of the lengths of all columns in the table. If the sum of the lengths of the columns exceeds this value, an error will still be reported when the table is built. (Except for TEXT and BLOB type fields)</span>

# #Note from Experiment 2 we can see that 65535 is not just a length limit for varchar type (This and Kang Master InnoDB Storage Engine second version of the relevant description of some discrepancies)

4. Solution

1. Replace the varchar type with text or blob type

2. Narrowing the character length specified by the varchar type

ERROR 1074 (42000): column length too big for Column ' flist ' (max = 21845); Use BLOB or TEXT instead