测试工程师必须掌握的SQL技巧

by 测试王同学in 自动化/工具on Posted on
测试王同学

各位测试小伙伴们，今天想和大家聊聊一个”老生常谈”但又特别重要的话题——SQL。

可能有同学会想：我是一个功能测试工程师，又不写代码，每天点点点就够了，学什么SQL啊？

别急，让我先给你讲个故事

一个真实的”事故”

去年我们团队有个小伙伴，负责一个订单模块的验收测试。测试环境上线后，一切看起来很正常，直到有一天——

运营突然发现：有1000多个订单的状态显示”已支付”，但实际上用户根本没付款！

查了一整天，最后发现是开发在改代码的时候，不小心把支付状态字段的默认值从”未支付”改成了”已支付”。

如果这位测试小伙伴会SQL，只要在提测前跑一条查询：

SELECT status, COUNT(*) FROM orders GROUP BY status;

就能立刻发现数据分布异常，及时拦截这个问题。

所以啊，SQL不是程序员的专利，而是测试工程师的”透视眼”。它能帮我们：

  • 快速了解数据库结构
  • 验证数据的正确性
  • 构造各种测试数据
  • 定位问题根因

今天，我就把自己日常工作中最常用的5个SQL技巧整理出来，全部是实战干货，建议收藏！

一、information_schema：摸清数据库”家底”

拿到一个新项目，面对几十上百张表，完全不知道从哪下手？

这时候就要用到information_schema这个”数据库地图”了。它存储了所有表的元数据，相当于数据库的”户口本”。

场景：接手新项目，想快速了解表结构

想看看某个数据库里有哪些表？表里有哪些字段？字段类型是什么？

-- 查看所有表
SELECT table_name, table_comment 
FROM information_schema.tables 
WHERE table_schema = 'your_database_name';
-- 查看某个表的所有字段
SELECT column_name, data_type, column_comment, column_key
FROM information_schema.columns 
WHERE table_schema = 'your_database_name' 
AND table_name = 'orders'
ORDER BY ordinal_position;

💡 小技巧：如果你用的是MySQL Workbench或Navicat，可以直接右键表→”逆向工程到模型”，自动生成ER图，一目了然！

延伸思考

除了查表结构，information_schema还能帮我们：

  • 查外键关系：了解表与表之间的关联
  • 查索引：了解查询性能优化
  • 查字符集：避免中文乱码问题

这对我们理解业务逻辑、编写测试用例非常有帮助。

二、聚合函数：数据一致性的”照妖镜”

测试过程中，你是否遇到过这样的困惑：功能明明没问题，但总感觉数据”怪怪的”？

这时候，聚合函数就是最好的验证工具。

场景：验证订单数据一致性

比如要验证一个电商订单系统的数据一致性，我们可以：

-- 1. 检查订单状态分布
SELECT status, COUNT(*) as count 
FROM orders 
GROUP BY status;
-- 2. 检查订单金额是否异常（负数或为0）
SELECT * FROM orders 
WHERE total_amount <= 0 OR total_amount > 1000000;
-- 3. 检查订单时间逻辑（创建时间晚于支付时间？）
SELECT order_id, created_at, paid_at 
FROM orders 
WHERE paid_at < created_at;
-- 4. 检查关联数据一致性（订单有明细，金额要对得上）
SELECT o.order_id, o.total_amount, 
       SUM(oi.price * oi.quantity) as detail_total
FROM orders o
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount != detail_total
LIMIT 10;

⚠️ 重点提示：第4条SQL非常重要！我在工作中遇到过多次”订单金额和明细对不上”的问题，靠的就是这条查询发现的。

延伸思考

常用的聚合函数组合：

  • COUNT + GROUP BY：统计各类数据分布
  • SUM + HAVING：找出异常汇总值
  • AVG + WHERE：检测极端值
  • MAX/MIN：检查时间戳、ID等边界值

学会这几招，数据异常基本无处遁形。

三、INSERT/UPDATE：造数据的”魔法棒”

测试过程中，最头疼的是什么？没有测试数据！

等开发造数据？太慢。
自己手动填？太累。

学会SQL造数据，让你5分钟搞定原来半天的活儿。

场景：构造各种”极端”测试数据

-- 1. 复制现有数据（快速造一批类似数据）
INSERT INTO orders (user_id, total_amount, status, created_at)
SELECT user_id, total_amount, 'pending', DATE_ADD(created_at, INTERVAL 1 DAY)
FROM orders WHERE status = 'paid' LIMIT 100;
-- 2. 批量更新状态
UPDATE orders SET status = 'cancelled' 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 1 YEAR);
-- 3. 随机造一批测试用户
INSERT INTO users (username, email, phone, created_at)
SELECT 
    CONCAT('test_user_', n) as username,
    CONCAT('test_user_', n, '@test.com') as email,
    CONCAT('138', LPAD(n, 8, '0')) as phone,
    NOW() as created_at
FROM (SELECT @row := @row + 1 as n FROM 
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t1,
      (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) t2,
      (SELECT @row:=0) t3 LIMIT 1000) numbers;

🔧 实战技巧：第3条SQL看起来复杂，其实就是利用MySQL的变量机制生成1-1000的序号，然后拼接成测试数据。核心是SELECT配合CONCAT，可以造出任何你想要的数据格式。

延伸思考

造数据的进阶用法：

  • 跨表复制：从A表复制到B表（结构相似时）
  • 时间偏移：用DATE_ADD函数批量修改时间
  • 状态机测试：手动把状态改成异常值，测试系统容错能力

⚠️ 提醒：生产环境切勿随意执行UPDATE和DELETE！建议先用SELECT确认影响范围，或者开启事务先ROLLBACK验证。

四、数据比对：找出差异的”神器”

测试过程中，你有没有遇到过这种场景：

“开发说这个功能没问题啊，为什么测试环境显示不对？”
“这个数据明明应该是A，为什么显示是B？”

这时候，我们需要数据比对来”对账”。

场景：比对前后台数据差异

-- 1. 找出订单在A表有但B表没有的
SELECT order_id FROM orders_A
WHERE order_id NOT IN (SELECT order_id FROM orders_B);
-- 2. 找出同一订单在两个表的数据差异
SELECT o1.order_id, o1.status as status_A, o2.status as status_B
FROM orders_A o1
JOIN orders_B o2 ON o1.order_id = o2.order_id
WHERE o1.status != o2.status;
-- 3. 统计差异数量
SELECT 
    'A表有B表无' as type, COUNT(*) as cnt FROM orders_A
WHERE order_id NOT IN (SELECT order_id FROM orders_B)
UNION ALL
SELECT 'B表有A表无' as type, COUNT(*) as cnt FROM orders_B
WHERE order_id NOT IN (SELECT order_id FROM orders_A);

延伸思考

数据比对的其他应用场景：

  • 接口返回vs数据库：验证API返回数据是否和数据库一致
  • 同步前后对比：数据同步后验证一致性
  • 版本升级对比：升级前后数据是否丢失

有个小工具推荐：Beyond Compare，可以可视化比对两个表的数据，非常适合复杂场景。

五、高效查询：处理大量数据的”快车道”

有时候，你需要查几十万甚至上百万条数据。

如果直接SELECT * FROM orders，会发生什么？

轻则卡死，重则数据库崩溃。

所以，处理大数据量必须有”优雅”的姿势。

场景：分页查询和导出大量数据

-- 1. 分页查询（OFFSET方式，简单但有性能问题）
SELECT * FROM orders 
ORDER BY created_at DESC 
LIMIT 100 OFFSET 10000;
-- 2. 基于ID的分页（性能更好，推荐！）
SELECT * FROM orders 
WHERE id > 10000  -- 上次查询的 最大ID
ORDER BY id 
LIMIT 100;
-- 3. 分批导出到文件（MySQL命令行）
SELECT * FROM orders 
INTO OUTFILE '/tmp/orders_export.csv'
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';
-- 4. 大数据量统计（用EXPLAIN分析）
EXPLAIN SELECT status, COUNT(*) FROM orders GROUP BY status;

🚀 性能优化TIP：第2种分页方式叫”游标分页”或”ID分页”，比OFFSET性能好10倍以上！因为OFFSET越大，MySQL需要扫描跳过的行数越多。

延伸思考

处理大数据量的其他建议：

  • 避免SELECT \*：只查需要的字段
  • 利用索引：WHERE条件尽量走索引
  • 分批处理：用LIMIT分批，避免一次性加载
  • 导出工具：Navicat、DataGrip都有批量导出功能，比命令行更友好

写在最后

好啦，今天的SQL技巧分享就到这里。

回顾一下我们聊到的5个技能：

    1. information_schema：快速了解数据库结构
    2. 聚合函数：验证数据一致性
    3. INSERT/UPDATE：快速构造测试数据
    4. 数据比对：找出差异，对账必备
    5. 高效查询：分页和导出大数据

    这些技巧不需要你背下来，而是要在实践中理解思路、举一反三。

    我的建议是：建一个自己的”SQL小本本”，把工作中常用的查询保存下来，下次遇到类似场景直接改改就能用。

    记住一句话：SQL不是魔法，但它能让测试工作更有”底气”。

    当你学会用SQL验证数据、用SQL构造场景、用SQL定位问题，你会发现——测试的世界，比你想象的要宽广得多。

    Leave a Comment

    您的邮箱地址不会被公开。 必填项已用 * 标注