php MySQL整数字段在PHP中作为字符串返回
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5323146/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL integer field is returned as string in PHP
提问by luca
I have a table field in a MySQL database:
我在 MySQL 数据库中有一个表字段：
userid INT(11)
So I am calling it to my page with this query:
所以我用这个查询将它调用到我的页面：
"SELECT userid FROM DB WHERE name='john'"
Then for handling the result I do:
然后为了处理我做的结果：
$row=$result->fetch_assoc();
$id=$row['userid'];
Now if I do:
现在，如果我这样做：
echo gettype($id);
I get a string. Shouldn't this be an integer?
我得到一个字符串。这不应该是一个整数吗？
回答by Michiel Pater
When you select data from a MySQL database using PHP the datatype will always be converted to a string. You can convert it back to an integer using the following code:
当您使用 PHP 从 MySQL 数据库中选择数据时，数据类型将始终转换为字符串。您可以使用以下代码将其转换回整数：
$id = (int) $row['userid'];Or by using the function intval():
或者通过使用函数intval()：
$id = intval($row['userid']);回答by advait
Use the mysqlnd (native driver) for php.
为 php 使用 mysqlnd（本机驱动程序）。
If you're on Ubuntu:
如果您使用的是 Ubuntu：
sudo apt-get install php5-mysqlnd
sudo service apache2 restart
If you're on Centos:
如果您使用 Centos：
sudo yum install php-mysqlnd
sudo service httpd restart
The native driver returns integer types appropriately.
本机驱动程序适当地返回整数类型。
Edit:
编辑：
As @Jeroen has pointed out, this method will only work out-of-the-box for PDO.
As @LarsMoelleken has pointed out, this method will work with mysqli if you also set the MYSQLI_OPT_INT_AND_FLOAT_NATIVE option to true.
正如@Jeroen 指出的那样，此方法仅适用于 PDO 开箱即用。
正如@LarsMoelleken 指出的那样，如果您还将 MYSQLI_OPT_INT_AND_FLOAT_NATIVE 选项设置为 true，则此方法将适用于 mysqli。
Example:
例子：
$mysqli = mysqli_init();
$mysqli->options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE, TRUE);
回答by mastermind202
My solution is to pass the query result $rsand get a assoc array of the casted data as the return:
我的解决方案是传递查询结果$rs并获取转换数据的关联数组作为返回：
function cast_query_results($rs) {
$fields = mysqli_fetch_fields($rs);
$data = array();
$types = array();
foreach($fields as $field) {
switch($field->type) {
case 3:
$types[$field->name] = 'int';
break;
case 4:
$types[$field->name] = 'float';
break;
default:
$types[$field->name] = 'string';
break;
}
}
while($row=mysqli_fetch_assoc($rs)) array_push($data,$row);
for($i=0;$i<count($data);$i++) {
foreach($types as $name => $type) {
settype($data[$i][$name], $type);
}
}
return $data;
}
Example usage:
用法示例：
$dbconn = mysqli_connect('localhost','user','passwd','tablename');
$rs = mysqli_query($dbconn, "SELECT * FROM Matches");
$matches = cast_query_results($rs);
// $matches is now a assoc array of rows properly casted to ints/floats/strings
回答by Larney
Easiest Solution I found:
我找到的最简单的解决方案：
You can force json_encode to use actual numbers for values that look like numbers:
您可以强制 json_encode 对看起来像数字的值使用实际数字：
json_encode($data, JSON_NUMERIC_CHECK)
(since PHP 5.3.3).
（自 PHP 5.3.3 起）。
Or you could just cast your ID to an int.
或者您可以将您的 ID 转换为 int。
$row = $result->fetch_assoc();
$id = (int) $row['userid'];
回答by BoltClock
No. Regardless of the data type defined in your tables, PHP's MySQL driver always serves row values as strings.
否。无论您的表中定义了何种数据类型，PHP 的 MySQL 驱动程序始终将行值作为字符串提供。
You need to cast your ID to an int.
您需要将您的 ID 转换为 int。
$row = $result->fetch_assoc();
$id = (int) $row['userid'];
回答by d'Artagnan Evergreen Barbosa
I like Chad's answer, especially when the query results will be passed on to javascript in a browser. Javascript deals cleanly with numeric like entities as numbers but requires extra work to deal with numeric like entities as strings. i.e. must use parseInt or parseFloat on them.
我喜欢 Chad 的回答，尤其是当查询结果将传递给浏览器中的 javascript 时。Javascript 将数字类实体作为数字处理干净，但需要额外的工作才能将数字类实体作为字符串处理。即必须对它们使用 parseInt 或 parseFloat 。
Building on Chad's solution I use this and it is often exactly what I need and creates structures that can be JSON encoded for easy dealing with in javascript.
基于 Chad 的解决方案，我使用它，它通常正是我所需要的，并创建了可以进行 JSON 编码的结构，以便在 javascript 中轻松处理。
while ($row = $result->fetch_assoc()) {
// convert numeric looking things to numbers for javascript
foreach ($row as &$val) {
if (is_numeric($val))
$val = $val + 0;
}
}
Adding a numeric string to 0 produces a numeric type in PHP and correctly identifies the type so floating point numbers will not be truncated into integers.
将数字字符串添加到 0 会在 PHP 中生成数字类型并正确识别该类型，因此浮点数不会被截断为整数。
回答by Charlie
This happens when PDO::ATTR_EMULATE_PREPARESis set to trueon the connection.
在连接上PDO::ATTR_EMULATE_PREPARES设置为时会发生这种情况true。
Careful though, setting it to falsedisallows the use of parameters more than once. I believe it also affects the quality of the error messages coming back.
不过要小心，将其设置为false不允许多次使用参数。我相信它也会影响返回的错误消息的质量。
回答by Didax
In my project I usually use an external function that "filters" data retrieved with mysql_fetch_assoc.
在我的项目中，我通常使用一个外部函数来“过滤”用mysql_fetch_assoc.
You can rename fields in your table so that is intuitive to understand which data type is stored.
您可以重命名表中的字段，以便直观地了解存储的数据类型。
For example, you can add a special suffix to each numeric field:
if useridis an INT(11)you can rename it userid_ior if it is an UNSIGNED INT(11)you can rename userid_u.
At this point, you can write a simple PHP function that receive as input the associative array (retrieved with mysql_fetch_assoc), and apply casting to the "value" stored with those special "keys".
例如，您可以为每个数字字段添加一个特殊的后缀：如果userid是 anINT(11)您可以重命名它userid_i或者如果它是一个UNSIGNED INT(11)您可以重命名userid_u。此时，您可以编写一个简单的 PHP 函数，该函数接收关联数组（使用 检索mysql_fetch_assoc）作为输入，并将转换应用于使用这些特殊“键”存储的“值”。
回答by Chad Hedgcock
You can do this with...
你可以用...
...depending on the extension you want to use. The first is not recommended because the mysql extension is deprecated. The third is still experimental.
...取决于您要使用的扩展名。第一个不推荐，因为不推荐使用 mysql 扩展。第三个还在试验中。
The comments at these hyperlinks do a good job of explaining how to set your type from a plain old string to its original type in the database.
这些超链接中的注释很好地解释了如何将类型从普通的旧字符串设置为数据库中的原始类型。
Some frameworks also abstract this (CodeIgniter provides $this->db->field_data()).
一些框架也对此进行了抽象（CodeIgniter 提供$this->db->field_data()）。
You could also do guesswork--like looping through your resulting rows and using is_numeric()on each. Something like:
您也可以进行猜测——比如遍历结果行并在每个行上使用is_numeric()。就像是：
foreach($result as &$row){
foreach($row as &$value){
if(is_numeric($value)){
$value = (int) $value;
}
}
}
This would turn anything that looks like a number into one...definitely not perfect.
这会将任何看起来像数字的东西变成一个……绝对不完美。
回答by Fredrik
If prepared statements are used, the type will be int where appropriate. This code returns an array of rows, where each row is an associative array. Like if fetch_assoc()was called for all rows, but with preserved type info.
如果使用准备好的语句，则类型将在适当的情况下为 int。此代码返回一个行数组，其中每一行都是一个关联数组。就像fetch_assoc()为所有行调用if 一样，但保留了类型信息。
function dbQuery($sql) {
global $mysqli;
$stmt = $mysqli->prepare($sql);
$stmt->execute();
$stmt->store_result();
$meta = $stmt->result_metadata();
$params = array();
$row = array();
while ($field = $meta->fetch_field()) {
$params[] = &$row[$field->name];
}
call_user_func_array(array($stmt, 'bind_result'), $params);
while ($stmt->fetch()) {
$tmp = array();
foreach ($row as $key => $val) {
$tmp[$key] = $val;
}
$ret[] = $tmp;
}
$meta->free();
$stmt->close();
return $ret;
}
