今天遇到一个保持问题比较诡异:
执行sql语句insert into test.COLUMNS select * from information_schema.COLUMNS;报错:ERROR 1406 (22001): Data Too Long, field len 30, data len 48
问题的背景大概是这样子:
1、TiDB4.0分布式数据库系统,去information_schema.TABLES copy一份表结构,然后建一个一模一样的空表到一个临时库csdn上面。
2、在tidb的命令行里面执行 insert into test.COLUMNS select * from information_schema.COLUMNS;
3、尝试mysqldump从information_schema的TABLES表里面导出数据,导入到csdn库上的TABLES表里面去,还是报错。
4、检查了两个库information_schema和csdn库,表结构都是一样的。
看报错,应该是字段只有30的长度,但是录入的数据缺有48个字符串,那么就再仔细检查表结构
CREATE TABLE `TABLES` (
`TABLE_CATALOG` varchar(512) DEFAULT NULL,
`TABLE_SCHEMA` varchar(64) DEFAULT NULL,
`TABLE_NAME` varchar(64) DEFAULT NULL,
`COLUMN_NAME` varchar(64) DEFAULT NULL,
`ORDINAL_POSITION` bigint(64) DEFAULT NULL,
`COLUMN_DEFAULT` text DEFAULT NULL,
`IS_NULLABLE` varchar(3) DEFAULT NULL,
`DATA_TYPE` varchar(64) DEFAULT NULL,
`CHARACTER_MAXIMUM_LENGTH` bigint(21) DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` bigint(21) DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) DEFAULT NULL,
`NUMERIC_SCALE` bigint(21) DEFAULT NULL,
`DATETIME_PRECISION` bigint(21) DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
`COLLATION_NAME` varchar(32) DEFAULT NULL,
`COLUMN_TYPE` text DEFAULT NULL,
`COLUMN_KEY` varchar(3) DEFAULT NULL,
`EXTRA` varchar(30) DEFAULT NULL,
`PRIVILEGES` varchar(80) DEFAULT NULL,
`COLUMN_COMMENT` varchar(1024) DEFAULT NULL,
`GENERATION_EXPRESSION` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
看到有一个字段EXTRA是`EXTRA` varchar(30) DEFAULT NULL,那么就去检查下这个字段里面的数据的值,是否有超过的。
mysql-17:31:41> select length(EXTRA) l,EXTRA from information_schema.TABLES order by l desc limit 50;
+----+--------------------------------------------------+
| l | EXTRA |
+----+--------------------------------------------------+
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
| 48 | DEFAULT_GENERATED on update CURRENT_TIMESTAMP(3) |
检查得到length(extra),确实最大长度是48,难道是字符集的原因吗,因为类似mysql的数据库里面,字符集不一样,占据空间是不一样的。
先看库的字符集,是一样的:
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| csdn | CREATE DATABASE `csdn` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
+--------------------+--------------------------------------------------------------------------------+
| Database | Create Database |
+--------------------+--------------------------------------------------------------------------------+
| INFORMATION_SCHEMA | CREATE DATABASE `INFORMATION_SCHEMA` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+--------------------+--------------------------------------------------------------------------------+
我们再看下表的字符集定义,是否一样,也是一样的:
use information_schema;
CREATE TABLE `TABLES` (
`TABLE_CATALOG` varchar(512) DEFAULT NULL,
`TABLE_SCHEMA` varchar(64) DEFAULT NULL,
`TABLE_NAME` varchar(64) DEFAULT NULL,
`COLUMN_NAME` varchar(64) DEFAULT NULL,
`ORDINAL_POSITION` bigint(64) DEFAULT NULL,
`COLUMN_DEFAULT` text DEFAULT NULL,
`IS_NULLABLE` varchar(3) DEFAULT NULL,
`DATA_TYPE` varchar(64) DEFAULT NULL,
`CHARACTER_MAXIMUM_LENGTH` bigint(21) DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` bigint(21) DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) DEFAULT NULL,
`NUMERIC_SCALE` bigint(21) DEFAULT NULL,
`DATETIME_PRECISION` bigint(21) DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
`COLLATION_NAME` varchar(32) DEFAULT NULL,
`COLUMN_TYPE` text DEFAULT NULL,
`COLUMN_KEY` varchar(3) DEFAULT NULL,
`EXTRA` varchar(30) DEFAULT NULL,
`PRIVILEGES` varchar(80) DEFAULT NULL,
`COLUMN_COMMENT` varchar(1024) DEFAULT NULL,
`GENERATION_EXPRESSION` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
use csdn;
CREATE TABLE `TABLES` (
`TABLE_CATALOG` varchar(512) DEFAULT NULL,
`TABLE_SCHEMA` varchar(64) DEFAULT NULL,
`TABLE_NAME` varchar(64) DEFAULT NULL,
`COLUMN_NAME` varchar(64) DEFAULT NULL,
`ORDINAL_POSITION` bigint(64) DEFAULT NULL,
`COLUMN_DEFAULT` text DEFAULT NULL,
`IS_NULLABLE` varchar(3) DEFAULT NULL,
`DATA_TYPE` varchar(64) DEFAULT NULL,
`CHARACTER_MAXIMUM_LENGTH` bigint(21) DEFAULT NULL,
`CHARACTER_OCTET_LENGTH` bigint(21) DEFAULT NULL,
`NUMERIC_PRECISION` bigint(21) DEFAULT NULL,
`NUMERIC_SCALE` bigint(21) DEFAULT NULL,
`DATETIME_PRECISION` bigint(21) DEFAULT NULL,
`CHARACTER_SET_NAME` varchar(32) DEFAULT NULL,
`COLLATION_NAME` varchar(32) DEFAULT NULL,
`COLUMN_TYPE` text DEFAULT NULL,
`COLUMN_KEY` varchar(3) DEFAULT NULL,
`EXTRA` varchar(30) DEFAULT NULL,
`PRIVILEGES` varchar(80) DEFAULT NULL,
`COLUMN_COMMENT` varchar(1024) DEFAULT NULL,
`GENERATION_EXPRESSION` text NOT NULL,
KEY `idx_pri` (`TABLE_SCHEMA`,`TABLE_NAME`,`COLUMN_KEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
当然了解决办法很简单,就是在csdn库上的TABLES表里面修改字符长度:
alter table csdn.TABLES modify `EXTRA` varchar(128) DEFAULT NULL;
但是为什么information_schema库的TABLES表里面,为什么`EXTRA` varchar(30) DEFAULT NULL里面能存取48个字符呢,这问题值得后续研究下。