admin管理员组文章数量:1130349
解决Data too long for column ‘xxx‘ at row 1问题以及深入理解mysql的字符串数据类型(char,varchar,enum,text,longtext...)
文章目录
- 1. 复现问题
- 2. 分析问题
- 3. 深入理解mysql的数据类型
- 3.1 CHAR 和 VARCHAR 类型
- 3.2 TEXT 类型
- 3.3 ENUM 类型
- 3.4 SET 类型
- 4. 解决问题
- 5. 总结
1. 复现问题
今天在测试环境新增数据时，报出如是错误：Data too long for column 'apply_service_type' at row 1 。
为了复现这个问题，我特地在本地数据库中增加如下test表：
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,`apply_service_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
在test表中新增如下数据：
mysql> INSERT test(apply_service_type) VALUES('[{"code":"apply_service_type_encrypt","value":"加密服务类"},{"code":"apply_service_type_qrcode","value":"二维码支撑类"},{"code":"apply_service_type_decode","value":"解密服务类"}]');
报出上述错误：
ERROR 1406 (22001): Data too long for column 'apply_service_type' at row 1
为什么会报出这个错误呢？接下来便分析错误原因。
2. 分析问题
从test表中可以看到apply_service_type是字符串类型，长度为255。
但是，存储的数据是[{"code":"apply_service_type_encrypt","value":"加密服务类"},{"code":"apply_service_type_qrcode","value":"二维码支撑类"},{"code":"apply_service_type_decode","value":"解密服务类"}]。该数据长度为298，如下图所示：
数据长度(298)大于varchar(255)的允许长度，自然会报出 Data too long for column 'apply_service_type' at row 1，即“apply_service_type”列太长。
3. 深入理解mysql的数据类型
为了更好的使用mysql数据库，在解决问题之前，我们需要了解mysql数据库字符串类型，以保证知其然知其所以然。
MySQL字符串类型其用来存储字符串数据，还可以存储图片和声音的二进制数据。
MySQL中的字符串类型有CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。
下表中列出了MySQL中的字符串数据类型，括号中的M表示可以为其指定长度。
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| CHAR(M) | 固定长度非二进制字符串 | M 字节，1<=M<=255 |
| VARCHAR(M) | 变长非二进制字符串 | L+1字节，在此，L< = M和 1<=M<=255 |
| TINYTEXT | 非常小的非二进制字符串 | L+1字节，在此，L<2^8 |
| TEXT | 小的非二进制字符串 | L+2字节，在此，L<2^16 |
| MEDIUMTEXT | 中等大小的非二进制字符串 | L+3字节，在此，L<2^24 |
| LONGTEXT | 大的非二进制字符串 | L+4字节，在此，L<2^32 |
| ENUM | 枚举类型，只能有一个枚举字符串值 | 1或2个字节，取决于枚举值的数目 (最大值为65535) |
| SET | 一个设置，字符串对象可以有零个或 多个SET成员 | 1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员） |
VARCHAR和TEXT类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用L表示），而不是取决于类型的最大可能尺寸。
假如一个VARCHAR(10)列能保存一个最大长度为10个字符的字符串，实际的存储需要字符串的长度L加上一个字节以记录字符串的长度。对于字符abcd，L是4，而存储要求5个字节。
3.1 CHAR 和 VARCHAR 类型
CHAR(M)为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。M表示列的长度，范围是0～255个字符。
例如，CHAR(4)定义了一个固定长度的字符串列，包含的字符个数最大为4。当检索到CHAR值时，尾部的空格将被删除。
VARCHAR(M)是长度可变的字符串，M表示最大列的长度，M的范围是0～65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加1。
例如，VARCHAR(50)定义了一个最大长度为50的字符串，如果插入的字符串只有10个字符，则实际存储的字符串为10个字符和一个字符串结束字符。VARCHAR在值保存和检索时尾部的空格仍保留。
**【实例】**下面将不同的字符串保存到CHAR(4)和VARCHAR(4)列，说明CHAR和VARCHAR之间的差别，如下表所示。
| 插入值 | CHAR(4) | 存储需求 | VARCHAR(4) | 存储需求 |
|---|---|---|---|---|
| ’ ’ | ’ ’ | 4字节 | ‘’ | 1字节 |
| ‘ab’ | 'ab ’ | 4字节 | ‘ab’ | 3字节 |
| ‘abc’ | 'abc ’ | 4字节 | ‘abc’ | 4字节 |
| ‘abcd’ | ‘abcd’ | 4字节 | ‘abcd’ | 5字节 |
| ‘abcdef’ | ‘abcd’ | 4字节 | ‘abcd’ | 5字节 |
对比结果可以看到，CHAR(4)定义了固定长度为4的列，无论存入的数据长度为多少，所占用的空间均为4个字节。VARCHAR(4)定义的列所占的字节数为实际长度加 1。
3.2 TEXT 类型
TEXT列保存非二进制字符串，如文章内容、评论等。当保存或查询TEXT列的值时，不删除尾部空格。
TEXT类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。不同的TEXT类型的存储空间和数据长度不同：
-
TINYTEXT表示长度为 255（28-1）字符的 TEXT 列。 -
TEXT表示长度为 65535（216-1）字符的 TEXT 列。 -
MEDIUMTEXT表示长度为 16777215（224-1）字符的 TEXT 列。 -
LONGTEXT表示长度为 4294967295 或 4GB（232-1）字符的 TEXT 列。
3.3 ENUM 类型
ENUM是一个字符串对象，值为表创建时列规定中枚举的一列值。其语法格式如下：
<字段名> ENUM( '值1', '值1', …, '值n' )
字段名指将要定义的字段，值n指枚举列表中第n个值。
ENUM类型的字段在取值时，能在指定的枚举列表中获取，而且一次只能取一个。如果创建的成员中有空格，尾部的空格将自动被删除。
ENUM值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号，枚举最多可以有65535个元素。
例如，定义 ENUM 类型的列（first，second，third），该列可以取的值和每个值的索引如下表所示。
| 值 | 索引 |
|---|---|
| NULL | NULL |
| ‘’ | 0 |
| ’first | 1 |
| second | 2 |
| third | 3 |
ENUM值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL值排在其他所有枚举值前。
提示：ENUM列总有一个默认值。如果将ENUM列声明为NULL，NULL值则为该列的一个有效值，并且默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列表的第1个元素。
3.4 SET 类型
SET是一个字符串的对象，可以有零或多个值，SET列最多可以有64个成员，值为表创建时规定的一列值。指定包括多个SET成员的SET列值时，各成员之间用逗号,隔开，语法格式如下：
SET( '值1', '值2', …, '值n' )
与ENUM类型相同，SET值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET成员值的尾部空格将自动删除。
但与ENUM类型不同的是，ENUM类型的字段只能从定义的列值中选择一个值插入，而SET类型的列可从定义的列值中选择多个字符的联合。
提示：如果插入SET字段中的列值有重复，则MySQL自动删除重复的值；插入SET字段的值的顺序并不重要，MySQL会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，给出警告。
4. 解决问题
通过以上的分析，我们需要把apply_service_type字段的数据类型修改为longtext或者text 都可以，本例将其修改为text的数类型类型，如下SQL所示：
mysql> alter table test modify column apply_service_type text;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
重新执行如上插入语句：
mysql> INSERT test(apply_service_type) VALUES('[{"code":"apply_service_type_encrypt","value":"加密服务类"},{"code":"apply_service_type_qrcode","value":"二维码支撑类"},{"code":"apply_service_type_decode","value":"解密服务类"}]');Query OK, 1 row affected (0.01 sec)
使用如下SQL查询可得：
mysql> select * from test;
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | apply_service_type |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | [{"code":"apply_service_type_encrypt","value":"加密服务类"},{"code":"apply_service_type_qrcode","value":"二维码支撑类"},{"code":"apply_service_type_decode","value":"解密服务类"}] |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
为什么将varchar类型修改为longtext或者text就行了呢，这里就涉及到mysql的数据类型。
5. 总结
了解mysql的字符串类型，对于熟练使用mysql存储数据，以及对字符串的性能优化，有着极大的好处。
解决Data too long for column ‘xxx‘ at row 1问题以及深入理解mysql的字符串数据类型(char,varchar,enum,text,longtext...)
文章目录
- 1. 复现问题
- 2. 分析问题
- 3. 深入理解mysql的数据类型
- 3.1 CHAR 和 VARCHAR 类型
- 3.2 TEXT 类型
- 3.3 ENUM 类型
- 3.4 SET 类型
- 4. 解决问题
- 5. 总结
1. 复现问题
今天在测试环境新增数据时，报出如是错误：Data too long for column 'apply_service_type' at row 1 。
为了复现这个问题，我特地在本地数据库中增加如下test表：
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (`id` int(11) NOT NULL AUTO_INCREMENT,`apply_service_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
在test表中新增如下数据：
mysql> INSERT test(apply_service_type) VALUES('[{"code":"apply_service_type_encrypt","value":"加密服务类"},{"code":"apply_service_type_qrcode","value":"二维码支撑类"},{"code":"apply_service_type_decode","value":"解密服务类"}]');
报出上述错误：
ERROR 1406 (22001): Data too long for column 'apply_service_type' at row 1
为什么会报出这个错误呢？接下来便分析错误原因。
2. 分析问题
从test表中可以看到apply_service_type是字符串类型，长度为255。
但是，存储的数据是[{"code":"apply_service_type_encrypt","value":"加密服务类"},{"code":"apply_service_type_qrcode","value":"二维码支撑类"},{"code":"apply_service_type_decode","value":"解密服务类"}]。该数据长度为298，如下图所示：
数据长度(298)大于varchar(255)的允许长度，自然会报出 Data too long for column 'apply_service_type' at row 1，即“apply_service_type”列太长。
3. 深入理解mysql的数据类型
为了更好的使用mysql数据库，在解决问题之前，我们需要了解mysql数据库字符串类型，以保证知其然知其所以然。
MySQL字符串类型其用来存储字符串数据，还可以存储图片和声音的二进制数据。
MySQL中的字符串类型有CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、ENUM、SET 等。
下表中列出了MySQL中的字符串数据类型，括号中的M表示可以为其指定长度。
| 类型名称 | 说明 | 存储需求 |
|---|---|---|
| CHAR(M) | 固定长度非二进制字符串 | M 字节，1<=M<=255 |
| VARCHAR(M) | 变长非二进制字符串 | L+1字节，在此，L< = M和 1<=M<=255 |
| TINYTEXT | 非常小的非二进制字符串 | L+1字节，在此，L<2^8 |
| TEXT | 小的非二进制字符串 | L+2字节，在此，L<2^16 |
| MEDIUMTEXT | 中等大小的非二进制字符串 | L+3字节，在此，L<2^24 |
| LONGTEXT | 大的非二进制字符串 | L+4字节，在此，L<2^32 |
| ENUM | 枚举类型，只能有一个枚举字符串值 | 1或2个字节，取决于枚举值的数目 (最大值为65535) |
| SET | 一个设置，字符串对象可以有零个或 多个SET成员 | 1、2、3、4或8个字节，取决于集合 成员的数量（最多64个成员） |
VARCHAR和TEXT类型是变长类型，其存储需求取决于列值的实际长度（在前面的表格中用L表示），而不是取决于类型的最大可能尺寸。
假如一个VARCHAR(10)列能保存一个最大长度为10个字符的字符串，实际的存储需要字符串的长度L加上一个字节以记录字符串的长度。对于字符abcd，L是4，而存储要求5个字节。
3.1 CHAR 和 VARCHAR 类型
CHAR(M)为固定长度字符串，在定义时指定字符串列长。当保存时，在右侧填充空格以达到指定的长度。M表示列的长度，范围是0～255个字符。
例如，CHAR(4)定义了一个固定长度的字符串列，包含的字符个数最大为4。当检索到CHAR值时，尾部的空格将被删除。
VARCHAR(M)是长度可变的字符串，M表示最大列的长度，M的范围是0～65535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加1。
例如，VARCHAR(50)定义了一个最大长度为50的字符串，如果插入的字符串只有10个字符，则实际存储的字符串为10个字符和一个字符串结束字符。VARCHAR在值保存和检索时尾部的空格仍保留。
**【实例】**下面将不同的字符串保存到CHAR(4)和VARCHAR(4)列，说明CHAR和VARCHAR之间的差别，如下表所示。
| 插入值 | CHAR(4) | 存储需求 | VARCHAR(4) | 存储需求 |
|---|---|---|---|---|
| ’ ’ | ’ ’ | 4字节 | ‘’ | 1字节 |
| ‘ab’ | 'ab ’ | 4字节 | ‘ab’ | 3字节 |
| ‘abc’ | 'abc ’ | 4字节 | ‘abc’ | 4字节 |
| ‘abcd’ | ‘abcd’ | 4字节 | ‘abcd’ | 5字节 |
| ‘abcdef’ | ‘abcd’ | 4字节 | ‘abcd’ | 5字节 |
对比结果可以看到，CHAR(4)定义了固定长度为4的列，无论存入的数据长度为多少，所占用的空间均为4个字节。VARCHAR(4)定义的列所占的字节数为实际长度加 1。
3.2 TEXT 类型
TEXT列保存非二进制字符串，如文章内容、评论等。当保存或查询TEXT列的值时，不删除尾部空格。
TEXT类型分为 4 种：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。不同的TEXT类型的存储空间和数据长度不同：
-
TINYTEXT表示长度为 255（28-1）字符的 TEXT 列。 -
TEXT表示长度为 65535（216-1）字符的 TEXT 列。 -
MEDIUMTEXT表示长度为 16777215（224-1）字符的 TEXT 列。 -
LONGTEXT表示长度为 4294967295 或 4GB（232-1）字符的 TEXT 列。
3.3 ENUM 类型
ENUM是一个字符串对象，值为表创建时列规定中枚举的一列值。其语法格式如下：
<字段名> ENUM( '值1', '值1', …, '值n' )
字段名指将要定义的字段，值n指枚举列表中第n个值。
ENUM类型的字段在取值时，能在指定的枚举列表中获取，而且一次只能取一个。如果创建的成员中有空格，尾部的空格将自动被删除。
ENUM值在内部用整数表示，每个枚举值均有一个索引值；列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号，枚举最多可以有65535个元素。
例如，定义 ENUM 类型的列（first，second，third），该列可以取的值和每个值的索引如下表所示。
| 值 | 索引 |
|---|---|
| NULL | NULL |
| ‘’ | 0 |
| ’first | 1 |
| second | 2 |
| third | 3 |
ENUM值依照列索引顺序排列，并且空字符串排在非空字符串前，NULL值排在其他所有枚举值前。
提示：ENUM列总有一个默认值。如果将ENUM列声明为NULL，NULL值则为该列的一个有效值，并且默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列表的第1个元素。
3.4 SET 类型
SET是一个字符串的对象，可以有零或多个值，SET列最多可以有64个成员，值为表创建时规定的一列值。指定包括多个SET成员的SET列值时，各成员之间用逗号,隔开，语法格式如下：
SET( '值1', '值2', …, '值n' )
与ENUM类型相同，SET值在内部用整数表示，列表中每个值都有一个索引编号。当创建表时，SET成员值的尾部空格将自动删除。
但与ENUM类型不同的是，ENUM类型的字段只能从定义的列值中选择一个值插入，而SET类型的列可从定义的列值中选择多个字符的联合。
提示：如果插入SET字段中的列值有重复，则MySQL自动删除重复的值；插入SET字段的值的顺序并不重要，MySQL会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL 将忽视这些值，给出警告。
4. 解决问题
通过以上的分析，我们需要把apply_service_type字段的数据类型修改为longtext或者text 都可以，本例将其修改为text的数类型类型，如下SQL所示：
mysql> alter table test modify column apply_service_type text;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
重新执行如上插入语句：
mysql> INSERT test(apply_service_type) VALUES('[{"code":"apply_service_type_encrypt","value":"加密服务类"},{"code":"apply_service_type_qrcode","value":"二维码支撑类"},{"code":"apply_service_type_decode","value":"解密服务类"}]');Query OK, 1 row affected (0.01 sec)
使用如下SQL查询可得：
mysql> select * from test;
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | apply_service_type |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | [{"code":"apply_service_type_encrypt","value":"加密服务类"},{"code":"apply_service_type_qrcode","value":"二维码支撑类"},{"code":"apply_service_type_decode","value":"解密服务类"}] |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
为什么将varchar类型修改为longtext或者text就行了呢，这里就涉及到mysql的数据类型。
5. 总结
了解mysql的字符串类型，对于熟练使用mysql存储数据，以及对字符串的性能优化，有着极大的好处。
本文标签：
版权声明：本文标题：解决Data too long for column ‘xxx‘ at row 1问题以及深入理解mysql的字符串数据类型(char,varchar,enum,text,longtext...) 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：https://it.en369.cn/jiaocheng/1700079583a337324.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论