一条语句中的mysql muliple查询 [英] mysql muliple queries in one statement

问题描述

我已经在stackoverflow上寻找了一个类似的问题，但是没有找到我真正想要的东西，所以去了.在phpMyAdmin中，您可以在一个语句中包含多个查询，并为您执行该查询，例如:'

I've looked around on stackoverflow for a similar question, but haven't found exactly what I was looking for, so here goes. In phpMyAdmin you can have multiple queries in one statement and it executes it for you, eg:'

UPDATE `test` WHERE `test2` = 4;
UPDATE `test` WHERE `test4` = 8;
UPDATE `test` WHERE `test8` = 1;

现在，如果我尝试在PHP中执行类似的操作，则根本无法使用.例如:

Now if I try to do something like that in PHP, it doesn't work at all. eg:

 $test = 'UPDATE `test` SET `value` = "123" WHERE `test2` = 4;
             UPDATE `test` SET `value` = "321" WHERE `test4` = 8;
             UPDATE `test` SET `value` = "533" WHERE `test8` = 1;';
    mysql_query($test);

礼物和错误:

您的SQL语法有错误； 检查对应的手册 您的MySQL服务器版本 在';附近使用正确的语法；更新 test SET值="123"，其中test2 = 4;在第1行更新test SE'

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; UPDATE test SET value = "123" WHERE test2 = 4; UPDATE test SE' at line 1

是否甚至可以在一个语句中组合多个上述查询?我想在以下情况下执行此操作:(此操作背后的逻辑可能很糟糕，但是我没有太多的MySQL经验，所以请告诉我是否有更好的方法来做到这一点)

Is it even possible to combine, say, multiple queries like above, in one statement? I want to do this in the following situation: (The logic behind this is probably very bad, but I don't have much MySQL experience, so please let me know if there's a better way to do it)

以下循环多次:

function SaveConfig($name, $value)
{
        global $sql_save_query;
    $sql = 'SELECT * FROM `config` WHERE `name` = "'.$name.'"';
    $res = mysql_query($sql);

    if($res)
    {
        $sql_save_query .= 'UPDATE `config` SET value = "'.$value.'" WHERE `name` = "' .$name. '"; '."\n";
    }
    else
    {
            $sql_save_query .= 'INSERT INTO `config`(`id`,`name`,`value`) VALUES("","' .$name. '","' .$value. '"); '."\n";
    }
}

然后在循环完成后运行:

Then after the loop finishes it runs:

mysql_query($sql_save_query);

哪个给出错误:

您的SQL语法有错误； 检查对应的手册 您的MySQL服务器版本 在';附近使用正确的语法；更新 config SET值=" WHERE name = "fcolour2";更新config SE'，位于 第1行

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; UPDATE config SET value = "" WHERE name = "fcolour2"; UPDATE config SE' at line 1

现在，我的另一种选择(在我看来)是在每个循环之后只执行一次SQL查询，一次只执行一次查询.但这不是不好/慢/不好的做法吗?

Now my other option (in my mind) is to just execute an SQL query after each loop, one query at a time. But wouldn't that be bad/slow/bad practice?

推荐答案

php API禁止您在一次调用中发出多个查询，以减少

the php API forbids you to issue multiple queries in a single call to reduce the chance of an SQL injection attack to your code (think of what would happen if I passed '; UPDATE users SET admin=1 WHERE username='hacker' to your login script as username). You need to either execute multiple statements, or wrap the logic of your statements into a single statement (which is not possible in your case).

这篇关于一条语句中的mysql muliple查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！