我有一个整数数组，如何在 mysql 查询(在 php 中)中使用每个整数? [英] I have an array of integers, how do I use each one in a mysql query (in php)?

问题描述

我有一个充满随机内容项 ID 的数组.我需要运行一个 mysql 查询(数组中的 id 在 WHERE 子句中)，使用数组中的每个 ID，按照它们出现在所述数组中的顺序.我该怎么做?

I have an array full of random content item ids. I need to run a mysql query (id in the array goes in the WHERE clause), using each ID that's in the array, in the order that they appear in the said array. How would I do this?

这将是一个 UPDATE 查询，针对数组中的每个 ID.

This will be an UPDATE query, for each individual ID in the array.

推荐答案

就像几乎所有我如何在 PHP 中执行 SQL"问题一样 - 您真的应该使用准备好的语句.没那么难:

As with nearly all "How do I do SQL from within PHP" questions - You really should use prepared statements. It's not that hard:

$ids  = array(2, 4, 6, 8);

// prepare an SQL statement with a single parameter placeholder
$sql  = "UPDATE MyTable SET LastUpdated = GETDATE() WHERE id = ?";
$stmt = $mysqli->prepare($sql);

// bind a different value to the placeholder with each execution
for ($i = 0; $i < count($ids); $i++)
{
    $stmt->bind_param("i", $ids[$i]);
    $stmt->execute();
    echo "Updated record ID: $id\n";
}

// done
$stmt->close();

或者，您可以这样做:

$ids    = array(2, 4, 6, 8);

// prepare an SQL statement with multiple parameter placeholders
$params = implode(",", array_fill(0, count($ids), "?"));
$sql    = "UPDATE MyTable SET LastUpdated = GETDATE() WHERE id IN ($params)";
$stmt   = $mysqli->prepare($sql);

// dynamic call of mysqli_stmt::bind_param                    hard-coded eqivalent
$types = str_repeat("i", count($ids));                        // "iiii"
$args = array_merge(array($types), $ids);                     // ["iiii", 2, 4, 6, 8]
call_user_func_array(array($stmt, 'bind_param'), ref($args)); // $stmt->bind_param("iiii", 2, 4, 6, 8)

// execute the query for all input values in one step
$stmt->execute();

// done
$stmt->close();
echo "Updated record IDs: " . implode("," $ids) ."\n";

// ----------------------------------------------------------------------------------
// helper function to turn an array of values into an array of value references
// necessary because mysqli_stmt::bind_param needs value refereces for no good reason
function ref($arr) {
    $refs = array();
    foreach ($arr as $key => $val) $refs[$key] = &$arr[$key];
    return $refs;
}

根据需要为其他字段添加更多参数占位符.

Add more parameter placeholders for other fields as you need them.

选择哪一个?

• 第一个变体迭代处理可变数量的记录，多次访问数据库.这对于 UPDATE 和 INSERT 操作最有用.

• The first variant works with a variable number of records iteratively, hitting the database multiple times. This is most useful for UPDATE and INSERT operations.

第二个变体也适用于可变数量的记录，但它只访问数据库一次.这比迭代方法效率高得多，显然你只能对所有受影响的记录做同样的事情.这对于 SELECT 和 DELETE 操作最有用，或者当您想要使用相同数据更新多个记录时.

The second variant works with a variable number of records too, but it hits the database only once. This is much more efficient than the iterative approach, obviously you can only do the same thing to all affected records. This is most useful for SELECT and DELETE operations, or when you want to UPDATE multiple records with the same data.

为什么要准备语句?

• 准备好的语句要安全得多，因为它们使 SQL 注入攻击成为不可能.这是使用准备好的语句的主要原因，即使编写它们需要更多的工作.一个明智的习惯是:始终使用准备好的语句，即使您认为它不是真的必要".忽视会咬你(或你的客户).
• 使用不同的参数值多次重用同一个准备好的语句比向数据库发送多个完整的 SQL 字符串更有效，因为数据库只需要编译一次语句，也可以重用它.
莉>
• execute() 时仅将参数值发送到数据库，因此重复使用时需要通过网络传输的数据更少.

• Prepared statements are a lot safer because they make SQL injection attacks impossible. This is the primary reason to use prepared statements, even if it is more work to write them. A sensible habit to get into is: Always use prepared statements, even if you think it's "not really necessary." Neglect will come and bite you (or your customers).
• Re-using the same prepared statement multiple times with different parameter values is more efficient than sending multiple full SQL strings to the database, because the database only needs to compile the statement once and can re-use it as well.
• Only parameter values are sent to the database on execute(), so less data needs to go over the wire when used repeatedly.

在较长的循环中，使用准备好的语句和发送普通 SQL 之间的执行时间差异将变得明显.

In longer loops the execution time difference between using a prepared statement and sending plain SQL will become noticeable.

这篇关于我有一个整数数组，如何在 mysql 查询(在 php 中)中使用每个整数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！