1. 递归查询实战：破解课程依赖关系网
第一次接触WITH RECURSIVE语法时，我正为在线教育平台设计课程推荐系统。平台里有门《机器学习入门》课程，竟然要求先修5门不同领域的基础课，而这些基础课本身又存在复杂的先修关系。传统JOIN查询在这里完全失效，就像试图用渔网打捞整片海洋。
递归查询的奥秘在于它像侦探破案一样层层推进。我们来看具体实现：首先定位目标课程《数据库系统概论》作为起点（锚成员），然后通过UNION ALL不断关联先修课程（递归成员）。这个过程中，数据库引擎会自动维护一个临时结果集RS，直到找不到新的先修课程为止。
WITH RECURSIVE RS AS ( -- 锚成员：定位初始课程 SELECT Cpno FROM Course WHERE Cname ='数据库系统概论' UNION -- 递归成员：查找先修课程 SELECT Course.Cpno FROM Course, RS WHERE RS.Cpno = Course.Cno ) SELECT Cno, Cname FROM Course WHERE Cno IN (SELECT Cpno FROM RS);实际项目中我踩过两个坑：一是忘记写终止条件导致无限循环（虽然数据库有默认深度限制），二是递归路径中存在环形依赖。后来我增加了CYCLE子句检测环状结构：
WITH RECURSIVE RS AS ( SELECT Cpno, 1 AS depth FROM Course WHERE Cname ='数据库系统概论' UNION SELECT Course.Cpno, depth+1 FROM Course, RS WHERE RS.Cpno = Course.Cno AND depth < 10 -- 深度限制 ) CYCLE Cpno SET is_cycle USING path -- 环状检测 SELECT * FROM RS;2. 存储过程开发：成绩统计的瑞士军刀
去年给某高校开发教务系统时，院系主任拿着纸质成绩单问我："能不能一键生成带分布直方图的成绩报告？"这个需求让我意识到存储过程就是数据库里的多功能工具箱。
以统计"离散数学"成绩分布为例，存储过程的核心在于游标遍历和条件分支。这里有个性能优化技巧：直接使用CASE WHEN聚合查询比游标循环效率高5倍以上：
CREATE PROCEDURE discrete_math_grade_v2() AS BEGIN SELECT COUNT(CASE WHEN grade=100 THEN 1 END) AS p_100, COUNT(CASE WHEN grade>=90 AND grade<100 THEN 1 END) AS p_90, COUNT(CASE WHEN grade>=80 AND grade<90 THEN 1 END) AS p_80 FROM SC WHERE cno = (SELECT Cno FROM Course WHERE Cname='离散数学'); END;等级转换时，我发明了个骚操作——用数学计算代替条件判断。将分数除以10取整后，用CHR(65 + (100-grade)/10)直接得到A-E的ASCII码，比嵌套IF语句简洁多了：
CREATE PROCEDURE gradetype_v2() AS BEGIN UPDATE SC SET grade_mark = CHR(65 + CASE WHEN grade=100 THEN 0 WHEN grade>=90 THEN 1 WHEN grade>=80 THEN 2 ELSE 10 - grade/10 END); END;3. 批量数据生成：压力测试的造浪机
模拟千万级产品数据时，我发现直接INSERT就像用吸管给游泳池注水。后来琢磨出三级火箭方案：内存表→批量插入→事务分批提交。这个方案让数据生成速度从200条/秒飙升到8万条/秒。
关键技巧在于：
- 使用MEMORY引擎的临时表避免磁盘IO
- 用RAND()函数生成随机数据时，先创建辅助函数：
DELIMITER $$ CREATE FUNCTION random_product_name() RETURNS VARCHAR(30) BEGIN DECLARE brands VARCHAR(200) DEFAULT '荣耀,小米,苹果,联想,华为,戴尔'; DECLARE types VARCHAR(200) DEFAULT '手机,笔记本,平板,耳机,手表,路由器'; RETURN CONCAT( SUBSTRING_INDEX(SUBSTRING_INDEX(brands, ',', 1+FLOOR(RAND()*6)), ',', -1), SUBSTRING_INDEX(SUBSTRING_INDEX(types, ',', 1+FLOOR(RAND()*6)), ',', -1), FLOOR(1000+RAND()*9000) ); END$$ DELIMITER ;批量插入时一定要控制事务大小。我曾因单次提交10万条记录导致undo日志爆仓。最佳实践是每500-1000条提交一次：
CREATE PROCEDURE generate_products(IN total INT, IN batch_size INT) BEGIN DECLARE i INT DEFAULT 0; WHILE i < total DO START TRANSACTION; INSERT INTO Product SELECT NULL, random_product_name(), ROUND(10+RAND()*990,2), FLOOR(1+RAND()*100), DATE_ADD('2020-01-01', INTERVAL FLOOR(RAND()*1000) DAY) FROM (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) dummy LIMIT batch_size; COMMIT; SET i = i + batch_size; END WHILE; END;4. 异构数据库迁移：数据摆渡船
最近把客户系统从MySQL迁移到PostgreSQL时，我设计了个通用数据搬运工方案。核心在于JDBC的DatabaseMetaData接口，它能自动识别不同数据库的字段类型差异。比如MySQL的DATETIME到PostgreSQL的TIMESTAMP需要特殊处理：
// 获取源库元数据 ResultSetMetaData meta = resultSet.getMetaData(); int columnCount = meta.getColumnCount(); // 构建跨库插入语句 StringBuilder insertSQL = new StringBuilder("INSERT INTO ") .append(targetTable).append(" VALUES ("); for(int i=1; i<=columnCount; i++) { insertSQL.append(i>1 ? ",?" : "?"); } insertSQL.append(")"); // 类型自适应转换 PreparedStatement pstmt = targetConn.prepareStatement(insertSQL.toString()); for(int i=1; i<=columnCount; i++) { switch(meta.getColumnType(i)) { case Types.TIMESTAMP: pstmt.setTimestamp(i, resultSet.getTimestamp(i)); break; case Types.DECIMAL: pstmt.setBigDecimal(i, resultSet.getBigDecimal(i)); break; // 其他类型处理... } }大数据量迁移一定要用批处理+事务隔离。我封装了个通用迁移工具类，主要优化点包括：
- 分页查询避免内存溢出
- 错误记录重试机制
- 断点续传功能
- 多线程并行搬运
public class DataMigrator { private static final int BATCH_SIZE = 500; public void migrate(String sourceSQL, Connection sourceConn, String targetTable, Connection targetConn) { try(Statement stmt = sourceConn.createStatement( ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)) { stmt.setFetchSize(BATCH_SIZE); ResultSet rs = stmt.executeQuery(sourceSQL); ResultSetMetaData meta = rs.getMetaData(); String insertSQL = buildInsertSQL(targetTable, meta); PreparedStatement pstmt = targetConn.prepareStatement(insertSQL); int count = 0; while(rs.next()) { for(int i=1; i<=meta.getColumnCount(); i++) { pstmt.setObject(i, rs.getObject(i)); } pstmt.addBatch(); if(++count % BATCH_SIZE == 0) { pstmt.executeBatch(); targetConn.commit(); } } pstmt.executeBatch(); targetConn.commit(); } catch(SQLException e) { // 错误处理逻辑... } } }5. 实战中的避坑指南
在给银行做数据迁移时，因为一个时区设置问题导致交易记录时间全部错乱8小时。从此我养成了在数据库连接字符串显式设置时区的习惯：
// MySQL连接字符串要加上时区 String url = "jdbc:mysql://localhost:3306/db?useSSL=false&serverTimezone=Asia/Shanghai"; // PostgreSQL连接配置 String url = "jdbc:postgresql://localhost:5432/db?options=-c%20TimeZone=Asia/Shanghai";其他常见坑点包括：
- 字符集问题：MySQL的utf8其实是伪UTF-8，要用utf8mb4
- 自增ID处理：PostgreSQL的SERIAL类型在迁移时需要重置序列
- 空值差异：Oracle的空字符串视为NULL，MySQL则区分空串和NULL
- 分页语法：MySQL用LIMIT，Oracle用ROWNUM，SQL Server用TOP
事务控制也有讲究。有次迁移过程中网络闪断，导致部分数据重复插入。现在我会先查目标表最大ID，再基于此增量迁移：
-- 在目标库创建水位线表 CREATE TABLE migration_watermark ( source_table VARCHAR(100) PRIMARY KEY, last_id BIGINT, update_time TIMESTAMP ); -- 增量查询语句 SELECT * FROM source_table WHERE id > (SELECT last_id FROM migration_watermark WHERE source_table='source_table') ORDER BY id ASC LIMIT 1000;
