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

发布于
2023-12-27 河北

框架：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速度也很快，有点搞不懂。当前查询中的分表是存在的，而且数据库的数据表总量也就近百个。原因在哪里呢？

php 后端数据库

mysql

阅读 1.2k

AI 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 个回答

得票最新

唯一丶

23.1k103743

发布于
2023-12-28 美国

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

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

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

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