尝试增加日期字段时，MySQL会重复输入错误？ [英] MySQL giving duplicate Entry error when trying to increment date field?

问题描述

我正在从XML文件读取数据。由于源代码的错误是有一天的，所以在加载到数据库后，我使用这个SQL语句来增加日期。

  UPDATE 2011_electricity SET DATE = DATE_ADD（DATE，INTERVAL 1 DAY）
  

上周工作正常，但是现在我收到一个错误：

  MySQL表示：
 
＃1062  -  2011年重复条目对于键1 
  

我在数据字段上有一个主键。这是数据库的外观：

  date energy daynum 
 2011-06-29 0.05 4197 
 2011 -07-19 0.20 4219 
 2011-07-20 17.07 4220 
 2011-07-21 11.56 4221 
 2011-07-22 18.18 4222 
 2011-07-23 24.92 4223 
 2011-07-24 10.56 4224 
 2011-07-25 12.68 4225 
 2011-07-26 10.06 4226 
 2011-07-27 19.72 4227 
 2011- 07-28 19.02 4228 
 2011-07-29 17.92 4229 
 2011-07-30 14.49 4230 
 2011-07-31 10.84 4231 
 2011-08-01 13.38 4232 
 2011-08-02 14.86 4233 
  

我看不到任何重复，所以不明白错误，有没有更好的方法来执行mysql代码来增加一天1？

解决方案

这是一个问题如何MySQL的 UPDATE 工作，行按照p.cambell解释。绕过这个问题的另一种方法是明确告诉引擎如何订购更新（另一个MySQL怪癖）：

 更新2011_电
 SET DATE = DATE_ADD（DATE，INTERVAL 1 DAY）
 ORDER BY DATE DESC 
  

<经验法则：如果要增加PK（或其他唯一密钥），请按顺序排列。如果要减少PK，请按升序排序。

I am reading in data from an XML file. Due to an error at the source it is one day out, so after loading into the database I use this SQL statement to increment the date.

UPDATE 2011_electricity SET DATE = DATE_ADD( DATE, INTERVAL 1 DAY )

Last week it worked fine, however now I get an error:

MySQL said: 

#1062 - Duplicate entry '2011-07-20' for key 1 

I have one primary key on the data field. This is how the database looks:

                date        energy  daynum
        2011-06-29  0.05    4197
        2011-07-19  0.20    4219
        2011-07-20  17.07   4220
        2011-07-21  11.56   4221
        2011-07-22  18.18   4222
        2011-07-23  24.92   4223
        2011-07-24  10.56   4224
        2011-07-25  12.68   4225
        2011-07-26  10.06   4226
        2011-07-27  19.72   4227
        2011-07-28  19.02   4228
        2011-07-29  17.92   4229
        2011-07-30  14.49   4230
        2011-07-31  10.84   4231
        2011-08-01  13.38   4232
        2011-08-02  14.86   4233

I cannot see any duplicate there, so do not understand the error, is there a better way to carry out mysql code to increment the day by 1?

解决方案

This is a problem on how MySQL's UPDATE works, row by row as p.cambell explained. Another way to bypass this issue, is to explicitely tell the engine how to order the updates (another MySQL quirk):

UPDATE 2011_electricity 
SET DATE = DATE_ADD( DATE, INTERVAL 1 DAY )
ORDER BY DATE DESC 

Rule of thumb: If you want to increase the PKs (or other Unique Key), order by descending. If you want to decrease the PKs, order by ascending.

这篇关于尝试增加日期字段时，MySQL会重复输入错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！