Mysql INSERT IGNORE(如果特别是两列中的行值已存在) [英] Mysql INSERT IGNORE if in particular row values in two columns already exists

问题描述

CurrencyAbbreviation | CurrencyRate | DateOfCurrencyRate

---

  AUD                |  1.1         |   2013-01-01 
  USD                |  1.1         |   2013-01-01 
  EUR                |  1.1         |   2013-01-01 

想避免在相同的日期插入相同的货币.

Want to prevent insert the same currency with the same date.

想要防止这种情况

CurrencyAbbreviation | CurrencyRate | DateOfCurrencyRate

---

  AUD                |  1.1         |   2013-01-01 
  AUD                |  1.1         |   2013-01-01 

如果将CurrencyAbbreviation或DateOfCurrencyRate设置为唯一，则例如无法插入AUD | 2013-01-02或USD | 2013-01-03

If set unique either CurrencyAbbreviation or DateOfCurrencyRate then for example could not insert either AUD | 2013-01-02 or USD | 2013-01-03

尝试了REPLACE进行检查，但是相同的结果并假定REPLACE不适用于这种情况.

Tried REPLACE to check, but the same result and suppose REPLACE is not good for the situation.

现在看到一些为每种货币创建单独表格的解决方案，但是效果不好，因为有大约40多种货币，并且INSERT SELECT多个表格浪费了资源

Now see some solution to create separate table for each currency, but not good, because there are ~ 40+ currencies and INSERT `SELECT`` multiple tables waste of resources

请提出解决方案.

考虑一下，似乎需要创建包含货币和日期的其他列并将该列设置为唯一.目前看来，这是我所知道的最佳解决方案

Thinking, seems need to create additional column that contains currency and date and set the column as unique. Seems at the moment it is the best solution I know

推荐答案

创建跨越这两列的UNIQUE索引

Create a UNIQUE index spanning these two columns

CREATE 
    UNIQUE INDEX currency_date 
    ON yourTableName (CurrencyAbbreviation, DateOfCurrencyRate)  

这篇关于Mysql INSERT IGNORE(如果特别是两列中的行值已存在)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！