NN2025112501/database/migrations/add_receiver_mobile_and_num_to_orders.sql

-- 为订单表添加收货人手机号和商品数量字段
-- 执行时间：2025-12-27

-- 添加收货人手机号字段
ALTER TABLE `lev_orders` 
ADD COLUMN `receiver_mobile` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT '' COMMENT '收货人手机号' 
AFTER `receiver_name`;

-- 添加商品数量字段
ALTER TABLE `lev_orders` 
ADD COLUMN `num` int(10) NOT NULL DEFAULT '0' COMMENT '商品数量' 
AFTER `store_id`;

-- 添加索引以提高查询性能
ALTER TABLE `lev_orders` 
ADD INDEX `idx_receiver_mobile` (`receiver_mobile`);

-- 更新现有数据（可选）
-- 如果需要从订单商品表中统计数量，可以执行以下语句：
-- UPDATE lev_orders o 
-- SET num = (
--     SELECT COALESCE(SUM(og.num), 0) 
--     FROM lev_orders_goods og 
--     WHERE og.order_no = o.order_no AND og.mark = 1
-- ) 
-- WHERE o.mark = 1;

-- 验证更新结果
SELECT 
    COUNT(*) as total_orders,
    COUNT(CASE WHEN receiver_mobile != '' THEN 1 END) as orders_with_mobile,
    AVG(num) as avg_goods_count
FROM lev_orders 
WHERE mark = 1;