更新包含特定值的表中的所有字段。

I have a MySQL table with 80 fields and I want to update all fields that contain a specified value. For example, I want to update the fields with item1 to the value 0:

我有一个包含80个字段的MySQL表，我想更新所有包含指定值的字段。例如，我想将item1的字段更新为值0:

id_num    field1       field2   field3   field4   field5
  1        item1       item2    item3    item4     item5
  2        item3       item3    item3    item4     item5
  3        item2       item1    item3    item4     item5
  4        item5       item4    item3    item2     item1
  5        item1       item1    item3    item4     item5
  6        item2       item2    item4    item4     item5

The result should be:

结果应该是:

  id_num    field1     field2   field3   field4   field5
      1        0       item2    item3    item4     item5
      2        item3   item3    item3    item4     item5
      3        item2   0        item3    item4     item5
      4        item5   item4    item3    item2     0
      5        0       0        item3    item4     item5
      6        item2   item2    item4    item4     item5

I want to do it with a MySQL query. The problem I have is that with 80 fields, I would have to use 80 queries like:

我想用MySQL查询。我的问题是，对于80个字段，我必须使用80个查询，比如:

update set field1=0 where field1=item1;
update set field2=0 where field2=item1;

And so on, which would be exhausting! :(

等等，这会让人筋疲力尽!:(

3 个解决方案

#1

In an UPDATE statement, the WHERE clause determines which rows are updated. Here, you also need to determine which column to update; so the only use of the WHERE clause would be to identify and filter out any rows where no columns at all need to change, i.e.:

在UPDATE语句中，WHERE子句确定更新哪些行。在这里，您还需要确定要更新哪个列;所以WHERE子句的唯一用途是识别和过滤任何根本不需要改变列的行，例如:

UPDATE table
SET
    ...
WHERE field1 = "item1"
    OR field2 = "item1"
    OR field3 = "item1"
    ...;

But judging by the number of columns and what you're trying to do, there's no way you've got a covering index for such a query. And because they're all OR conditions, you're not likely to be able to use even a single very selective index to prevent a full table scan. What that means is that the WHERE clause is probably entirely useless for you here, and your UPDATE is going to be slow on any reasonably large table.

但是从列的数量和你要做的事情来判断，你不可能为这样的查询建立一个覆盖索引。因为它们都是或都是条件，你不太可能使用一个非常有选择性的索引来阻止完整的表扫描。这意味着WHERE子句对您来说可能完全无用，而且您的更新在任何合理的大表上都将是缓慢的。

So you can't filter rows, you can only choose which columns to SET; but in any given row, you might have to set any column. So with SQL, you actually have to SET every column; but apply a condition so that you only change the value when you want to. Use a CASE expression for this:

你不能过滤行，只能选择要设置的列;但是在任何给定的行中，你可能需要设置任何列。使用SQL，你需要设置每一列;但是应用一个条件，以便您只在需要时更改值。使用一个CASE表达式:

UPDATE table
SET
    field1 = CASE field1 WHEN "item1" THEN "0" ELSE field1 END,
    field2 = CASE field2 WHEN "item1" THEN "0" ELSE field2 END,
    field3 = CASE field3 WHEN "item1" THEN "0" ELSE field3 END,
    ...;

I'm using "item1" and "0" here just for consistency, since you haven't told us what the datatypes of these columns are. Use your own values, obviously.

我在这里使用“item1”和“0”只是为了保持一致性，因为您还没有告诉我们这些列的数据类型是什么。显然，要用你自己的价值观。

Seeing your problem though, the question has to be asked: Why do you have this same "item1" in every column? You should have different sorts of data in each column, not repetitions of the same sort of data over and over again. There appear to be serious design issues with the table you're describing; you should seriously look into a redesign. The fact that you can't filter out any rows with your query is a big warning sign.

但是，看到您的问题，必须要问:为什么在每个列中都有相同的“item1”?您应该在每个列中都有不同的数据类型，而不是反复重复相同的数据类型。您所描述的表似乎存在严重的设计问题;你应该认真考虑重新设计。不能用查询过滤任何行是一个很大的警告信号。

#2

In many statements, you can do it like this.

在很多情况下，你可以这样做。

UPDATE table 
SET   field1 = '0' 
WHERE field1 = 'item1';

UPDATE table 
SET   field2 = '0' 
WHERE field2 = 'item1';

...

UPDATE table 
SET   fieldN = '0' 
WHERE fieldN = 'item1';

In one statement, you can do it like this.

在一个表述中，你可以这样做。

UPDATE table 
SET   field1 = CASE WHEN field1 = 'item1'
                 THEN '0' 
                 ELSE field1 
          END
, field2 = CASE WHEN field2 = 'item1'
                 THEN '0' 
                 ELSE field2
          END
...

, fieldN = CASE WHEN fieldN = 'item1'
                 THEN '0' 
                 ELSE fieldN 
          END
;

However, I think this is another wrong question, and no literal answer will do good.

然而，我认为这是另一个错误的问题，没有一个字面上的答案是好的。

Id you need such a loop, your database design is probably wrong.

你需要这样一个循环，你的数据库设计可能是错误的。

A column has to contain distinct value, not shared value among other columns.

列必须包含不同的值，而不是其他列之间的共享值。

Update: (a php solution) This PHP script will go through each column name and apply the query to it.

更新:(php解决方案)这个php脚本将遍历每个列名并将查询应用到它。

<?php
$result = mysql_query("SHOW COLUMNS FROM table ");
while ($row = mysql_fetch_assoc($result)) {
    mysql_query("UPDATE table SET ".$row['field']." = '0' WHERE ".$row['field']." = 'item1'");
}
?>

#3

If you have access to a web server with php, you can do this:

如果您可以使用php访问web服务器，您可以这样做:

<?php

$connection = new mysqli( host, user, password, database );
// Skipping error checking
for ($i = 0; i < num_of_columns; i++) {
  $connection->query( "UPDATE table SET field".i."=0 WHERE field".i."='item1'" );
}
?>

You should also consider database normalization, the act of making data like this manageable.

您还应该考虑数据库规范化，这是一种使数据易于管理的行为。

#1