postgresql中資料表如何透過一個欄位標識資料行多種狀態？

文所未闻發表於2024-10-23
SQL

通常我們在業務開發過程中，為了避免多餘欄位的建立，要儘可能的減少單表欄位個數，針對一些資料狀態可以採用一個數字融合多種資料狀態。

以下是業務開發過程中的列舉類：

@Getter
public enum TicketTagEnum {

    REMINDERS(1, "狀態1"),
    REASSIGNMENT(2, "狀態2"),
    REWORK(4, "狀態3"),
    SUSPEND(8, "狀態4"),
    TRANSFER(16, "狀態5"),
    INSPECTION(32, "狀態6"),
    ALARM(64, "狀態7");

    private final Integer code;
    private final String desc;

    TicketTagEnum(Integer code, String desc) {
        this.code = code;
        this.desc = desc;
    }

    // 將標籤數字和拆分成2次冪的數字
    public static List<Integer> splitTags(int n) {
        List<Integer> result = new ArrayList<>();
        int bit = 1;
        while (n > 0) {
            if ((n & bit) != 0) {
                result.add(bit);
            }
            n -= (n & bit);
            bit <<= 1;
        }
        return result;
    }

    // 新增標籤
    public static int addTag(int oldTag, int newTag) {
        return oldTag | newTag;
    }

    // 移除標籤，但是這種計算需要舊的標籤中一定包含了新的標籤
    public static int removeTag(int oldTag, int newTag) {
        return splitTags(oldTag).contains(newTag) ? oldTag ^ newTag : oldTag;
    }
    
    // 移除標籤 更加完善的寫法
    public static int removeTagPro(int oldTag, int newTag) {
        return oldTag & ~newTag;
    }
}

新增資料狀態時：TicketTagEnum.addTag(oldTag, newTag);

移除資料狀態時：TicketTagEnum.removeTag(oldTag, newTag);

SQL查詢資料行是否包含某種狀態時：select * from table where 4 = any(split_into_powers_of_two_arr(tags))

具體SQL函式的實現如下：

CREATE OR REPLACE FUNCTION "public"."split_into_powers_of_two_arr"("n" int8)
  RETURNS "pg_catalog"."_int8" AS $BODY$
DECLARE
    current_value BIGINT := n;
    current_power BIGINT := 1;
    powers_arr BIGINT[] := '{}';
BEGIN
    WHILE current_value > 0 LOOP
        IF (current_value & 1) = 1 THEN
            powers_arr := array_append(powers_arr, current_power);
        END IF;

        current_value := current_value >> 1;
        current_power := current_power << 1;
    END LOOP;

    RETURN powers_arr;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

相關文章