如何为SQLite扑克Flop表设置忽略卡牌顺序的主键及无序查询方法

推荐使用生成列+联合主键的方案实现约束，SQLite 3.31.0及以上版本均支持该特性，修改后的建表语句如下：

CREATE TABLE Flop 
(
     id SMALLINT UNIQUE NOT NULL, 
     card1 TINYINT NOT NULL,
     card2 TINYINT NOT NULL,
     card3 TINYINT NOT NULL,
     -- 自动计算排序后的最小、中间、最大卡牌ID
     sorted_min TINYINT GENERATED ALWAYS AS (MIN(card1, card2, card3)) STORED,
     sorted_mid TINYINT GENERATED ALWAYS AS (card1 + card2 + card3 - MIN(card1, card2, card3) - MAX(card1, card2, card3)) STORED,
     sorted_max TINYINT GENERATED ALWAYS AS (MAX(card1, card2, card3)) STORED,
     -- 联合主键绑定排序后的三个字段，自动拦截顺序不同但卡牌相同的重复插入
     CONSTRAINT Flop_pk PRIMARY KEY (sorted_min, sorted_mid, sorted_max)
);

如果使用的SQLite版本低于3.31.0，不支持生成列，可以用触发器实现同等效果，无需修改原表结构：

-- 插入前自动把三个卡牌字段调整为升序排列
CREATE TRIGGER sort_flop_cards BEFORE INSERT ON Flop
BEGIN
    SELECT 
        MIN(NEW.card1, NEW.card2, NEW.card3) INTO NEW.card1,
        NEW.card1 + NEW.card2 + NEW.card3 - MIN(NEW.card1, NEW.card2, NEW.card3) - MAX(NEW.card1, NEW.card2, NEW.card3) INTO NEW.card2,
        MAX(NEW.card1, NEW.card2, NEW.card3) INTO NEW.card3;
END;

触发器方案保留原表的(card1, card2, card3)联合主键即可，同样可以拦截顺序不同的重复数据。

配合上面的约束方案，查询无需枚举6种排列组合：

1. 如果使用生成列方案：在C#侧先把输入的三个卡牌ID做升序排序，直接匹配排序字段即可，查询可以命中主键索引，效率极高：

// C#侧预处理输入参数
int[] inputCards = new int[] { 3, 1, 2 };
Array.Sort(inputCards);
// 生成的查询语句
string querySql = $"SELECT id FROM Flop WHERE sorted_min = {inputCards[0]} AND sorted_mid = {inputCards[1]} AND sorted_max = {inputCards[2]}";

2. 如果使用触发器方案：因为存储的card1/card2/card3本身就是升序排列的，同样先在C#侧排序输入参数，直接匹配三个原字段即可：

SELECT id FROM Flop WHERE card1 = 1 AND card2 = 2 AND card3 = 3;

如果不想修改表结构，临时查询也可以直接用排序逻辑匹配，无需枚举排列：

SELECT id FROM Flop 
WHERE 
    MIN(card1, card2, card3) = 1 
    AND (card1 + card2 + card3 - MIN(card1, card2, card3) - MAX(card1, card2, card3)) = 2
    AND MAX(card1, card2, card3) = 3;

该写法无法命中原主键索引，仅适合小数据量场景使用。

内容的提问来源于stack exchange，提问作者Steve W