duangenshi9836
2010-06-25 21:08 阅读 40

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

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

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?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

5条回答 默认 最新

  • 已采纳
    duanji8615 duanji8615 2010-06-25 21:30
    UPDATE queries 
    SET ... 
    WHERE Email = value1 
      AND Query = value2 
      AND NumRecords = value3 
      AND Date = value4 
    LIMIT 1;
    
    点赞 6 评论 复制链接分享
  • doumo6356 doumo6356 2010-06-25 21:14

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

    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.

    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.

    点赞 6 评论 复制链接分享
  • duan1443 duan1443 2010-06-25 21:25

    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'
    

    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.

    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.

    点赞 评论 复制链接分享
  • duanban4769 duanban4769 2010-06-25 21:29

    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.

    点赞 评论 复制链接分享
  • dongshao4207 dongshao4207 2010-06-25 21:50

    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.

    点赞 评论 复制链接分享

相关推荐