使用PHP操作MySQL执行重命名表时出现表被意外删除的问题

• 补全SQL执行错误检测：现有代码未判断每一条SQL语句的执行结果，首先要在每一次query执行后增加错误返回判断，比如使用PDO时开启异常捕获模式，使用MySQLi时读取error属性输出错误信息，确认CREATE TABLE、DROP、RENAME三个步骤分别是否执行成功、报错信息是什么。
• 校验实际执行的SQL语句：可以临时开启MySQL的通用查询日志（general_log），确认脚本实际传入数据库执行的SQL语句与预期是否一致，重点检查$tableSuffix、$tableSuffix2变量是否被意外覆盖、替换后的值是否符合预期。
• 排查重复执行问题：由于脚本由浏览器端触发，容易出现用户刷新页面、请求重复提交的情况，可在脚本入口增加执行日志，记录请求时间、参数，确认是否出现短时间内多次执行的情况：如果第一次执行成功生成了testing_$tableSuffix，第二次执行时会先删除这个新生成的正式表，若后续RENAME执行失败，就会出现两张表都不存在的现象。
• 校验数据库账号权限：确认当前数据库操作账号是否拥有RENAME TABLE的对应权限，权限不足会导致重命名语句直接执行失败。

• 调整表替换逻辑，使用原子操作：不要先删除旧表再重命名新表，改成使用RENAME TABLE的原子多表重命名特性，全程不先删旧表，避免中间状态出错导致数据丢失，示例逻辑如下：

$tableSuffix = "1";
$tableSuffix2 = "2";

// 1. 先创建新表、写入所需数据，确认创建成功
$createRes = $db->query("CREATE TABLE testing_{$tableSuffix2} LIKE template_testing");
if (!$createRes) {
    // 抛出错误、终止执行
    die("新表创建失败:".$db->error);
}
// 此处可加新表存在性校验、数据校验

// 2. 原子执行新旧表替换，要么全成功要么全失败
$renameRes = $db->query("RENAME TABLE testing_{$tableSuffix} TO testing_{$tableSuffix}_old, testing_{$tableSuffix2} TO testing_{$tableSuffix}");
if (!$renameRes) {
    // 抛出错误、终止执行，此时旧表依然可用
    die("表替换失败:".$db->error);
}

// 3. 确认业务运行无异常后，再手动/自动删除备份的旧表
// $db->query("DROP TABLE testing_{$tableSuffix}_old");

• 增加错误兜底：如果使用支持DDL事务的MySQL 8.0+版本，可将所有操作放入事务中，任意步骤出错直接回滚；低版本MySQL可在任意步骤执行失败时，立即触发回滚操作（比如把改名的旧表改回原名）。
• 增加防重机制：脚本执行前加分布式锁或者文件锁，同一个参数的操作同一时间只能执行一次，避免重复请求导致逻辑混乱。
• 所有操作增加前置校验：执行DROP、RENAME前先确认目标表存在，避免操作不存在的表导致逻辑异常。

内容的提问来源于stack exchange，提问作者stackbee