mysql中更新查询中的多集和where子句 [英] Multiple set and where clauses in Update query in mysql

问题描述

我认为这是不可能的，因为我找不到任何东西，但是我认为如果我没有搜索正确的东西，我会在这里进行检查.

I don't think this is possible as I couldn't find anything but I thought I would check on here in case I am not searching for the correct thing.

我的数据库中有一个设置表，该表有两列.第一列是设置名称，第二列是值.

I have a settings table in my database which has two columns. The first column is the setting name and the second column is the value.

我需要同时更新所有这些内容.我想看看是否有一种方法可以同时更新一个查询，如下所示

I need to update all of these at the same time. I wanted to see if there was a way to update these values at the same time one query like the following

UPDATE table SET col1='setting name' WHERE col2='1 value' AND SET col1='another name' WHERE col2='another value';

我知道上述方法不是正确的SQL格式，但这是我想做的事情，我想知道是否还有另一种方法可以完成此操作，而不必为每个方法执行单独的SQL查询设置我要更新.

I know the above isn't a correct SQL format but this is the sort of thing that I would like to do so was wondering if there was another way that this can be done instead of having to perform separate SQL queries for each setting I want to update.

感谢您的帮助.

推荐答案

我决定一次性使用多个查询.所以代码会像这样

I decided to use multiple queries all in one go. so the code would go like

UPDATE table SET col2='value1' WHERE col1='setting1';
UPDATE table SET col2='value2' WHERE col1='setting1';

等 等等

我刚刚进行了一项测试，向数据库中插入了1500条记录.在不启动数据库事务的情况下执行此操作，该过程花费了35秒，清空数据库并再次执行，但是首先启动了一个事务，然后一旦插入第1500条记录完成了事务，并且花费了1秒钟的时间，因此看起来确实很像在数据库事务中是要走的路.

I've just done a test where I insert 1500 records into the database. Do it without starting a DB transaction and it took 35 seconds, blanked the database and did it again but starting a transaction first, then once the 1500th record inserted finish the transaction and the time it took was 1 second, so definetely seems like doing it in a db transaction is the way to go.

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