mysql迁移gaussdb字段长度导致数据写入失败

问题描述

从mysql迁移数据到gaussdb数据库，两边表结构的字段长度一致，但是数据写入时提示提示ERROR: value too long for type character(30)报错

问题排查

经过查找资料是由于mysql和gaussdb数据库对于字符类型长度定义的度量单位不同导致，下面是该问题详细验证步骤。

mysql库

查看mysql数据库的版本及字符集设置

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+

mysql> show variables like 'character_set%';
+--------------------------+----------------------------------------------------------------+
| Variable_name            | Value                                                          |
+--------------------------+----------------------------------------------------------------+
| character_set_client     | utf8                                                           |
| character_set_connection | utf8                                                           |
| character_set_database   | utf8                                                           |
| character_set_filesystem | binary                                                         |
| character_set_results    | utf8                                                           |
| character_set_server     | utf8                                                           |
| character_set_system     | utf8                                                           |
| character_sets_dir       | /usr/local/mysql-8.0.23-linux-glibc2.12-x86_64/share/charsets/ |
+--------------------------+----------------------------------------------------------------+
8 rows in set (0.02 sec)

验证mysql字符类型定义长度是按照字节计算还是字符计算

 create table t1(a char(5),b varchar(5));
--插入英文字符
insert into t1 values('abcde','abcde');
mysql> select * from t1;
+-------+-------+
| a     | b     |
+-------+-------+
| abcde | abcde |
+-------+-------+
1 row in set (0.00 sec)

--插入中文字符
insert into t1 values('北京你好呀','北京你好呀');
mysql> select * from t1;
+-----------------+-----------------+
| a               | b               |
+-----------------+-----------------+
| abcde           | abcde           |
| 北京你好呀      | 北京你好呀      |
+-----------------+-----------------+
2 rows in set (0.00 sec)

从上面的验证结果可知，在mysql8.0版本下，char、varchar字段定义是以为字符长度单位

MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）；MySQL5.0版本以上，varchar(20)：指的是20字符

opengauss库

查看opengauss数据库的版本及字符集设置

openGauss=# select version();
                                                                       version                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------
 (openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)


openGauss=# select datname,pg_encoding_to_char("encoding") from pg_catalog.pg_database where datname='test';
 datname | pg_encoding_to_char 
---------+---------------------
 test    | UTF8
(1 row)

验证opengauss字符类型定义长度是按照字节计算还是字符计算

 create table t1(a char(5),b varchar(5));
--插入英文字符
test=# insert into t1 values('abcde','abcde');
INSERT 0 1
test=# select * from t1;
   a   |   b   
-------+-------
 abcde | abcde
(1 row)
--插入中文字符
test=# insert into t1 values('北京你好呀','北京你好呀');
ERROR:  value too long for type character(5)
CONTEXT:  referenced column: a
test=# insert into t1 values('北京','北京');
ERROR:  value too long for type character(5)
CONTEXT:  referenced column: a
test=# insert into t1 values('北','北');
INSERT 0 1
--当按照之前用例插入5个字符时报错，当减少字符为一个汉字时，插入成功。
这是由于utf-8下一个汉子占用3个字节，定义字段长度为5个字节时，只能存储1个汉子符合预期

通过上面的测试验证可知，opengauss字符类型定义长度是按照字节计算

总结

在mysql8.0版本，char、varchar字段定义是以为字符长度单位，而opengauss字符类型定义长度是按照字节计算，所以当从mysql迁移到opengauss或者其他pg系数据时，需要考虑当字符类型字段存储的数据中含有中文字符时，pg系中字符类型长度应该为mysql字符类型长度的3倍。
– / END / –

可以通过下面的方式联系我

• 微信公众号：@墨竹札记
• 墨天轮：@墨竹
• 微信：wshf395062788
• PGFans：@墨竹

如果这篇文章为你带来了灵感或启发，就请帮忙点赞、收藏、转发；如果文章中不严谨或者错漏之处，请及时评论指正。非常感谢！