如何使用PDO确定列类型？

I need a way to determine the type of a database column (varchar/numeric/date/...) when reading from the DB with PDO.

当从带有PDO的DB读取时,我需要一种方法来确定数据库列的类型(varchar / numeric / date / ...)。

When fetching values from the DB, PDO produces only string values, regardless of the actual type of the table column.

从DB中获取值时,无论表列的实际类型如何,PDO都只生成字符串值。

Is there any non driver specific way to get this information? I know that there are SQL statements that retrieve the types for any given table but i'd prefer a more generic solution.

是否有任何非驱动程序的特定方式来获取此信息?我知道有SQL语句可以检索任何给定表的类型,但我更喜欢更通用的解决方案。

EDIT: PDOStatement::getColumnMeta() is of no use to me, because it's not supported by the PDO driver I use at the moment (Oracle).

编辑:PDOStatement :: getColumnMeta()对我没用,因为我目前使用的PDO驱动程序(Oracle)不支持它。

6 个解决方案

#1

Take a look at this method: PDOStatement->getColumnMeta

看看这个方法:PDOStatement-> getColumnMeta

#2

This is how I did it in my WraPDO class:

这就是我在WraPDO课程中的表现:

$tomet = $sth->getColumnMeta($column_index);
$tomet['type'] = $this->_translateNativeType($tomet['native_type']);

private function _translateNativeType($orig) {
    $trans = array(
        'VAR_STRING' => 'string',
        'STRING' => 'string',
        'BLOB' => 'blob',
        'LONGLONG' => 'int',
        'LONG' => 'int',
        'SHORT' => 'int',
        'DATETIME' => 'datetime',
        'DATE' => 'date',
        'DOUBLE' => 'real',
        'TIMESTAMP' => 'timestamp'
    );
    return $trans[$orig];
}

$sth: PDOStatement->getColumnMeta

#3

It's marked as "experimental", but the PDOStatement->getColumnMeta method looks like it will do what you want.

它标记为“实验性”,但PDOStatement-> getColumnMeta方法看起来会像你想要的那样。

#4

I wrote a function a while ago which extracted table column information. I ended up doing something like this:

我刚才写了一个函数,它提取了表列信息。我最终做了这样的事情:

SHOW COLUMNS FROM <table> WHERE Field = ?

For a typical primary key, that produces this:

对于典型的主键,产生这个:

+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+

I then parsed the output into a usable array. However, that was pre-PHP 5.1.0. Now you can probably use PDOStatement->getColumnMeta.

然后我将输出解析为可用的数组。但是,这是PHP 5.1.0之前的版本。现在你可以使用PDOStatement-> getColumnMeta。

#5

If you're working with Oracle:

如果您正在使用Oracle:

select COLUMN_NAME,
       DATA_TYPE,
       DATA_LENGTH,
       DATA_PRECISION,
       DATA_SCALE
from user_tab_cols
where table_name = '<Table Name>'
order by column_id

but it isn't portable

但它不便携

Many SQL flavours also have

许多SQL风格也有

DESCRIBE <Table Name>

#6

If you're working with Postgres:

如果您正在使用Postgres:

select
    CHARACTER_MAXIMUM_LENGTH,
    COLUMN_NAME,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    NUMERIC_PRECISION,
    NUMERIC_SCALE,
    UDT_NAME 
from
    INFORMATION_SCHEMA.COLUMNS 
where
    TABLE_NAME='table_name'

#1