mysql为int类型的字段php取出来之后为何变为string类型？

程序员文章站 2022-04-14 14:26:31

...

我是使用pdo连接的，然后使用var_dump打印出来后，发现mysql中类型为int的字段打印之后变为string类型，不知道这是怎么回事，有没有办法让php显示mysql字段的实际类型？

回复内容：

自己测试了下:
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输出:

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

当你把数据存进数据库的那个刻，所有类型都变成字符串了。

比如MySQL能存数据吗？

它必须要你先转成字符串再存，比如：转Json、序列化。

php这种类弱类型语言，有可能是你在遍历结果的时候一不小心变给自动转成了string

查看一下变量的赋值地址

入库之前要intval一下。