How can make both rows unique even when interchanged in MYSQL table?

问题: id | sender | receiver ------------------------------- 1 | 1 | 12 <- case 1 2 | 12 | 1 <- case 2 3 |...

问题:

id |  sender          | receiver
-------------------------------
1  |    1             | 12           <- case 1
2  |    12            | 1            <- case 2
3  |    1             | 2
4  |    3             | 1 

I have a table with the above content, both sender and receiver are composite keys, meaning I can not have them twice in the same position but how can I make sure that sender or receiver are not repeated interchanged as marked in case 1 and 2 above.


回答1:

You can use computed columns along with a unique index:

create table mytable
(
  id int not null auto_increment,
  sender int not null,
  receiver int not null,
  key1 int as (least(sender,receiver)),
  key2 int as (greatest(sender,receiver)),
  primary key (id)
);

create unique index idx_mytable_unique on mytable(key1, key2);

Then

insert into mytable(sender, receiver) values (1,12);

  -> 1 row inserted.

Then

insert into mytable(sender, receiver) values (12,1);

  -> Duplicate entry '1-12' for key 'idx_mytable_unique'

Demo: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a21e48498ff6c10007451109f8e07166


回答2:

This should work:

select distinct
       case when sender > receiver then sender else receiver end as sender,
       case when sender > receiver then receiver else sender end as receiver
from MyTable

回答3:

use cross join

 select distinct a.*
from cte a cross join cte b
where a.sender<b.sender

回答4:

One option would be to use a before insert trigger to prevent this from happening in the first place. The other option would be to do nothing, and instead just handle this in your reporting. For example, if you wanted to find all distinct pairs of senders and receivers you could use LEAST and GREATEST:

SELECT DISTINCT
    LEAST(sender, receiver) AS sender,
    GREATEST(sender, receiver) AS receiver
FROM yourTable;

If your intention might be to retain only the first pair coming in a certain order, then you can add a timestamp column to your table and then aggregate:

SELECT
    LEAST(t1.sender, t1.receiver) AS sender,
    GREATEST(t1.sender, t1.receiver) AS receiver
FROM yourTable t1
INNER JOIN
(
    SELECT
        LEAST(sender, receiver) AS sender,
        GREATEST(sender, receiver) AS receiver,
        MIN(ts) AS min_ts
    FROM yourTable
    GROUP BY
        LEAST(sender, receiver),
        GREATEST(sender, receiver)
) t2
    ON LEAST(t1.sender, t1.receiver) = t2.sender AND
       GREATEST(t1.sender, t1.receiver) = t2.receiver AND
       t1.ts = t2.min_ts;
  • 发表于 2019-03-27 01:30
  • 阅读 ( 182 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除