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

116 阅读 0 评论 77 点赞

我是靠谱客的博主迅速导师，这篇文章主要介绍pdo 查询 mysql 返回值 int 转 string 类型的问题，现在分享给大家，希望可以做个参考。

前言

复制代码

1
2
3

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

查错

复制代码

1
2
3
4
5
6
7

因为更改前没有发生这个问题，所以大致判断是 pdo 做了某些处理导致的这个问题。
其中，发现
PDO::ATTR_STRINGIFY_FETCHES（提取的时候将数值转换为字符串）
PDO::ATTR_EMULATE_PREPARES（启用或禁用预处理语句的模拟）
这两个预定义之间会有一些影响，我们来做个实验验证下

实验

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

$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)

创建测试表和插入数据:

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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"
}
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
<?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类型给程序是安全的,之后可以进行类型转换:

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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;