wwweww
30 lines
1.2 KiB
SQL
30 lines
1.2 KiB
SQL
CREATE TABLE games (
|
|
id BIGINT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
icon TEXT NOT NULL,
|
|
category VARCHAR(50) NOT NULL,
|
|
sort_order INT DEFAULT 0,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
-- 基础索引
|
|
CREATE INDEX idx_games_category ON games(category) WHERE is_active = TRUE;
|
|
CREATE INDEX idx_games_sort ON games(sort_order, id) WHERE is_active = TRUE;
|
|
|
|
-- 三元组索引用于游戏名称模糊搜索
|
|
CREATE INDEX idx_games_name_trgm ON games USING gin(name gin_trgm_ops) WHERE is_active = TRUE;
|
|
|
|
-- 复合索引优化分类+排序查询
|
|
CREATE INDEX idx_games_category_sort ON games(category, sort_order, id) WHERE is_active = TRUE;
|
|
|
|
-- 全文搜索索引
|
|
CREATE INDEX idx_games_fulltext ON games USING gin(
|
|
to_tsvector('simple', name || ' ' || category)
|
|
) WHERE is_active = TRUE;
|
|
|
|
CREATE TRIGGER trigger_games_updated_at
|
|
BEFORE UPDATE ON games
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column(); |