PostgreSQL用SQL实现俄罗斯方块

📢📢📢📣📣📣
作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验
Oracle、PostgreSQL ACE
CSDN博客专家及B站知名UP主，全网粉丝10万+
擅长主流Oracle、MySQL、PG、高斯及Greenplum备份恢复，
安装迁移，性能优化、故障应急处理

文章目录

- 1.游戏安装
- 2.游戏使用
- 3.PL/pgSQL能做什么？
- - 3.1 敏感数据自动加密
  - - 3.2 定时数据清理
  - 2.3 大数据迁移与转换
  - 2.4 与Linux交互备份表
- 总结

基于PostgreSQL实现的俄罗斯方块游戏项目Tetris-SQL，通过纯SQL代码和数据库操作重构了经典游戏逻辑，展现了SQL语言的图灵完备性和技术潜力。

1.游戏安装

1. 先安装一个PostgreSQL数据库
[postgres@pghost ~]$psql
psql (15.8)
2.安装 psycopg2
yum install sql>postgresql-devel python3-devel
yum install -y python3-psycopg2
pip3 install psycopg2
pip3 install psycopg2-binary
3.游戏代码克隆
git clone https://github.com/nuno-faria/tetris-sql
chmod +x ./tetris-sql/input.py

2.游戏使用

1.本地运行 Python
python3 input.py \
[-h] [-H HOST] [-P PORT] \
[-d DB] [-u USER] [-p PASSWORD]2.另一个终端运行以下查询
psql -U postgres -f tetris-sql/game.sql说明：
game.sql 用于实现游戏逻辑，主要涉及通用表表达式（CTE）实现游戏循环；
input.py 用于捕获键盘输入。

3.PL/pgSQL能做什么？

其实上面这个游戏PL/pgSQL（PostgreSQL的过程化语言）为核心，结合数据库表、查询语句和存储过程实现游戏逻辑，核心代码仅500余行。虽然包含少量Python脚本，但仅用于将用户输入（如键盘操作）转换为SQL命令，不参与核心逻辑处理。

PL/pgSQL的功能其实非常的强大，让我们一起来回顾下

3.1 敏感数据自动加密

通过触发器在数据写入前加密，读取时解密，例如为users表的phone字段添加加密逻辑：

CREATE TRIGGER encrypt_phone BEFORE INSERT ON users
FOR EACH ROW EXECUTE FUNCTION encrypt_data();

3.2 定时数据清理

结合pg_cron扩展，定期清理日志表：

CREATE OR REPLACE FUNCTION clean_old_logs()
RETURNS VOID AS $$
BEGINDELETE FROM logs WHERE created_at < NOW() - INTERVAL '30 days';
END;
$$ LANGUAGE plpgsql;-- 每天凌晨执行
SELECT cron.schedule('0 0 * * *', 'SELECT clean_old_logs()');

2.3 大数据迁移与转换

使用游标逐批处理千万级数据迁移：

CREATE OR REPLACE FUNCTION migrate_legacy_data()
RETURNS VOID AS $$
DECLAREbatch_size INT := 1000;c CURSOR FOR SELECT * FROM legacy_table;r RECORD;
BEGINOPEN c;LOOPFETCH c INTO r;EXIT WHEN NOT FOUND;INSERT INTO new_table VALUES (r.id, transform_data(r.raw_content));IF (count % batch_size = 0) THEN COMMIT; END IF;END LOOP;CLOSE c;
END;
$$ LANGUAGE plpgsql;

2.4 与Linux交互备份表

通过PL/pgSQL结合COPY命令实现PostgreSQL表数据备份的存储过程脚本，包含动态参数、异常处理和文件路径验证功能：

CREATE OR REPLACE PROCEDURE backup_table(table_name TEXT, file_path TEXT DEFAULT '/tmp/backup/'
) 
LANGUAGE plpgsql 
AS $$
DECLAREfull_path TEXT;backup_cmd TEXT;file_suffix TEXT := to_char(CURRENT_TIMESTAMP, 'YYYYMMDD_HH24MISS');
BEGIN-- 验证表是否存在[7](@ref)IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = $1 AND table_schema = 'public') THENRAISE EXCEPTION 'Table % does not exist', table_name;END IF;-- 生成带时间戳的文件路径full_path := file_path || table_name || '_' || file_suffix || '.csv';-- 构建COPY命令（自动处理列名）[6](@ref)backup_cmd := format('COPY (SELECT * FROM %I) TO %L WITH (FORMAT CSV, HEADER, ENCODING ''UTF8'')', table_name, full_path);-- 执行备份命令BEGINEXECUTE backup_cmd;RAISE NOTICE 'Table % backed up to %', table_name, full_path;EXCEPTION WHEN insufficient_privilege THENRAISE EXCEPTION 'Permission denied for path: %', file_path;WHEN others THENRAISE EXCEPTION 'Backup failed: %', SQLERRM;END;
END;
$$;

PL/pgSQL通过将逻辑贴近数据存储，显著降低了网络延迟和代码冗余。其与PostgreSQL生态的深度集成（如PostGIS、pg_cron），使其成为企业级数据处理的核心工具。

总结

Tetris-SQL证明编程的本质在于逻辑表达，而非语言类型。通过创意实现，SQL可扩展至游戏开发、模拟仿真等领域，成为“趣味驱动学习”的典范，推动技术知识的低门槛传播。