安全只读 SQL

本页提供线上排查模板。所有参数都使用 :parameter 占位，执行前在数据库客户端绑定实际值；不要把客户姓名、手机号、地址、证件号或支付账号复制到群聊和工单。

执行规则

1. 使用生产只读账号，默认禁止 INSERT、UPDATE、DELETE、ALTER、TRUNCATE 和 DROP。
2. 查询必须带业务主键、时间范围或 LIMIT，不做无条件全表扫描。
3. 资金、支付、保险和订单排查保留查询时间、工单号和结论，不在知识库保存查询结果明细。
4. 字段在不同历史版本中可能有差异。报“未知字段”时先执行 SHOW COLUMNS，不要临时改生产表。
5. 需要修数时另建审批和补偿方案，本页 SQL 不能改成写操作直接执行。

可以在支持的 MySQL 客户端中使用只读事务：

SET SESSION TRANSACTION READ ONLY;
START TRANSACTION;

-- 在这里执行本页 SELECT。

ROLLBACK;

订单主状态与日志

SELECT
    id,
    order_number,
    store_id,
    store_uuid,
    business_mode,
    order_status,
    order_status_name,
    create_time,
    update_time
FROM `order`
WHERE order_number = :order_number
LIMIT 1;

SELECT
    id,
    order_number,
    operation_type,
    operation_desc,
    operation_time,
    create_time
FROM order_logs
WHERE order_number = :order_number
ORDER BY id DESC
LIMIT 100;

如 order_logs 的操作字段与当前环境不一致，先执行：

SHOW COLUMNS FROM order_logs;

订单服务人员

SELECT
    ow.id,
    ow.order_number,
    ow.service_id,
    ow.service_name,
    ow.service_start_time,
    ow.service_end_time,
    ow.commission_rate,
    ow.service_personal_commission,
    ow.service_personal
FROM order_waiter AS ow
WHERE ow.order_number = :order_number
ORDER BY ow.id;

order_waiter 存在历史兼容字段，员工身份以当前服务查询逻辑和 service_staff 记录共同判断。

支付、分账和退款

SELECT
    id,
    order_number,
    transaction_id,
    pay_status,
    settle_status,
    split_status,
    create_time,
    update_time
FROM order_payments
WHERE order_number = :order_number
ORDER BY id DESC
LIMIT 20;

SELECT
    id,
    order_id,
    unique_order_no,
    contract_uuid,
    status,
    amount,
    pay_amount,
    created_at
FROM store_payment_order
WHERE order_id = :order_number
ORDER BY id DESC
LIMIT 20;

不同支付表的字段名可能随版本调整。先用 SHOW COLUMNS FROM order_payments 或 SHOW COLUMNS FROM store_payment_order 核实后再查询，不为了兼容临时修改表。

标准单派发

SELECT
    id,
    order_number,
    staff_id,
    attempt_no,
    dispatch_status,
    notified_at,
    accepted_at,
    rejected_at,
    timeout_at,
    created_at,
    updated_at
FROM standard_staff_dispatch_attempt
WHERE order_number = :order_number
ORDER BY attempt_no, id;

SELECT
    id,
    order_number,
    attempt_id,
    event_type,
    event_message,
    created_at
FROM standard_staff_dispatch_log
WHERE order_number = :order_number
ORDER BY id
LIMIT 200;

如日志表使用不同的动作或原因字段，先查看字段结构，再按订单号查询全部非敏感状态字段。

标准单通知和结算

SELECT
    id,
    order_number,
    channel,
    outbox_status,
    retry_count,
    next_retry_at,
    created_at,
    updated_at
FROM standard_order_notification_outbox
WHERE order_number = :order_number
ORDER BY id;

SELECT
    id,
    order_number,
    staff_id,
    task_status,
    settlement_mode,
    settlement_amount,
    failure_reason,
    processed_at,
    created_at,
    updated_at
FROM standard_order_settlement_task
WHERE order_number = :order_number
ORDER BY id;

门店、公司和有效状态

SELECT
    s.id,
    s.store_uuid,
    s.company_id,
    s.name AS store_name,
    s.status,
    s.is_delete,
    c.name AS company_name,
    c.status AS company_status,
    c.contract_maintenance_mode
FROM store AS s
LEFT JOIN company AS c ON c.id = s.company_id
WHERE s.store_uuid = :store_uuid
LIMIT 1;

公司或门店有效期字段存在历史差异。需要排查到期时间时，先执行：

SHOW COLUMNS FROM company;
SHOW COLUMNS FROM store;

门店钱包和积分

SELECT
    store_uuid,
    available_balance,
    frozen_amount,
    status,
    updated_at
FROM store_wallet_account
WHERE store_uuid = :store_uuid
LIMIT 1;

SELECT
    id,
    store_uuid,
    transaction_type,
    amount,
    idempotency_key,
    created_at
FROM store_wallet_transaction
WHERE store_uuid = :store_uuid
  AND created_at >= :start_time
  AND created_at < :end_time
ORDER BY id DESC
LIMIT 200;

SELECT
    store_uuid,
    base_score,
    total_score,
    today_score,
    dispatch_status,
    pause_reason,
    updated_at
FROM store_score_account
WHERE store_uuid = :store_uuid
LIMIT 1;

钱包和积分字段以生产表结构为准。查询时必须分清 store_wallet_* 资金和 store_score_* 积分。工单和群聊只保留汇总与差异，不复制逐笔资金明细。

员工状态与标准单资格

SELECT
    id,
    uuid,
    company_id,
    store_id,
    status,
    is_delete,
    city_id,
    city,
    update_time
FROM service_staff
WHERE uuid = :staff_uuid
LIMIT 1;

SELECT
    id,
    staff_id,
    work_type,
    product_mode,
    approval_status,
    enabled,
    effective_from,
    effective_to,
    updated_at
FROM staff_service_capability
WHERE staff_id = :staff_id
ORDER BY id;

SELECT
    id,
    staff_id,
    region_level,
    region_code,
    region_name,
    enabled,
    updated_at
FROM standard_staff_service_region
WHERE staff_id = :staff_id
ORDER BY id;

保险和回调

SELECT
    id,
    outer_id,
    purchase_type,
    contract_id,
    staff_id,
    status,
    payment_status,
    policy_no,
    callback_status,
    created_at
FROM insurance_record
WHERE outer_id = :outer_id
LIMIT 1;

SELECT
    id,
    insurance_record_id,
    operation_type,
    operation_status,
    created_at
FROM insurance_operation_record
WHERE insurance_record_id = :insurance_record_id
ORDER BY id DESC
LIMIT 100;

保险日志中可能包含第三方响应和个人信息，默认只查状态和时间；需要查看原文时按工单授权处理。

家姐联盟线索派发

SELECT
    id,
    source_order_need_id,
    dispatch_status,
    dispatch_message,
    resolved_city_code,
    resolved_district_code,
    region_resolve_status,
    create_time,
    update_time
FROM jiejie_alliance_lead
WHERE source_order_need_id = :source_order_need_id
LIMIT 1;

SELECT
    id,
    source_order_need_id,
    store_uuid,
    attempt_no,
    dispatch_status,
    rank_scope_level,
    rank_scope_code,
    freeze_status,
    first_viewed_at,
    create_time,
    update_time
FROM store_lead_dispatch_record
WHERE source_order_need_id = :source_order_need_id
ORDER BY attempt_no, id;

线索排查必须结合积分排名、今日派发次数、轮发位置、进行中线索、余额和自动接收开关，不能仅按积分最高判断派发结果。

计划任务状态

SELECT
    job_id,
    job_name,
    invoke_target,
    cron_expression,
    misfire_policy,
    concurrent,
    status,
    update_time
FROM sys_job
WHERE job_name LIKE CONCAT('%', :job_keyword, '%')
ORDER BY job_id
LIMIT 50;

SELECT
    job_log_id,
    job_name,
    job_group,
    status,
    exception_info,
    create_time
FROM sys_job_log
WHERE job_name LIKE CONCAT('%', :job_keyword, '%')
  AND create_time >= :start_time
ORDER BY job_log_id DESC
LIMIT 100;

宝塔调用脚本的任务不一定写入 sys_job_log，还要查 /www/wwwlogs、脚本标准输出和业务任务表。