問(wèn)題描述

這里我使用的是 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);

結(jié)果:array(2) { [0]=>object(DateTime)#1 (3) { ["date"]=>string(26) "2020-04-19 20:40:00.000000" ["timezone_type"]=>int(3) [時(shí)區(qū)"]=>string(3) "UTC" } ["birthdate"]=>object(DateTime)#1 (3) { ["date"]=>string(26) "2020-04-19 20:40:00.000000" ["timezone_type"]=>int(3) [時(shí)區(qū)"]=>string(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);

結(jié)果:array(1) { ["birthdate"]=>string(19) "2020-04-19 20:40:00" }

如果您注意到，我必須在 PDO 代碼上使用 cast(? as varchar(13)).沒(méi)有它不會(huì)返回任何行.在 sqlsrv 上，我不必使用 CAST() 函數(shù).為什么是這樣?另外，數(shù)據(jù)庫(kù)上的id列是一個(gè)BINARY(13)，那么為什么我要強(qiáng)制轉(zhuǎn)換id 到 varchar 而不是 binary(使用二進(jìn)制轉(zhuǎn)換它也找不到行)?

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

推薦答案

為什么日期和時(shí)間值的返回方式不同?

其實(shí)這只是一個(gè)設(shè)定.

當(dāng)您使用 PDO_SQLSRV(正如 在文檔中提到)，日期和時(shí)間類型(smalldatetime、datetime、date、time、datetime2 和 datetimeoffset)默認(rèn)以字符串形式返回.PDO::ATTR_STRINGIFY_FETCHES 和 PDO::SQLSRV_ATTR_FETCHES_NUMERIC_TYPE 屬性都沒(méi)有任何影響.為了將日期和時(shí)間類型檢索為 PHP DateTime 對(duì)象，請(qǐng)將連接或語(yǔ)句屬性 PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE 設(shè)置為 true(默認(rèn)為 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).

當(dāng)您使用 SQLSRV 驅(qū)動(dòng)程序時(shí)(同樣來(lái)自 文檔)、smalldatetime、datetime、date、time、datetime2 和 datetimeoffset 類型將作為 PHP DateTime 對(duì)象返回.可以通過(guò)在連接字符串或語(yǔ)句級(jí)別設(shè)置 'ReturnDatesAsStrings' 選項(xiàng)來(lái)更改此行為.

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" => ""
   )
);

請(qǐng)注意，某些功能取決于 SQL Server 的 PHP 驅(qū)動(dòng)程序版本.

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

如何轉(zhuǎn)換參數(shù)值?

在語(yǔ)句中使用 CAST() 和 CONVERT() 函數(shù)并將參數(shù)值與字符串值綁定應(yīng)該可以工作.當(dāng)然，您可以在綁定參數(shù)時(shí)指定參數(shù)數(shù)據(jù)類型.

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.

對(duì)于 PDO_SQLSRV，您應(yīng)該為 PDOStatement::bindParam().

對(duì)于 SQLSRV，您可以使用擴(kuò)展的 $params syntax 指定 SQL Server數(shù)據(jù)類型，當(dāng)您調(diào)用 sqlsrv_query()sqlsrv_execute() 時(shí).

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().

我可以重現(xiàn)這個(gè)問(wèn)題(PHP 7.1.12、PHP Driver for SQL Server 4.3.0+9904、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);

...
?> 

這篇關(guān)于PHP sqlsrv 驅(qū)動(dòng)程序和 PDO 驅(qū)動(dòng)程序之間的數(shù)據(jù)類型差異的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！