关系MySql表的UPDATE语句的基础规则:for vs while [英] Basics rules on UPDATE statement for relational MySql table: for vs while

问题描述

我正在使用mysql和php做我的第一步，所以我对正确的代码优化的基本规则有疑问.

I'm doing my first steps with mysql and php, so I have doubts on foundamental rules for a right code optimization.

我有一种情况，我的UPDATE语句需要在一定数量的行上执行，因为它应该在关系表上执行，所以cicle正确吗?

I have a case where my UPDATE statement need to be executed on a certain number of rows, because it should be executed on a relational table, so is a for cicle correct?

<?
// connection already created
$data[] = array ("id" => 54, "enabled" => 1);
$data[] = array ("id" => 33, "enabled" => 0);
$data[] = array ("id" => 12, "enabled" => 0);
$data[] = array ("id" => 58, "enabled" => 0);
$data[] = array ("id" => 21, "enabled" => 1);
$data[] = array ("id" => 10, "enabled" => 1);
$data[] = array ("id" => 18, "enabled" => 0);
$data[] = array ("id" => 32, "enabled" => 1);
$data[] = array ("id" => 84, "enabled" => 0);
$data[] = array ("id" => 80, "enabled" => 1);

for (var $i = 0; $i < count ($data); $i ++) {

    $id = $data[$i]["id"];
    $enabled = $data[$i]["enabled"];

    $sql = "UPDATE users SET user_enabled = '$enabled' WHERE user_id = '$id' LIMIT 1;";
    $res = mysql_query ($sql);
    $num = mysql_num_rows ($res);

}


?>

我应该使用while还是for循环? 此代码是否可用于多个UPDATE或是否存在更好的功能，例如针对此类操作的特定查询?

Should I use a while or a for loop? Is this code valid for multiple UPDATEs or does exist something better like specific queries for this kind of action?

推荐答案

几个评论:

• 在循环的每次迭代中计算count($data)都是不必要的成本.在count($data)处开始循环并倒数 ，或者按照@Ignacio的建议，使用foreach.

• Calculating the count($data) on every iteration of the loop is a needless cost. Either start the loop at count($data) and count down, or else as @Ignacio suggests, use foreach.

这是一个很好的示例，其中使用带参数的预准备查询将是一个好处.但是，PHP的纯mysql扩展不支持此功能.您将必须使用mysqli或最好使用 PDO .

This is a great example where it would be a benefit to use a prepared query with parameters. But this feature is not supported in PHP's plain mysql extension. You would have to use mysqli or preferably PDO.

您不必在SQL表达式中的整数值两边加上引号.

You don't have to put quotes around integer values in an SQL expression.

mysql_num_rows()对于UPDATE查询没有意义.您可能打算使用mysql_affected_rows().

mysql_num_rows() is meaningless for an UPDATE query. You probably meant to use mysql_affected_rows().

我不知道为什么在此查询中使用LIMIT，因为我猜user_id是users表的主键，因此您永远不会更新多于一行无论如何.

I don't know why you use LIMIT in this query, since I would guess that user_id is the primary key of the users table, and therefore you would never update more than one row anyway.

这是我编写该代码的方式:

Here is how I would write that code:

<?php
// PDO connection already created
$data[] = array ("id" => 54, "enabled" => 1);
...etc...

$sql = "UPDATE users SET user_enabled = :enabled WHERE user_id = :id";
$stmt = $pdo->prepare($sql);

foreach ($data as $parameters) {

    $success = $stmt->execute($parameters);
    $num = $stmt->rowCount();

}


?>

这篇关于关系MySql表的UPDATE语句的基础规则:for vs while的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！