MySQL中表创建失败的原因之一

╰+攻爆jí腚メ 2022-08-18 00:56 206阅读 0赞

**报错信息：**

ERROR 1005 (HY000): Can't create table 'example\_db.example\_table' (errno: 150)

**故事背景：**  
从生产环境example\_db数据库往测试环境对应的库导入恢复数据。因为前一天使用mysqldump工具对生产环境整个实例中所有数据库做过一次备份，考虑到再做一次针对example\_db库的备份比较耗时，于是决定直接使用sed工具从原来的完整备份中截取出example\_db对应的部分，然后使用该部分逻辑备份文件恢复测试环境的example\_db数据库.所使用的命令如下：

<pre name="code" class="plain">sed -n '/-- Current Database: `example_db`/,/-- Current Database: `example_db2`/p' fullbakcup.sql > /data/tmp/example_db.sql

其中fullbakcup.sql为已经存在的使用mysqldump做的全备份文件，example\_db2为备份文件中紧随example\_db对应的备份内容之后的数据库，该命令的作用是从备份文件中截取出example\_db对应的备份内容。至此，一切看似没有问题。但是通过如下命令执行时,报出如下错误：

mysql -uuser -p -htest.db.service.com example_db < /data/tmp/example_db.sql
    ERROR 1005 (HY000): Can't create table 'example_db.example_table' (errno: 150)

打开截取生成的备份文件找到创建example\_db.example\_table的语句，发现创建表的语句如下，表中包含外键约束，而外键引用了来自example\_table\_2表的列，而在创建当前表时外键引用的表尚未创建，这应该就是导致表创建失败的原因。

<pre name="code" class="sql">DROP TABLE IF EXISTS `example_table`;
    /*!40101 SET @saved_cs_client     = @@character_set_client */;
    /*!40101 SET character_set_client = utf8 */;
    CREATE TABLE `example_table` (
      `TRIGGER_NAME` varchar(200) NOT NULL,
      `TRIGGER_GROUP` varchar(200) NOT NULL,
      `BLOB_DATA` blob,
      `datachange_lasttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '行更新时间',
      PRIMARY KEY (`TRIGGER_NAME`,`TRIGGER_GROUP`),
      CONSTRAINT `ibfk_1` FOREIGN KEY (`TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `example_table_2` (`TRIGGER_NAME`, `TRIGGER_GROUP`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
    /*!40101 SET character_set_client = @saved_cs_client */;

那么如何避开呢？可以通过两种方法：  
其一,手工调整备份文件中创建表的先后顺序，保证后边的表定义中要引用的表先被创建；  
其二，在创建各个表之前先关闭外键约束检查，在所有的表创建完成之后重新打开外键约束检查；  
第一种方案理论上可行，但实际操作起来较为毛麻烦，而第二种方案只需简单的在备份文件的开始和末尾添加关闭/打开外键约束检查的命令即可，具体的命令为：

<pre name="code" class="sql">--关闭
    SET FOREIGN_KEY_CHECKS=0
    --打开
    SET FOREIGN_KEY_CHECKS=1

按照这个思路在截取出的备份文件首尾分别添加以上两条命令，再次执行

mysql -uuser -p -htest.db.service.com example_db < /data/tmp/example_db.sql

发现可以成功运行备份文件中的命令了。  
  
我们平时使用mysqldump工具备份数据库时，也没有明确的指明在备份文件首尾添加控制外键约束的检查啊，为什么不会出现创建数据库失败的情形呢？其实仔细查看完整的备份文件会发现，备份文件的首尾分别存在如下内容，其中分别包含了关闭和开启外键约束检查的命令，不仅如此，还包含进了其他一些设置命令如设置客户端字符集、结果字符集、时区、唯一索引检查、SQL Mode等信息，也就是说mysqldump工具已经默认帮我们做好了这些。所以在从完整备份文件中截取内容片断是最好在截取结果的首尾添加如下部分的内容以避免错误并提高性能。

--头部
    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8 */;
    /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
    /*!40103 SET TIME_ZONE='+00:00' */;
    /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
    /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
    --尾部
    /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
    /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
    /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
    /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
    /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;