PHP与MySQL：防止DDL重复执行方法

问题背景：重复执行DDL的困境

在Web开发中，我们经常需要初始化数据库结构，例如创建表。然而，如果将数据库定义语言（DDL）语句（如CREATE TABLE）直接放置在每次页面加载时都会执行的PHP脚本中，就会遇到一个常见问题：在表已经存在的情况下，再次尝试创建会引发数据库错误。例如，以下PHP代码片段展示了这种常见错误：

Connection Error: " . mysqli_connect_error() . "
");

// 创建表
$sql = "create table students2 (
    id int not null auto_increment,
    first varchar(20), 
    last varchar(20), 
    dob date, primary key (id))";

$create = mysqli_query($conn,$sql);
if ($create)
    echo "Created";
else
    echo "Error creating table: " . mysqli_error($conn);

html_bottom();
?>

这段代码的意图是创建名为 students2 的表。首次运行成功，但随后的每次页面刷新都会因为 students2 表已存在而报错，提示“Table 'students2' already exists”。这不仅影响用户体验，也暴露出代码设计上的不足。

解决方案一：使用CREATE TABLE IF NOT EXISTS

MySQL提供了一个非常有用的语法糖来解决重复创建表的问题：IF NOT EXISTS。当这个子句与CREATE TABLE命令一起使用时，如果指定的表已经存在，数据库将不会执行创建操作，也不会报错。

示例代码：

优点：

• 简单快捷： 只需要在SQL语句中添加一个短语，即可立即解决报错问题。
• 避免错误： 有效防止了因表已存在而导致的运行时错误。

局限性：

• 性能开销： 即使表已存在，数据库仍然会解析并执行这个查询（尽管不会实际创建表），在每次页面加载时执行DDL查询，这在大型应用中并非最佳实践。
• 职责不清： 将DDL逻辑与应用运行时逻辑混杂在一起，不利于代码的模块化和维护。
• 不适用于复杂迁移： 对于需要进行复杂数据库结构变更（如添加/修改列、索引等）的场景，IF NOT EXISTS无法提供足够的灵活性和控制。

解决方案二：分离DDL到独立初始化脚本（推荐）

更专业和健壮的做法是将数据库的DDL操作与应用程序的运行时逻辑完全分离。这意味着DDL语句应该放在一个独立的初始化脚本中，该脚本只在以下情况执行：

1. 首次部署应用时。
2. 数据库结构需要更新（即进行数据库迁移）时。

这种方法遵循了“职责分离”的原则，使得代码结构更清晰，更易于管理和维护。

实现思路：

1. 创建独立的初始化脚本： 创建一个名为 init_db.php 或 setup.php 的文件，其中包含所有用于创建表、插入初始数据等DDL和DML语句。

   init_db.php 示例：

   Connection Error: " . mysqli_connect_error() . "
   ");
   }
   
   echo "Attempting to initialize database...\n";
   
   // 创建 students2 表
   $sql_create_students = "CREATE TABLE IF NOT EXISTS students2 (
       id INT NOT NULL AUTO_INCREMENT,
       first VARCHAR(20), 
       last VARCHAR(20), 
       dob DATE, 
       PRIMARY KEY (id)
   )";
   
   if (mysqli_query($conn, $sql_create_students)) {
       echo "Table 'students2' created or already exists.\n";
   } else {
       echo "Error creating table 'students2': " . mysqli_error($conn) . "\n";
   }
   
   // 可以添加更多DDL或初始数据插入语句
   // 例如：
   // $sql_insert_data = "INSERT INTO students2 (first, last, dob) VALUES ('John', 'Doe', '2000-01-01')";
   // if (mysqli_query($conn, $sql_insert_data)) {
   //     echo "Initial data inserted.\n";
   // } else {
   //     echo "Error inserting initial data: " . mysqli_error($conn) . "\n";
   // }
   
   mysqli_close($conn);
   echo "Database initialization complete.\n";
   ?>

2. 执行初始化脚本： 这个脚本不应该在每次页面加载时执行。它应该通过命令行手动执行一次，或者在部署流程中作为一次性步骤执行。

   php init_db.php

3. 应用程序代码： 在应用程序的常规页面脚本中，只包含进行数据查询和操作（DML）的代码，不再包含DDL。

   常规应用页面（例如 index.php）：

   Connection Error: " . mysqli_connect_error() . "
   ");
   
   // 这里只执行DML操作，例如查询数据
   $result = mysqli_query($conn, "SELECT * FROM students2");
   if ($result) {
       echo "
   Students List:
   ";
       echo "
   ";
       while ($row = mysqli_fetch_assoc($result)) {
           echo "
   " . htmlspecialchars($row['first']) . " " . htmlspecialchars($row['last']) . "
   ";
       }
       echo "
   ";
       mysqli_free_result($result);
   } else {
       echo "Error fetching students: " . mysqli_error($conn);
   }
   
   mysqli_close($conn);
   html_bottom();
   ?>

优点：

• 职责分离： DDL与DML清晰分离，代码结构更清晰。
• 性能优化： DDL查询只在需要时执行，避免了不必要的数据库开销。
• 易于部署和维护： 数据库初始化和升级过程可控，便于版本控制和自动化部署。
• 专业性： 这是大型项目和生产环境中的标准实践。
• 支持数据库迁移： 对于更复杂的数据库结构变更，这种模式可以很容易地扩展到使用专业的数据库迁移工具（如Laravel Migrations, Doctrine Migrations等），这些工具能够跟踪数据库状态，并按顺序执行一系列结构变更。

最佳实践与注意事项

• 选择合适的方案：
  • 对于小型、一次性或快速原型开发，CREATE TABLE IF NOT EXISTS可能足够方便。
  • 对于任何需要长期维护、部署到生产环境或团队协作的项目，强烈推荐使用独立的初始化/迁移脚本。
• 版本控制： 将你的初始化脚本或迁移文件纳入版本控制系统（如Git），这样可以跟踪数据库结构的历史变更。
• 数据库迁移工具： 对于更复杂的应用，考虑使用框架自带的数据库迁移工具（例如Laravel的Artisan Migrate、Symfony的Doctrine Migrations）。这些工具提供了更强大的功能，如版本管理、回滚、批处理等。
• 错误处理： 无论选择哪种方法，始终确保对数据库操作进行适当的错误处理，以便在出现问题时能够及时发现并解决。

总结

避免PHP脚本在每次页面刷新时重复执行DDL操作是数据库应用开发中的一个基本而重要的实践。通过采用CREATE TABLE IF NOT EXISTS可以快速解决错误提示问题，但更推荐且专业的做法是将DDL操作从常规应用逻辑中分离出来，放入独立的初始化或迁移脚本中。这种分离不仅提升了代码的清晰度和可维护性，也为应用的长期发展和数据库结构的演进奠定了坚实的基础。