萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 取得 MySQL 資料表欄位名稱完整範例

動態取得資料表欄位名稱是建構通用 CRUD、匯出工具、動態表單的基礎。本文展示使用 PDO(現代標準)與 mysqli 兩種方式,並說明避坑指南。

方法一:PDO 方式(推薦)

PDO 提供統一介面、支援預處理語句、例外處理,是現代 PHP 專案首選。

<?php
function getTableColumnsPDO(PDO $pdo, string $table, string $database = null): array {
    $sql = "SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT, COLUMN_KEY, IS_NULLABLE, COLUMN_DEFAULT
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
            ORDER BY ORDINAL_POSITION";
    
    $db = $database ?? $pdo->query("SELECT DATABASE()")->fetchColumn();
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$db, $table]);
    
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

// 使用範例
$pdo = new PDO("mysql:host=localhost;dbname=testdb;charset=utf8mb4", 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

$columns = getTableColumnsPDO($pdo, 'users');
foreach ($columns as $col) {
    echo "{$col['COLUMN_NAME']} ({$col['DATA_TYPE']}) - {$col['COLUMN_COMMENT']}\n";
}

方法二:mysqli 方式

<?php
function getTableColumnsMySQLi(mysqli $mysqli, string $table, string $database = null): array {
    $db = $database ?? $mysqli->query("SELECT DATABASE()")->fetch_row()[0];
    $sql = "SELECT COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT, COLUMN_KEY, IS_NULLABLE, COLUMN_DEFAULT
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
            ORDER BY ORDINAL_POSITION";
    
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param('ss', $db, $table);
    $stmt->execute();
    $result = $stmt->get_result();
    
    return $result->fetch_all(MYSQLI_ASSOC);
}

// 使用範例
$mysqli = new mysqli('localhost', 'user', 'pass', 'testdb');
$mysqli->set_charset('utf8mb4');
$columns = getTableColumnsMySQLi($mysqli, 'users');
print_r($columns);

方法三:SHOW COLUMNS / DESCRIBE(簡易但功能較少)

<?php
// 僅需欄位名稱陣列時
function getColumnNamesSimple(PDO $pdo, string $table): array {
    $stmt = $pdo->query("SHOW COLUMNS FROM `$table`");
    return $stmt->fetchAll(PDO::FETCH_COLUMN, 0); // 只取 Field 欄位
}

// 或 DESCRIBE
$stmt = $pdo->query("DESCRIBE `$table`");
$names = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);

進階應用:動態建構 INSERT/UPDATE SQL

<?php
function buildInsertSQL(PDO $pdo, string $table, array $data): array {
    $columns = getTableColumnsPDO($pdo, $table);
    $allowed = array_column($columns, 'COLUMN_NAME');
    
    // 過濾資料:只保留資料表存在的欄位,排除自增主鍵
    $filtered = array_intersect_key($data, array_flip($allowed));
    foreach ($columns as $col) {
        if ($col['COLUMN_KEY'] === 'PRI' && strpos($col['COLUMN_DEFAULT'], 'auto_increment') !== false) {
            unset($filtered[$col['COLUMN_NAME']]);
        }
    }
    
    $fields = implode(', ', array_map(fn($c) => "`$c`", array_keys($filtered)));
    $placeholders = implode(', ', array_map(fn($c) => ":$c", array_keys($filtered)));
    $sql = "INSERT INTO `$table` ($fields) VALUES ($placeholders)";
    
    return [$sql, $filtered];
}

// 使用
[$sql, $params] = buildInsertSQL($pdo, 'users', $_POST);
$stmt = $pdo->prepare($sql);
$stmt->execute($params);

避坑指南與最佳實務

  • 資安:欄位名稱雖來自 schema,但動態 SQL 仍需用反引號包裹識別符,防止保留字衝突
  • 權限:資料庫帳號需具備 `INFORMATION_SCHEMA` 讀取權限(通常預設即有)
  • 大小寫敏感:Linux 下表名大小寫敏感,查詢時建議統一小寫或配合 `lower_table_names` 設定
  • 效能:Schema 查詢可快取(Redis/APCu),資料表結構變更頻率低
  • 棄用 `mysql_*`:原範例使用 `mysql_list_fields` 已在 PHP 7.0 移除,務必遷移至 PDO/mysqli
copyright © 萬盛學電腦網 all rights reserved