PHP sqlsrv驱动和PDO驱动的数据类型区别 [英] Data type differences between PHP sqlsrv driver and PDO driver

问题描述

这里我用的是sqlsrv:

$conn = sqlsrv_connect("192.168.1.102,1433", array("Database"=>"RF_User", "UID"=>"rfo-gcp", "PWD" => ""));

$tsql = "SELECT birthdate FROM tbl_rfaccount WHERE id = ?";

$stmt = sqlsrv_query($conn, $tsql, array("test"));

$result = sqlsrv_fetch_array($stmt);

var_dump($result);

结果:array(2) { [0]=>对象(日期时间)#1 (3) { [日期"]=>字符串(26)2020-04-19 20:40:00.000000"[timezone_type"]=>int(3) ["时区"]=>字符串(3)UTC"}[生日"]=>对象(日期时间)#1 (3) { [日期"]=>字符串(26)2020-04-19 20:40:00.000000"[timezone_type"]=>int(3) ["时区"]=>字符串(3) "UTC" } }

这里我使用的是PDO:

$conn = new PDO("sqlsrv:Server=192.168.1.102,1433; Database=RF_User;", "rfo-gcp", "");

$tsql = "SELECT birthdate FROM tbl_rfaccount WHERE id = cast(? as varchar(13))";     

$stmt = $conn->prepare($tsql);

$stmt->execute(array("test"));

$result = $stmt->fetch(PDO::FETCH_ASSOC);

var_dump($result);

结果:array(1) { ["birthdate"]=>字符串(19) "2020-04-19 20:40:00" }

如果您注意到，我不得不在 PDO 代码上使用 cast(? as varchar(13)).没有它就不会返回任何行.在 sqlsrv 上，我不必使用 CAST() 函数.为什么是这样?另外，数据库上的id列是BINARY(13)，那我为什么要强制转换id 到 varchar 而不是 binary (使用二进制转换它也找不到行)?

If you notice, I had to use cast(? as varchar(13)) on the PDO code. Without it would not return any row. On the sqlsrv I didn't have to use the CAST() function. Why is this? Also, the id column on the database is a BINARY(13), so why do I have to cast the id to varchar and not to binary (with binary cast it also doesn't find the row)?

推荐答案

为什么返回的日期和时间值不同?

其实这只是一个设置.

当您使用 PDO_SQLSRV 时(如 在文档中提到)，日期和时间类型(smalldatetime、datetime、date、time、 datetime2 和 datetimeoffset) 默认以字符串形式返回.PDO::ATTR_STRINGIFY_FETCHES 和 PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE 属性都不起作用.为了将日期和时间类型检索为 PHP DateTime 对象，请将连接或语句属性 PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE 设置为 true(默认为 false).

When you use PDO_SQLSRV (as is mentioned in the documentation), date and time types (smalldatetime, datetime, date, time, datetime2, and datetimeoffset) are by default returned as strings. Neither the PDO::ATTR_STRINGIFY_FETCHES nor the PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE attribute has any effect. In order to retrieve date and time types as PHP DateTime objects, set the connection or statement attribute PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE to true (it is false by default).

当您使用 SQLSRV 驱动程序时(同样来自 documentation), smalldatetime, datetime, date、time、datetime2 和 datetimeoffset 类型将作为 PHP DateTime 对象返回.可以通过在连接字符串或语句级别设置 'ReturnDatesAsStrings' 选项来更改此行为.

When you use SQLSRV driver (again from the documentation), smalldatetime, datetime, date, time, datetime2, and datetimeoffset types will be returned as PHP DateTime objects. This behaviour can be changed by setting the 'ReturnDatesAsStrings' option in the connection string or at the statement level.

$conn = sqlsrv_connect(
   "192.168.1.102,1433", 
    array(
       "ReturnDatesAsStrings"=>true,
       "Database"=>"RF_User", 
       "UID"=>"rfo-gcp", 
       "PWD" => ""
   )
);

请注意，某些功能取决于 PHP Driver for SQL Server 的版本.

Note that some of the features depend on the version of PHP Driver for SQL Server.

如何转换参数值?

在语句中使用 CAST() 和 CONVERT() 函数并将参数值与字符串值绑定应该可以工作.当然，您可以在绑定参数时指定参数数据类型.

Using CAST() and CONVERT() functions in the statement and binding parameter value with string value should work. Of course, you can specify the parameter data type, when you bind a parameter.

对于 PDO_SQLSRV，您应该扩展 PDOStatement::bindParam().

For PDO_SQLSRV you should extended syntax for PDOStatement::bindParam().

对于 SQLSRV，您可以使用扩展的 $params 语法 指定 SQL Server数据类型，当您调用 sqlsrv_query()sqlsrv_execute() 时.

For SQLSRV you may use the extended $params syntax to specify the SQL Server data type, when you make a call to sqlsrv_query()sqlsrv_execute().

我能够重现此问题(PHP 7.1.12，SQL Server 4.3.0+9904 的 PHP 驱动程序，SQL Server 2012)，解决方案是使用:

I'm able to reproduce this issue (PHP 7.1.12, PHP Driver for SQL Server 4.3.0+9904, SQL Server 2012) and the solution is to use:

$params = array($id, SQLSRV_PARAM_IN, null, SQLSRV_SQLTYPE_BINARY);          // SQLSRV
$stmt->bindParam(1, $id, PDO::PARAM_LOB, null, PDO::SQLSRV_ENCODING_BINARY); // PDO_SQLSRV 

表:

CREATE TABLE tbl_rfaccount (id binary(13), birthdate datetime)
INSERT INTO tbl_rfaccount (id, birthdate) VALUES (CONVERT(binary(13), 'Test'), GETDATE())

PHP:

<?php
...

// 
$id = "Test";

// SQLSRV
$tsql = "SELECT birthdate FROM tbl_rfaccount WHERE id = ?";
$params = array($id, SQLSRV_PARAM_IN, null, SQLSRV_SQLTYPE_BINARY);
$stmt = sqlsrv_query($conn, $tsql, $params);
if ($stmt === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}
$result = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC);
var_dump($result);

// PDO_SQLSRV
$tsql = "SELECT birthdate FROM tbl_rfaccount WHERE id = ?";     
$stmt = $conn->prepare($tsql);
$stmt->bindParam(1, $id, PDO::PARAM_LOB, null, PDO::SQLSRV_ENCODING_BINARY);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
var_dump($result);

...
?> 

这篇关于PHP sqlsrv驱动和PDO驱动的数据类型区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！