防止数据库中的重复值 - mysql [英] Prevent duplicate values in database - mysql

问题描述

我有一张表存储了一些事件的重复发生的值，例如，id=1 的事件发生在周一、周三和周五，而 id=2 的事件发生在 发生在星期一和星期三.所以我的表是这样的:

I have one table that store values about recurrence of some events, for example, the event with id=1 occurs in Monday, Wednesday and Friday, and the event with id=2 occurs in Monday and Wednesday. So my table is like this:

id  event_id  day
1      1      Monday
2      1      Wednesday
3      1      Friday
4      2      Monday
5      2      Wednesday

我的问题是有时(并非总是)我的脚本在我的数据库中插入重复值，所以我的表有两倍的重复事件 1 和 2，如下表所示:

My problem is that sometimes (not always) my script insert duplicate values in my database, so my table have twice the recurrence of event 1 and 2, like show the following table:

id  event_id  day
1      1      Monday
2      1      Wednesday
3      1      Friday
4      2      Monday
5      2      Wednesday
1      1      Monday
2      1      Wednesday
3      1      Friday
4      2      Monday
5      2      Wednesday

如果我在 event_id 列中添加约束 UNIQUE，则事件 1 只有星期一的值，但我希望它具有其他值，如星期三和星期五.如何添加约束来解决这个问题?

If I add a constraint UNIQUE in column event_id the event 1 only have the value Monday but I want that has the rest of values like Wednesday and Friday. How to add a constraint to solve this?

推荐答案

您想要 event_id 和 day 的唯一索引:

You want a unique index on event_id and day:

create unique index idx_recurrence_event_day on recurrence(event_id, day)

这将防止您不想要的重复.

This will prevent the duplication that you do not want.

如果您已经有重复项，有多种方法可以摆脱它们.但是，如果每一行都有一个唯一的 id 会更容易，这就是为什么自动递增的主键在所有表中都很有用的一个原因.

If you already have duplicates there are various ways you can get rid of them. However, it would be easier if you had a unique id for each row, which one reason why an auto-incremented primary key is useful in all tables.

即使没有唯一 id 也可以摆脱它们的一种方法是使用:

One way to get rid of them even without a unique id is to use:

alter ignore table recurrence add unique (event_id, day);

我个人不喜欢这种方法，因为添加索引不应该删除行，但这是一个有效的 MySQL 扩展.

Personally, I don't like this method because adding an index shouldn't delete rows, but this is a valid MySQL extension.

这篇关于防止数据库中的重复值 - mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！