sqlsrv_query 是否限制了一个查询中可以执行的语句数? [英] Does sqlsrv_query limit the number of statements that can be exectuted in one query?

问题描述

我正在 PHP for 循环中生成 SQL insert 语句.

I am generating a SQL insert statement within a PHP for loop.

生成的 SQL 字符串是大量的单个 SQL 语句，如下所示:

The SQL string generated is a large number of individual SQL statements like this:

INSERT INTO tbl VALUES(1,2,3);
INSERT INTO tbl VALUES(4,5,6);
INSERT INTO tbl VALUES(7,8,9);

等等...

然后我执行:

$InsertResult = sqlsrv_query($conn, $InsertSQL);

问题是只执行了前 312 条语句，而不是完整的 2082 行(只有 312 行插入到表中).

The problem is that only the first 312 statements get executed instead of the full 2082 lines (only 312 rows are inserted into the table).

当我将 $InsertSQL 变量输出到 JavaScript 控制台，然后在 SSMS 中手动执行它时，它可以完美运行并插入所有 2082 行.只有当我通过 sqlsrv_query 运行 $InsertSQL 变量时，它才不会完成.

When I output the $InsertSQL variable to the JavaScript console and then execute it manually in SSMS it works perfectly and inserts all 2082 lines. Only when I run the $InsertSQL variable through sqlsrv_query does it not go to completion.

我也没有收到任何错误，并且查询结果在这一行中进行了测试:

I also don't get any errors and the query result comes back true as tested in this line:

if(!$InsertResult) die('Problem with Insert query: ' . $InsertSQL);

当我搜索这个问题的解决方案时，我看到(虽然在 PHP 手册站点中没有提到)sqlsrv_query 显然对 $SQL 变量(大约 65k 个字符).

When I searched for a solution to this problem I saw that (although it's not mentioned in the PHP manual site) sqlsrv_query apparently has a string character limit on the $SQL variable (around 65k characters).

在此处查看另一篇 StackOverflow 文章:sqlsrv_query 上的 sql 变量的长度限制?

See the other StackOverflow article here: length restriction for the sql variable on sqlsrv_query?

我认为这是问题所在，因此创建了一个较短版本的字符串(通过仅添加我实际想要导入的列值).然而，这个更短的版本仍然只插入前 312 行！所以现在看来​​这与最大字符串长度无关.事实上，如果是这样，我应该只有大约 250 行(在 250 条语句之后，我大约有 65k 个字符).

I figured this was the problem and so created a shorter version of the string (by only adding in the column values that I actually wanted to import). This much shorter version however, still only Inserts the first 312 lines! So now it seems this is NOT related to the max string length. In fact, if it was, I should only get around 250 lines (after 250 statements I'm at about 65k characters).

我也可以单独执行每个 insert 语句，但这当然需要更长的时间.在我的测试中，这样做需要 90 秒左右，而在 SMSS 中手动运行组合语句只需要大约 40 秒.

I can also execute each insert statement individually but of course this takes much longer. In my testing, it takes 90s or so to do it this way where as running the combined statement manually in SMSS takes only around 40s.

请注意，我还研究了 SQL Server 的批量插入，但是我无法将文件发送到安装了 SQL Server 的计算机(SQL Server 和 Web 服务器位于不同的计算机上).根据我的理解，这消除了这种可能性.

Note that I've also looked into SQL Server's Bulk Insert however I won't be able to send the file to the machine where SQL Server is installed (SQL Server and Web servers are on separate computers). From my understanding this eliminates this possibility.

非常感谢任何帮助，因为我什至无法弄清楚是什么限制了我，更不用说修复它了，我不想一次执行一行.

Any help is much appreciated as I can't even figure out what it is that is limiting me, never mind fix it and I'd hate to have to execute one line at a time.

推荐答案

说明:

此驱动程序有一个已知的问题，发布在 GitHub 上，关于执行大型 SQL 语句.提供的解决方案的一部分是以下解释:

There is a known issue with this driver, posted on GitHub, about executing large SQL statements. One part of the provided solution are the following explanations:

似乎在执行大量 SQL 语句时，Microsoft SQL Server 可能会在批处理中的所有语句执行完毕之前停止处理该批处理.处理批处理结果时，SQL Server 用批处理创建的结果集填充连接的输出缓冲区.这些结果集必须由客户端应用程序处理.如果您正在执行具有多个结果集的大批处理，SQL Server 会填充该输出缓冲区，直到达到内部限制并且无法继续处理更多结果集.此时，控制权返回给客户端.此行为是设计使然.客户端应用程序应刷新所有挂起的结果集.一旦客户端使用了所有挂起的结果集，SQL Server 就会完成批处理的执行.客户端应用程序可以调用 sqlsrv_next_result() 直到它返回 NULL.

所以，我认为 SQL 语句长度没有限制，只有 PHP 字符串变量的大小(在您的情况下为 $InsertSQL)被限制为允许的最大 PHP内存限制.这种意外行为的实际原因是这样一个事实，即使用 SET NOCOUNT OFF(默认情况下)和大量单个 INSERT 语句，SQL Server 返回作为结果集的受影响行的计数(例如 (1 行受影响)).

So, I don't think that there is a limit for the SQL statement length, only the size of a PHP string variable ($InsertSQL in your case) is limited to the maximum allowed PHP memory limit. The actual reason for this unexpected behaviour is the fact, that with SET NOCOUNT OFF (this is by default) and a large number of single INSERT statements, the SQL Server returns the count of the affected rows as a result set (e.g. (1 row affected)).

解决方案:

我可以重现这个问题(使用 SQL Server 2012、PHP 7.1.12 和 PHP Driver for SQL Server 4.3.0+9904)，您有以下选择来解决这个问题:

I'm able to reprodiuce this issue (using SQL Server 2012, PHP 7.1.12 and PHP Driver for SQL Server 4.3.0+9904) and you have the following options to solve this problem:

• 使用 sqlsrv_next_result() 刷新待处理的结果集.
• 执行 SET NOCOUNT ON 作为复杂 T-SQL 语句的第一行，以停止 SQL Server 将受影响行的计数作为结果集返回.
• 使用sqlsrv_prepare()\sqlsrv_execute()
使用参数化语句

• Flush the pending result sets using sqlsrv_next_result().
• Execute SET NOCOUNT ON as first line in your complex T-SQL statement to stop SQL Server to return the count of the affected rows as a resultset.
• Use parameterized statement using sqlsrv_prepare()\sqlsrv_execute()

表格:

CREATE TABLE MyTable (
    Column1 int,
    Column2 int,
    Column3 int
)

一个复杂的语句(使用sqlsrv_query()和sqlsrv_next_result()):

One complex statement (using sqlsrv_query() and sqlsrv_next_result()):

<?php 

// Connection info
$server = 'server\instance';
$database = 'database';
$username = 'username';
$password = 'password';
$cinfo = array(
    "Database" => $database,
    "UID" => $username,
    "PWD" => $password
);

// Statement with sqlsrv_query
$sql = "";
for ($i = 1; $i <= 1000; $i++) {
    $sql .= "INSERT INTO MyTable (Column1, Column2, Column3) VALUES (".$i.", 0, 0);";
}
$stmt = sqlsrv_query($con, $sql);
if ($stmt === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

// Clean the buffer
while (sqlsrv_next_result($stmt) != null){};

// End
sqlsrv_free_stmt($stmt);
sqlsrv_close($con);
echo "OK";
?>

一个复杂的语句(使用sqlsrv_query()和SET NOCOUNT ON):

One complex statement (using sqlsrv_query() and SET NOCOUNT ON):

<?php 

// Connection info
$server = 'server\instance';
$database = 'database';
$username = 'username';
$password = 'password';
$cinfo = array(
    "Database" => $database,
    "UID" => $username,
    "PWD" => $password
);

// Connection
$con = sqlsrv_connect($server, $cinfo);
if ($con === false) {
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

// Statement with sqlsrv_query
$sql = "SET NOCOUNT ON;";
for ($i = 1; $i <= 1000; $i++) {
    $sql .= "INSERT INTO MyTable (Column1, Column2, Column3) VALUES (".$i.", 0, 0);";
}
$stmt = sqlsrv_query($con, $sql);
if ($stmt === false) {
    echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
    exit;
}

// End
sqlsrv_free_stmt($stmt);
sqlsrv_close($con);
echo "OK";
?>

参数化语句(使用sqlsrv_prepare()和sqlsrv_execute()):

Parameterized statement (using sqlsrv_prepare() and sqlsrv_execute()):

<?php 

// Connection info
$server = 'server\instance';
$database = 'database';
$username = 'username';
$password = 'password';
$cinfo = array(
    "Database" => $database,
    "UID" => $username,
    "PWD" => $password
);

// Connection
$con = sqlsrv_connect($server, $cinfo);
if ($con === false) {
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

$sql = "INSERT INTO MyTable (Column1, Column2, Column3) VALUES (?, ?, ?);";
$value1 = 0;  
$value2 = 0;  
$value3 = 0;  
$params = array(&$value1, &$value2, &$value3);
$stmt = sqlsrv_prepare($con, $sql, $params);
if ($stmt === false ) {
    echo "Error (sqlsrv_prepare): ".print_r(sqlsrv_errors(), true);
    exit;
}
for ($i = 1; $i <= 1000; $i++) {
    $value1 = $i;  
    $value2 = 0;  
    $value3 = 0;  
    $result = sqlsrv_execute($stmt);
    if ($result === false) {
        echo "Error (sqlsrv_execute): ".print_r(sqlsrv_errors(), true);
        exit;
    }
}

// End
sqlsrv_free_stmt($stmt);
sqlsrv_close($con);
echo "OK";
?>

这篇关于sqlsrv_query 是否限制了一个查询中可以执行的语句数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！