php在循环中使用 SHOW TABLES LIKE 'table'; 为什么会出现 2006, MySQL server has gone away?

框架:thinkphp
场景:在脚本中循环查询数据并处理,查询数据使用的model中含有分表逻辑,若分表不存在则自动创建分表。
model代码:

class TestTableModel {
    public function getTable($date) {
        $r = Db::query('show tables like "test_table_{$date}"');
        if (empty($r)) {  // 若表不存在,则创建
            Db::execute('创建 test_table_{$date} 表');
        }
        // 使用Db对象
        return Db::table('test_table_{$date}');
    }
}

脚本代码:

$date = '20230506';
$page = 1;  // 分页页码
while (1) {
     $db = (new TestTableModel)->getTable($date)->page($page)->select();
    ++$page;
    // 进行一些处理
    (......省略)
}

这个循环只要一执行就瞬间报出 2006, MySQL server has gone away 的错误。最一开始我以为是和mysql的连接超时导致的,但后来将model中的 show tables like 这句去掉之后,代码就能正常运行了,而且运行很长很长时间也不会报错。
调整后能正确运行的model:

class TestTableModel {
    public function getTable($date) {
        // 使用Db对象
        return Db::table('test_table_{$date}');
    }
}

所以现在的问题就是,为什么加上 show tables like 这句查询就会导致 2006, MySQL server has gone away ? 然而如果这个model不在循环中使用也不会报错,而且在mysql中执行show tables like速度也很快,有点搞不懂。当前查询中的分表是存在的,而且数据库的数据表总量也就近百个。原因在哪里呢?

阅读 1.2k
avatarAI BotBETA

出现 "2006, MySQL server has gone away" 错误的原因可能是由于在循环中频繁执行 show tables like 查询,导致 MySQL 服务器认为连接已经断开。MySQL 的默认连接超时时间是 28800 秒(8 小时),如果在这段时间内没有活动,服务器就会关闭连接。

在您的情况中,当使用 show tables like 查询时,每次查询都会打开一个新的事务,查询结束后事务并没有被正确地提交或关闭,导致连接一直被占用。在循环中频繁执行这样的查询,连接很快就会被耗尽,从而导致 "MySQL server has gone away" 错误。

解决这个问题的方法是优化您的代码,避免在循环中频繁执行 show tables like 查询。如果分表逻辑是固定的,可以考虑将分表信息预先加载到内存中,而不是在每次循环时都查询一次。如果分表逻辑需要根据某些条件动态生成,可以考虑使用缓存机制来缓存查询结果,避免重复查询。

另外,您也可以考虑增加 MySQL 服务器的连接超时时间,但这并不是解决问题的根本方法,因为长时间保持空闲连接会浪费服务器资源。

调整后的模型代码示例:

class TestTableModel {
    public function getTable($date) {
        // 预先加载分表信息,根据实际情况修改逻辑
        static $tables = [];
        if (!isset($tables[$date])) {
            $tables[$date] = Db::query('show tables like "test_table_{$date}"')->fetch();
        }
        
        // 使用缓存的分表信息创建表名
        $tableName = $tables[$date]['Tables_in_database'];
        
        // 使用Db对象
        return Db::table($tableName);
    }
}

这样修改后,分表信息会被预先加载到 $tables 数组中,并在后续的查询中使用缓存的分表信息,避免了频繁查询 show tables like。您可以根据自己的实际需求调整预加载逻辑和缓存机制。

2 个回答

可能的原因会很多,可以参考 MySQL 手册 中的说明去解决。

如果你只想简单解决问题,那有更简单的方法,就是自动重连,参考 ThinkPHP 手册中关于 断线重连 的配置介绍。


另外,你这种方式会存在一些意外风险,在 MySQL 中,DDL 语句是会自动提交事务的,如果你的代码在事务中运行,且这个表不存在的时候,走到了创建表的语句,就有可能导致事务被意外的提交了,基于这个原因,你得建表操作最好在外部完成。

除此之外,在一些情况下,你还可以有其他的优化方案来查询你这个 show tables,你可以在循环外面使用:show tables like 'test_table_%',将指定前缀的表取出来,存为变量,然后用 php 去判断就好了,而不是现在这样每次都查询。如果循环比较多,可能在这个期间创建了表,那你还可以考虑每 N 个或者每 N 秒后,执行一下上面的语句,重新覆盖一下。

既然已经试用了Model,默认你使用ThinkPHP框架吧,在Thinkphp框架下使用Model层,可以在脚本结束时候最后释放链接。

比如:Db::connect()->close();
记住把对应的库关闭

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
推荐问题