代码之家 › 专栏 › 技术社区 › Austin Hyde

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

database-design mysql sql php

3

Austin Hyde · 技术社区 · 16 年前

我正在使用一个旧的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;

现在,我需要能够 UPDATE 这张表上的记录(别问为什么,我不知道)。正常情况下,我会这么做的

UPDATE table SET ... WHERE unique_column = value

但在本例中,我没有一个可供使用的独特列。

是否有解决办法,或者我只需要推动以达到一个好的标准 INT NOT NULL AUTO_INCREMENT ?

5 回复 | 直到 16 年前

1

3

Doug Currie 16 年前

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

2

Community Mohan Dere 9 年前

唯一的标识符是实现这一点的唯一可靠方法。只需添加一个 auto_increment 列并完成它。

有关详尽的信息,包括一些变通方法(尽管没有一种方法是完美的!)检查 this question ,其中op有一个没有唯一标识符的表,并且无法更改它。

更新 : 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.

3

0

Thomas 16 年前

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 应该 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.

4

0

Marcus Adams 16 年前

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:

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

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

在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.

5

0

nvogel 16 年前

先删除重复项。在表(或任何与此相关的表)中有重复的行有什么意义?

一旦删除了重复项,就可以实现密钥,并且解决了问题。