如何通過PDOStatement::getColumnMeta 函數準確獲取數據庫列名和數據類型？

gitbox 2025-07-10
<span><span><span class="hljs-meta">&lt;?php</span></span><span>
</span><span><span class="hljs-comment">// 這部分代碼與文章內容無關，僅用於展示分隔前的內容</span></span><span>
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"歡迎閱讀本文，本文將詳細講解 PDOStatement::getColumnMeta 函數的使用。"</span></span><span>;
</span><span><span class="hljs-meta">?&gt;</span></span><span>

&lt;hr&gt;

</span><span><span class="hljs-meta">&lt;?php</span></span><span>
<span class="hljs-comment">/*
 * 如何通過 PDOStatement::getColumnMeta 函數準確獲取數據庫列名和數據類型？
 *
 * 在使用 PHP 的 PDO 擴展操作數據庫時，獲取查詢結果的元數據非常重要，特別是在動態生成表結構、數據導出或數據驗證時。
 * PDOStatement::getColumnMeta 是 PDO 提供的一個方法，用於獲取結果集中指定列的元信息，幫助我們獲取列名、數據類型等信息。
 *
 * 一、函數簡介
 * PDOStatement::getColumnMeta(int $column) ：
 *   - 參數 $column 是列的索引（從0開始）。
 *   - 返回值是一個關聯數組，包含該列的元數據，如列名（name）、數據類型（native_type）、長度（len）、是否允許 NULL（flags）等。
 *
 * 二、使用示例
 * 下面以 MySQL 為例，演示如何通過 PDOStatement::getColumnMeta 獲取列名和數據類型。
 */</span>

</span><span><span class="hljs-keyword">try</span></span><span> {
    </span><span><span class="hljs-variable">$dsn</span></span><span> = </span><span><span class="hljs-string">"mysql:host=localhost;dbname=testdb;charset=utf8mb4"</span></span><span>;
    </span><span><span class="hljs-variable">$username</span></span><span> = </span><span><span class="hljs-string">"root"</span></span><span>;
    </span><span><span class="hljs-variable">$password</span></span><span> = </span><span><span class="hljs-string">""</span></span><span>;
    </span><span><span class="hljs-variable">$options</span></span><span> = [
        PDO::</span><span><span class="hljs-variable constant_">ATTR_ERRMODE</span></span><span> =&gt; PDO::</span><span><span class="hljs-variable constant_">ERRMODE_EXCEPTION</span></span><span>,
    ];

    </span><span><span class="hljs-variable">$pdo</span></span><span> = </span><span><span class="hljs-keyword">new</span></span><span> </span><span><span class="hljs-title function_ invoke__">PDO</span></span><span>(</span><span><span class="hljs-variable">$dsn</span></span><span>, </span><span><span class="hljs-variable">$username</span></span><span>, </span><span><span class="hljs-variable">$password</span></span><span>, </span><span><span class="hljs-variable">$options</span></span><span>);

    </span><span><span class="hljs-variable">$sql</span></span><span> = </span><span><span class="hljs-string">"SELECT id, username, email, created_at FROM users LIMIT 1"</span></span><span>;
    </span><span><span class="hljs-variable">$stmt</span></span><span> = </span><span><span class="hljs-variable">$pdo</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">query</span></span><span>(</span><span><span class="hljs-variable">$sql</span></span><span>);

    </span><span><span class="hljs-variable">$columnCount</span></span><span> = </span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">columnCount</span></span><span>();

    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"表字段信息：\n"</span></span><span>;
    </span><span><span class="hljs-keyword">for</span></span><span> (</span><span><span class="hljs-variable">$i</span></span><span> = </span><span><span class="hljs-number">0</span></span><span>; </span><span><span class="hljs-variable">$i</span></span><span> &lt; </span><span><span class="hljs-variable">$columnCount</span></span><span>; </span><span><span class="hljs-variable">$i</span></span><span>++) {
        </span><span><span class="hljs-variable">$meta</span></span><span> = </span><span><span class="hljs-variable">$stmt</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">getColumnMeta</span></span><span>(</span><span><span class="hljs-variable">$i</span></span><span>);
        </span><span><span class="hljs-comment">// 獲取列名</span></span><span>
        </span><span><span class="hljs-variable">$columnName</span></span><span> = </span><span><span class="hljs-variable">$meta</span></span><span>[</span><span><span class="hljs-string">'name'</span></span><span>] ?? </span><span><span class="hljs-string">'未知列名'</span></span><span>;
        </span><span><span class="hljs-comment">// 获取数据库原生數據類型</span></span><span>
        </span><span><span class="hljs-variable">$nativeType</span></span><span> = </span><span><span class="hljs-variable">$meta</span></span><span>[</span><span><span class="hljs-string">'native_type'</span></span><span>] ?? </span><span><span class="hljs-string">'未知類型'</span></span><span>;
        </span><span><span class="hljs-comment">// 有些数据库返回的字段長度信息</span></span><span>
        </span><span><span class="hljs-variable">$length</span></span><span> = </span><span><span class="hljs-variable">$meta</span></span><span>[</span><span><span class="hljs-string">'len'</span></span><span>] ?? </span><span><span class="hljs-string">'未知長度'</span></span><span>;

        </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"列索引 <span class="hljs-subst">{$i}</span></span></span><span>: 名稱 = </span><span><span class="hljs-subst">{$columnName}</span></span><span>, 類型 = </span><span><span class="hljs-subst">{$nativeType}</span></span><span>, 長度 = </span><span><span class="hljs-subst">{$length}</span></span><span>\n";
    }

} </span><span><span class="hljs-keyword">catch</span></span><span> (PDOException </span><span><span class="hljs-variable">$e</span></span><span>) {
    </span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"數據庫錯誤: "</span></span><span> . </span><span><span class="hljs-variable">$e</span></span><span>-&gt;</span><span><span class="hljs-title function_ invoke__">getMessage</span></span><span>();
}

<span class="hljs-comment">/*
 * 三、注意事項
 * 1. 不同數據庫驅動返回的元數據字段可能有所不同，需根據實際情況處理。
 * 2. getColumnMeta 僅對已經執行的查詢有效，且有些驅動不完全支持該函數。
 * 3. native_type 是数据库驱动返回的原生類型，和 PHP 類型不完全对应，若需轉換請自行映射。
 *
 * 四、總結
 * 通過 PDOStatement::getColumnMeta，可以方便地获取查询结果的列名和數據類型，增強程序的動態處理能力。
 * 結合 columnCount 方法，可以遍歷所有字段信息，實現更靈活的數據庫操作。
 */</span>
</span><span><span class="hljs-meta">?&gt;</span></span><span>
</span></span>
相關標籤:
PDOStatement
PDOStatement::getColumnMeta