zkxw/docs/sql/express_company_full_seed.sql

-- 快递公司初始化（仅国内）
-- 用法：
-- 1) 默认租户为 zkxw，如需其他租户请修改 @tenant_no
-- 2) 可重复执行：按 tenant_no + express_code + del_flag=0 去重插入

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

SET @tenant_no = 'zkxw';
SET @operator_id = 'd3000000000000000000000000000001';

START TRANSACTION;

DROP TEMPORARY TABLE IF EXISTS tmp_express_company_seed;
CREATE TEMPORARY TABLE tmp_express_company_seed (
  express_code varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  express_name varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  order_num int(11) DEFAULT NULL,
  remark varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=Memory DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO tmp_express_company_seed (express_code, express_name, order_num, remark) VALUES
('sf', '顺丰速运', 1, '国内主流快递'),
('jd', '京东快递', 2, '国内主流快递'),
('zt', '中通快递', 3, '国内主流快递'),
('yt', '圆通速递', 4, '国内主流快递'),
('sto', '申通快递', 5, '国内主流快递'),
('yunda', '韵达速递', 6, '国内主流快递'),
('deppon', '德邦快递', 7, '国内主流快递'),
('ems', 'EMS中国邮政速递物流', 8, '国家邮政体系'),
('youzheng', '中国邮政', 9, '邮政包裹/普邮'),
('jtexpress', '极兔速递', 10, '国内主流快递'),
('zjs', '宅急送', 11, '国内常见快递'),
('anneng', '安能物流', 12, '零担/大件常见'),
('suning', '苏宁物流', 13, '电商体系物流'),
('best', '百世快递', 14, '原百世快递体系'),
('tiantian', '天天快递', 15, '历史常见渠道'),
('guotong', '国通快递', 16, '历史常见渠道'),
('quanfen', '全峰快递', 17, '历史常见渠道'),
('rufengda', '如风达', 18, '历史常见渠道'),
('kuaijie', '快捷快递', 19, '历史常见渠道'),
('huitong', '汇通快运', 20, '区域快运常见'),
('yousu', '优速快递', 21, '国内常见快递'),
('suer', '速尔快递', 22, '国内常见快递'),
('ky', '跨越速运', 23, '企业件/时效件常见'),
('ymdd', '壹米滴答', 24, '零担快运常见'),
('longbang', '龙邦快递', 25, '国内常见快递');

INSERT INTO biz_express_company (
  id,
  express_code,
  express_name,
  tenant_no,
  order_num,
  remark,
  create_by,
  create_dept_id,
  create_time,
  update_by,
  update_time,
  del_flag
)
SELECT
  CONCAT(
    REPLACE(UUID(), '-', ''),
    LPAD(CONV(CRC32(t.express_code), 10, 16), 8, '0')
  ),
  t.express_code,
  t.express_name,
  @tenant_no,
  t.order_num,
  t.remark,
  @operator_id,
  '',
  NOW(),
  @operator_id,
  NOW(),
  0
FROM tmp_express_company_seed t
WHERE NOT EXISTS (
  SELECT 1
  FROM biz_express_company e
  WHERE e.tenant_no COLLATE utf8mb4_unicode_ci = @tenant_no COLLATE utf8mb4_unicode_ci
    AND e.express_code COLLATE utf8mb4_unicode_ci = t.express_code COLLATE utf8mb4_unicode_ci
    AND e.del_flag = 0
);

DROP TEMPORARY TABLE IF EXISTS tmp_express_company_seed;

COMMIT;
SET FOREIGN_KEY_CHECKS = 1;