PHP MySQL PDO:如何保留zerofill int列的前导零 [英] PHP MySQL PDO: how to preserve leading zeros of zerofill int columns
问题描述
在从旧的mysql_*()函数迁移到新的PDO类的过程中，我遇到了另一个障碍:
我有一张下表:
I've hit one more bump in the road of migrating from the old mysql_*() functions to the new PDO class:
I have a the following table:
CREATE TABLE `test` (
`Id` tinyint(4) unsigned zerofill NOT NULL,
`UserName` varchar(4) NOT NULL,
`TestDecimal` decimal(6,0) unsigned zerofill DEFAULT NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
请注意填零的Id和TestDecimal字段.
如果我运行以下代码，则使用旧的mysql_*()函数:
If I run the following code, using the old mysql_*() functions:
$SqlQuery = "SELECT * FROM test";
$Sql_Result = mysql_query($SqlQuery);
var_dump(mysql_fetch_array($Sql_Result));
我得到以下输出，其中正确填零的Id列:
I get the following output, with the correctly zerofilled Id column:
array (size=6)
0 => string '0001' (length=4)
'Id' => string '0001' (length=4)
1 => string 'alex' (length=4)
'UserName' => string 'alex' (length=4)
2 => string '000002' (length=6)
'TestDecimal' => string '000002' (length=6)
但是，如果我使用PDO进行相同操作，就像这样:
However, if I do the same using PDO, like so:
$SqlQuery = "SELECT * FROM test";
$SqlResult = $MysqlPDO->prepare($SqlQuery);
$SqlResult->execute();
var_dump($SqlResult->fetch(PDO::FETCH_BOTH));
我得到的输出是不正确填充零的Id列:
I get this output, with the incorrectly non-zerofilled Id column:
array (size=6)
'Id' => int 1
0 => int 1
'UserName' => string 'alex' (length=4)
1 => string 'alex' (length=4)
'TestDecimal' => string '000002' (length=6)
2 => string '000002' (length=6)
似乎PDO类正在查看列类型并在PHP中返回匹配的变量类型(在这种情况下为整数).
经过一番搜索，我发现了PDO::ATTR_STRINGIFY_FETCHES属性，可以将其设置为强制所有MYSQL结果以字符串形式返回，尽管这似乎可行(我得到的是字符串而不是int)，但它仍然没有返回前导零:
It seems like the PDO class is looking at the column type and returning a matching variable type (integer in this case) in PHP.
After some searching I found out about the PDO::ATTR_STRINGIFY_FETCHES attribute which can be set to force all MYSQL results to be return as strings, while this seems to work (I get a string instead of an int), it still doesn't return the leading zeros:
array (size=6)
'Id' => string '1' (length=1)
0 => string '1' (length=1)
'UserName' => string 'alex' (length=4)
1 => string 'alex' (length=4)
'TestDecimal' => string '000002' (length=6)
2 => string '000002' (length=6)
似乎可以在decimal(6,0) zerofill字段中正常工作，但不能在tinyint(4) zerofill字段中正常工作...
有什么方法可以使这项工作奏效，还是我必须遍历我的代码库并找出此更改有哪些突破(我已经确定了一些不再起作用的东西...)?
It seems to work correctly with the decimal(6,0) zerofill field, but not with the tinyint(4) zerofill field...
Is there any way to make this work, or will I have to go over my codebase and find out what breaks with this change (I already identified a couple of things which don't work anymore...)?
演示代码.
推荐答案
您可以使用LPAD吗?
尝试:SELECT *, LPAD( Id, 3, '0') AS zero_Fill_Id FROM test
应根据int大小更改3:在这种情况下可能为4?
should change 3 according to int size: maybe 4 for this situation?
更新:
我不认为将int更改为十进制是一种好习惯，为什么我不对此做更深入的介绍，您可以搜索该主题.
I don't think change int to decimal to be good practice, why I'll not go deeper at this, you can search on that subject.
我认为您使用的是mysqlnd驱动程序，关于它我发现了什么(检查是否已启用
I think you use mysqlnd driver, what I've found about it (check if enabled How to know if MySQLnd is the active driver?):
将mysqlnd用于PDO的优势
mysqlnd在以下情况下返回本机数据类型 使用服务器端预备语句， 例如返回一个INT列 作为整数变量而不是 细绳.这意味着更少的数据 内部转化.
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.
在这种情况下，应将PDO::ATTR_STRINGIFY_FETCHES设置为true，您还可以尝试尝试PDO::ATTR_EMULATE_PREPARES属性，请参见:
In this case there is PDO::ATTR_STRINGIFY_FETCHES which in your case should be set to true, also you can give try to PDO::ATTR_EMULATE_PREPARES attribute farther see: PDO MySQL: Use PDO::ATTR_EMULATE_PREPARES or not?
...
$pdo->setAttribute(PDO::ATTR_STRINGIFY_FETCHES, true);
希望这在任何情况下都可以帮助您:))
Hope this helps in any case or anyone :))
这篇关于PHP MySQL PDO:如何保留zerofill int列的前导零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
