MySQL:更新没有唯一字段保证的行

I am working with an old MySQL table, which serves as a log of sorts. It looks like

我正在使用一个旧的MySQL表，它作为各种日志。它看起来像

CREATE TABLE `queries` (
  `Email` char(32) NOT NULL DEFAULT '',
  `Query` blob,
  `NumRecords` int(5) unsigned DEFAULT NULL,
  `Date` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Now, I need to be able to UPDATE the records in this table (don't ask why, I don't know). Normally, I would just do

现在，我需要能够更新这个表中的记录(不要问为什么，我不知道)。通常情况下，我会这么做

UPDATE table SET ... WHERE unique_column = value

But in this case, I don't have a unique column to work from.

但是在这种情况下，我没有一个唯一的列可以使用。

Is there a workaround for this, or am I just going to have to push to put in a nice, standard INT NOT NULL AUTO_INCREMENT?

是否有办法解决这个问题，或者我需要推入一个漂亮的，标准的INT类型而不是NULL AUTO_INCREMENT?

5 个解决方案

#1

UPDATE queries 
SET ... 
WHERE Email = value1 
  AND Query = value2 
  AND NumRecords = value3 
  AND Date = value4 
LIMIT 1;

#2

A unique identifier is the only reliable way of doing this. Just add an auto_increment column and be done with it.

惟一标识符是惟一可靠的实现方法。只要添加一个auto_increment列，就可以使用它。

For exhaustive info including some workaround approaches (none of them perfect though!) check this question, where the OP had a table without a unique identifier and no way to change it.

对于详细的信息，包括一些变通的方法(没有一个是完美的!)检查这个问题，OP有一个没有唯一标识符的表，并且没有办法更改它。

Update: As Doug Currie points out, this is not entirely true: A unique ID is not necessary as such here. I still strongly recommend the practice of always using one. If two users decide to update two different rows that are exact duplicates of each other at the exact same time (e.g. by selecting a row in a GUI), there could be collisions because it's not possible to define which row is targeted by which operation. It's a microscopic possibility and in the case at hand probably totally negligeable, but it's not good design.

更新:正如Doug Currie指出的，这并不是完全正确的:在这里不需要一个唯一的ID。我仍然强烈建议始终使用一个。如果两个用户决定同时更新两个完全相同的行(例如通过在GUI中选择一行)，那么可能会发生冲突，因为不可能定义哪个行针对哪个操作。这是一种极小的可能性，在目前的情况下，可能完全可以否定，但这不是好的设计。

#3

There are two different issues here. First, is de-duping the table. That is an entirely different question and solution which might involve adding a auto_increment column. However, if you are not going to de-dup the table, then by definition, two rows with the same data represent the same instance of information and both ought to be updated if they match the filtering criteria. So, either add a unique key, de-dup the table (in which case uniqueness is based on the combination of all columns) or update all matching rows.

这里有两个不同的问题。首先，是拆桌子。这是一个完全不同的问题和解决方案，可能涉及添加auto_increment列。但是，如果您不打算对表进行反dup，那么根据定义，具有相同数据的两行表示相同的信息实例，如果它们匹配过滤条件，则应该对它们进行更新。因此，要么添加一个唯一的键，删除表(在这种情况下，惟一性是基于所有列的组合)，或者更新所有匹配的行。

#4

In case you didn't know this, it will affect performance, but you don't need to use a primary key in your WHERE clause when updating a record. You can single out a row by specifying the existing values:

如果您不知道这一点，它将影响性能，但是在更新记录时不需要在WHERE子句中使用主键。您可以通过指定现有的值来挑选一行:

UPDATE queries
SET Query = 'whatever'
WHERE Email = 'whatever@whatever.com' AND
  Query = 'whatever' AND
  NumRecords = 42 AND
  Date = '1969-01-01'

If there are duplicate rows, why not update them all, since you can't differentiate anyway?

如果有重复的行，为什么不全部更新它们，因为无论如何您都无法区分它们?

You just can't do it with a GUI interface in MySQL Query Browser.

用MySQL查询浏览器的GUI界面是做不到的。

If you need to start differentiating the rows, then add an autoincrement integer field, and you'll be able to edit them in MySQL Query Browser too.

如果您需要开始区分行，那么添加一个自动递增的整数字段，您也可以在MySQL查询浏览器中编辑它们。

#5

Delete the duplicates first. What's the point of having duplicate rows in the table (or any table for that matter)?

首先删除重复。在表中有重复的行(或任何表)有什么意义?

Once you've deleted the duplicates you can implement the key and they your problem is solved.

一旦您删除了副本，您就可以实现密钥并解决问题。

#1

UPDATE queries 
SET ... 
WHERE Email = value1 
  AND Query = value2 
  AND NumRecords = value3 
  AND Date = value4 
LIMIT 1;

#2