获取MySQL查询结果作为其本机数据类型？

I have tried fetching MySQL query results using mysql_fetch_row() and mysql_result() and numeric values are being returned as strings.

我尝试使用mysql_fetch_row（）和mysql_result（）获取MySQL查询结果，并将数值作为字符串返回。

Is there any way to fetch the data as its datatype stored in the table?

有没有办法获取数据作为存储在表中的数据类型？

The application will be querying many different queries so I will be unable to cast the values as the intended datatype on a 1 by 1 basis.

应用程序将查询许多不同的查询，因此我将无法以1为单位将值转换为预期的数据类型。

5 个解决方案

#1

I don't think getting data in their native datatypes (i.e. anything else that strings) can be done in PHP 5.2...

我不认为在他们的本机数据类型（即其他任何字符串）中获取数据可以在PHP 5.2中完成...

In PHP 5.3, it becomes possible, if I remember correctly, when you are using the new (new as in PHP >= 5.3) mysqlnd (MySQL Native Driver) driver.

在PHP 5.3中，如果我没记错的话，当你使用新的（在PHP> = 5.3中为新的）mysqlnd（MySQL Native Driver）驱动程序时，它就成了可能。

After more digging through my bookmarks I found this article about mysqlnd : PDO_MYSQLND: The new features of PDO_MYSQL in PHP 5.3

经过我的书签后，我发现了一篇关于mysqlnd的文章：PDO_MYSQLND：PHP 5.3中PDO_MYSQL的新功能

It says this (quote) :

它说（引用）：

Advantages of using mysqlnd for PDO

使用mysqlnd进行PDO的优点

mysqlnd returns native data types when using Server-side Prepared Statements, for example an INT column is returned as an integer variable not as a string. That means fewer data conversions internally.

使用服务器端预准备语句时，mysqlnd返回本机数据类型，例如，INT列作为整数变量而不是字符串返回。这意味着内部数据转换更少。

But this is PHP 5.3 only (provided your version of PHP 5.3 is compiled with mysqlnd (and not the old libmysql)), and seems to only be the case for prepared statements :-(

但这只是PHP 5.3（假设您的PHP 5.3版本是使用mysqlnd编译的（而不是旧的libmysql）），并且似乎只是预处理语句的情况:-(

Which doesn't quite help, in your situation, I guess...

在你的情况下，哪个不太有帮助，我猜......

And here's another one, still about the new features of mysqlnd, which talks about this for not only prepared statements : PHP: New network traffic, CPU and memory savings with mysqlnd.

这是另一个，仍然是关于mysqlnd的新功能，它不仅讨论了这个准备语句：PHP：新的网络流量，CPU和内存节省与mysqlnd。

Not sure this has been merged into the official mysqlnd driver, though -- best way would be to try ; but it'll still be PHP >= 5.3 only, anyway...

不知道这已经合并到官方的mysqlnd驱动程序，但最好的方法是尝试;但它仍然只是PHP> = 5.3，无论如何......

Another solution would be to have, on the PHP-side, some kind of a mapping-system (like an ORM) to convert results coming from the DB to PHP datatypes...

另一个解决方案是在PHP端使用某种映射系统（如ORM）将来自DB的结果转换为PHP数据类型......

And yes, this is bad if you want to use operators like === and !==, which are type-sensitive...

是的，如果你想使用像===和！==这样的类型敏感的运算符，这很糟糕......

#2

I've implemented this the manual way. It's actually not too bad, just a few lines.

我已经实现了这种手动方式。它实际上并不是太糟糕，只有几行。

As suggested, call mysqli_fetch_fields() on the resource resulting from your query.

如建议的那样，在查询产生的资源上调用mysqli_fetch_fields（）。

Then from a mapping of the PHP field type numbers to MySQL data types (see industrious work here http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php) you can convert your values from the wide range of database types returned as strings by MySQLi, into the appropriate type in PHP.

然后从PHP字段类型编号到MySQL数据类型的映射（请参阅这里的勤奋工作http://www.php.net/manual/en/mysqli-result.fetch-field-direct.php），您可以转换您的值从MySQLi作为字符串返回的各种数据库类型转换为PHP中的相应类型。

How much of a slowdown it is I'm not sure tho.

它减速了多少我不确定。

#3

try this if using mysqli instead of PDO

如果使用mysqli而不是PDO，请尝试此操作

$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, 1);

#4

I wrote a function to circuvent this (for PDO):

我写了一个函数来循环这个（对于PDO）：

/**
 * Converts columns from strings to types according to 
 * PDOStatement::columnMeta
 * 
 * @param PDOStatement $st
 * @param array $assoc returned by PDOStatement::fetch with PDO::FETCH_ASSOC
 * @return copy of $assoc with matching type fields
 */
function convertTypes(PDOStatement $statement, $assoc)
{
    for ($i = 0; $columnMeta = $statement->getColumnMeta($i); $i++)
    {
        $type = $columnMeta['native_type'];

        switch($type)
        {
            case 'DECIMAL':
            case 'TINY':
            case 'SHORT':
            case 'LONG':
            case 'LONGLONG':
            case 'INT24':
                $assoc[$columnMeta['name']] = (int) $assoc[$columnMeta['name']];
                break;
            case 'DATETIME':
            case 'DATE':
            case 'TIMESTAMP':
                $assoc[$columnMeta['name']] = strtotime($assoc[$columnMeta['name']]);
                break;
            // default: keep as string
        }
    }

    return $assoc;
}

Of course the type list are not complete and the conversion is oversimplified, but can be useful for start.

当然，类型列表不完整，转换过于简单，但对于开始可能很有用。

#5

-1

In addition to Pascal MARTIN answer, if you use MySQLi should also work. Try this:

除了Pascal MARTIN的答案，如果你使用MySQLi也应该工作。尝试这个：

<?php
$mysqli = new mysqli("example.com", "user", "password", "database");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
}

if (!$mysqli->query("DROP TABLE IF EXISTS test") ||
    !$mysqli->query("CREATE TABLE test(id INT, label CHAR(1))") ||
    !$mysqli->query("INSERT INTO test(id, label) VALUES (1, 'a')")) {
    echo "Table creation failed: (" . $mysqli->errno . ") " . $mysqli->error;
}

$stmt = $mysqli->prepare("SELECT id, label FROM test WHERE id = 1");
$stmt->execute();
$res = $stmt->get_result();
$row = $res->fetch_assoc();

printf("id = %s (%s)\n", $row['id'], gettype($row['id']));
printf("label = %s (%s)\n", $row['label'], gettype($row['label']));
?>

The above example will output:

以上示例将输出：

id = 1 (integer)
label = a (string)

You can get more info here: https://dev.mysql.com/doc/apis-php/en/apis-php-mysqli.quickstart.prepared-statements.html

您可以在此处获取更多信息：https：//dev.mysql.com/doc/apis-php/en/apis-php-mysqli.quickstart.prepared-statements.html

Hope this helps

希望这可以帮助

#1