云数据库RDS

    使用mysqldump导入GeneratedColumn报错问题

    背景

    MySQL在5.7版本中提供了Generated Column的功能,它可以通过函数计算获得新的一列数据,我们可以通过在虚拟列加索引的方式提高查询效率、分区表可使用虚拟列进行分区等。

    问题现象

    在使用低版本mysqldump命令做有虚拟列表的导入导出操作时,报错:

    ERROR 3105 (HY000) at line 38: The value specified for generated column 'col1' in table 'tb_01' is not allowed.

    原因分析

    mysqldump版本低于5.7.9的话,会误导出虚拟列数据,但是虚拟列是不允许被插入的,因此产生以上报错。

    Changes in MySQL 5.7.9:mysqldump used incorrect syntax for generated column definitions. (Bug #20769542)

    解决方案

    使用5.7.9以上版本的mysqldump工具导出含Generated Column的表的数据。

    案例复现

    1、测试表和数据

     CREATE TABLE triangle (
      sidea double DEFAULT NULL,
      sideb double DEFAULT NULL,
      sidec double GENERATED ALWAYS AS (sqrt(((sidea * sidea) + (sideb * sideb)))) VIRTUAL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    insert into triangle(sidea,sideb)values(1,1),(3,4),(6,8);

    2、分别使用不同版本的mysqldump导出数据

    • 使用低版本mysqldump导出数据示例
    LOCK TABLES `triangle` WRITE;
    /*!40000 ALTER TABLE `triangle` DISABLE KEYS */;
    INSERT INTO `triangle` VALUES (1,2,2.23606797749979),(3,4,5),(2,9,9.219544457292887);
    /*!40000 ALTER TABLE `triangle` ENABLE KEYS */;
    UNLOCK TABLES;
    • 使用高版本mysqldump导出数据示例
    LOCK TABLES `triangle` WRITE;
    /*!40000 ALTER TABLE `triangle` DISABLE KEYS */;
    INSERT INTO `triangle` (`sidea`, `sideb`) VALUES (1,2),(3,4),(2,9);
    /*!40000 ALTER TABLE `triangle` ENABLE KEYS */;
    UNLOCK TABLES;

    总结建议

    建议mysqldump与MySQL Server版本对齐,避免使用较低版本导致类似Generated Column的新特性带来的兼容性问题。

    上一篇
    为什么mysql的ibdata1文件会不断增大
    下一篇
    MySQL5.7ibtmp暴涨导致磁盘打满问题