PHP中高效判断MySQL表是否存在的方法与实践

function addSts($database, $brow, $vers, $pag, $lang) {
  $tablename = "sts" . $pag;
  // 尝试插入数据
  $stsinsert = $database->query('INSERT INTO ' . $tablename . '(id, browser, version, language, date) VALUES (NULL, ?, ?, ?, current_timestamp())', $brow, $vers, $lang);

  if ($stsinsert && $stsinsert->affectedRows()) {
    echo "TABLE EXISTS AND DATA INSERTED";
    $database->close();
  } else {
    // 如果插入失败，尝试创建表
    echo "TABLE DOES NOT EXIST -> CREATE TABLE";
    $pagecreation = $database->query('CREATE TABLE ' . $tablename . ' (`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `browser` VARCHAR(20) NOT NULL, `version` VARCHAR(10) NOT NULL,  `language` VARCHAR(5) NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`))');
    if ($pagecreation) {
      // 递归调用以插入数据
      addSts($database, $brow, $vers, $pag, $lang);
    }
  }
}

SELECT 1
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

/**
 * 检查指定数据库中是否存在某个表。
 *
 * @param object $database 数据库连接对象（需提供query和fetch/numRows方法）
 * @param string $dbName 要检查的数据库名称
 * @param string $tableName 要检查的表名称
 * @return bool 如果表存在则返回 true，否则返回 false。
 */
function tableExists($database, $dbName, $tableName) {
    // 构建查询语句，使用参数化查询或确保输入已正确转义以防SQL注入
    $sql = "SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";

    // 执行查询
    // 假设$database->query() 方法返回一个结果对象，该对象有numRows()方法
    $result = $database->query($sql, $dbName, $tableName);

    // 检查查询结果的行数
    if ($result && method_exists($result, 'numRows')) {
        return $result->numRows() > 0;
    } 
    // 对于某些数据库类，可能需要通过fetch方法判断
    // if ($result && $result->fetch()) {
    //     return true;
    // }
    return false;
}

function addSts($database, $dbName, $brow, $vers, $pag, $lang) {
  $tablename = "sts" . $pag;

  // 首先检查表是否存在
  if (tableExists($database, $dbName, $tablename)) {
    echo "TABLE EXISTS -> INSERT DATA";
    $stsinsert = $database->query('INSERT INTO ' . $tablename . '(id, browser, version, language, date) VALUES (NULL, ?, ?, ?, CURRENT_TIMESTAMP())', $brow, $vers, $lang);
    if ($stsinsert && $stsinsert->affectedRows()) {
      echo " -> DATA INSERTED SUCCESSFULLY";
    } else {
      echo " -> FAILED TO INSERT DATA";
    }
  } else {
    echo "TABLE DOES NOT EXIST -> CREATE TABLE";
    $pagecreation = $database->query('CREATE TABLE ' . $tablename . ' (`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, `browser` VARCHAR(20) NOT NULL, `version` VARCHAR(10) NOT NULL,  `language` VARCHAR(5) NOT NULL, `date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`))');

    if ($pagecreation) {
      echo " -> TABLE CREATED SUCCESSFULLY";
      // 表创建成功后，再次调用自身以插入数据
      addSts($database, $dbName, $brow, $vers, $pag, $lang);
    } else {
      echo " -> FAILED TO CREATE TABLE";
    }
  }
}

// 示例调用（假设$database是你的数据库连接对象，'testdb'是你的数据库名）
// addSts($database, 'testdb', 'Chrome', '100', 'homepage', 'en');