pdo 查询 mysql 返回值 int 转 string 类型的问题

前言

   因为本地接口开发时，php 使用 mysqli 扩展，发现测试环境没有使用 mysqli，所以更换成了 pdo，随后对接口进行测试的时候发现，返回值的整型字段数据全都变为字符型。

查错

    因为更改前没有发生这个问题，所以大致判断是 pdo 做了某些处理导致的这个问题。
    其中，发现  
    PDO::ATTR_STRINGIFY_FETCHES（提取的时候将数值转换为字符串）  
    PDO::ATTR_EMULATE_PREPARES（启用或禁用预处理语句的模拟）

    这两个预定义之间会有一些影响，我们来做个实验验证下

实验

$pdo = new PDO($dsn, $user, $pass, $options);


/* 实验1，结果 int 不转为 string */  

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

/* 实验2，结果 int 转为 string */  

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);

/* 实验3，结果 int 转为 string */  

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);

/* 实验4，结果 int 转为 string */  

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, false);

转载于：http://blog.csdn.net/lipeigang1109/article/details/54971285 

自己测试了下:PHP-5.4.39(内置驱动mysqlnd 5.0.10)

创建测试表和插入数据:

create table test(
    c1 int, 
    c2 float, 
    c3 float(10,2), 
    c4 double, 
    c5 double(10,2), 
    c6 decimal(10,2), 
    PRIMARY KEY (c1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into test values(32.10, 32.10, 32.10, 32.10, 32.10, 32.10);
insert into test values(43.21, 43.21, 43.21, 43.21, 43.21, 43.21);
insert into test values(9876543.21, 9876543.21, 9876543.21, 9876543.21, 9876543.21, 9876543.21);
select * from test;
+---------+---------+------------+------------+------------+------------+
| c1      | c2      | c3         | c4         | c5         | c6         |
+---------+---------+------------+------------+------------+------------+
|      32 |    32.1 |      32.10 |       32.1 |      32.10 |      32.10 |
|      43 |   43.21 |      43.21 |      43.21 |      43.21 |      43.21 |
| 9876543 | 9876540 | 9876543.00 | 9876543.21 | 9876543.21 | 9876543.21 |
+---------+---------+------------+------------+------------+------------+

PDO查询var_dump输出:

<?php
$dsn = "mysql:dbname=$app[db_name];host=$app[db_host];port=$app[db_port];charset=utf8";
try {
    $dbh = @new PDO($dsn, $app['db_username'], $app['db_password'], array(
        PDO::ATTR_PERSISTENT => $app['db_pconnect'],
        PDO::ATTR_EMULATE_PREPARES => false, //注意这里
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
    ));
} catch (PDOException $e) {
    echo $e->getMessage();
    exit();
}
$sth = $dbh->query('SELECT * FROM test');
$arr = $sth->fetchAll(PDO::FETCH_ASSOC);
$sth = null;
$dbh = null;
var_dump($arr);
//输出:
array(3) {
  [0]=>
  array(6) {
    ["c1"]=>
    int(32)
    ["c2"]=>
    float(32.099998474121)
    ["c3"]=>
    float(32.099998474121)
    ["c4"]=>
    float(32.1)
    ["c5"]=>
    float(32.1)
    ["c6"]=>
    string(5) "32.10"
  }
  [1]=>
  array(6) {
    ["c1"]=>
    int(43)
    ["c2"]=>
    float(43.209999084473)
    ["c3"]=>
    float(43.209999084473)
    ["c4"]=>
    float(43.21)
    ["c5"]=>
    float(43.21)
    ["c6"]=>
    string(5) "43.21"
  }
  [2]=>
  array(6) {
    ["c1"]=>
    int(9876543)
    ["c2"]=>
    float(9876543)
    ["c3"]=>
    float(9876543)
    ["c4"]=>
    float(9876543.21)
    ["c5"]=>
    float(9876543.21)
    ["c6"]=>
    string(10) "9876543.21"
  }
}
//如果设置 PDO::ATTR_EMULATE_PREPARES => true ,则输出:
array(3) {
  [0]=>
  array(6) {
    ["c1"]=>
    string(2) "32"
    ["c2"]=>
    string(4) "32.1"
    ["c3"]=>
    string(5) "32.10"
    ["c4"]=>
    string(4) "32.1"
    ["c5"]=>
    string(5) "32.10"
    ["c6"]=>
    string(5) "32.10"
  }
  [1]=>
  array(6) {
    ["c1"]=>
    string(2) "43"
    ["c2"]=>
    string(5) "43.21"
    ["c3"]=>
    string(5) "43.21"
    ["c4"]=>
    string(5) "43.21"
    ["c5"]=>
    string(5) "43.21"
    ["c6"]=>
    string(5) "43.21"
  }
  [2]=>
  array(6) {
    ["c1"]=>
    string(7) "9876543"
    ["c2"]=>
    string(7) "9876540"
    ["c3"]=>
    string(10) "9876543.00"
    ["c4"]=>
    string(10) "9876543.21"
    ["c5"]=>
    string(10) "9876543.21"
    ["c6"]=>
    string(10) "9876543.21"
  }
}

可以看到无论PDO::ATTR_EMULATE_PREPARES设为false还是true,
decimal(10,2)的类型都是string,输出的数据是正确的.
不模拟预处理时(false),能保持数据类型,但某些类型,输出的数据跟数据库里的数据不一致,比如上面的float.
MySQLi查询返回的字段类型也都是string.
所以说返回string类型给程序是安全的,之后可以进行类型转换:

settype($foo, "array");
settype($foo, "bool");
settype($foo, "boolean");
settype($foo, "float");
settype($foo, "int");
settype($foo, "integer");
settype($foo, "null");
settype($foo, "object");
settype($foo, "string");

$foo = (array)$foo;
$foo = (b)$foo;      // from PHP 5.2.1
$foo = (binary)$foo; // from PHP 5.2.1
$foo = (bool)$foo;
$foo = (boolean)$foo;
$foo = (double)$foo;
$foo = (float)$foo;
$foo = (int)$foo;
$foo = (integer)$foo;
$foo = (object)$foo;
$foo = (real)$foo;
$foo = (string)$foo;