zkxw/docs/sql/migrations/2026-10-08-sample-freight-no-fix-format.sql

-- 若已按旧规则 YF+YYYY-MM-DD+序号 回填，纠正为 YF+YYYYMMDD+001～999

SET @grp := '';
SET @seq := 0;

UPDATE biz_sample_freight f
INNER JOIN (
  SELECT
    id,
    CONCAT(
      'YF',
      DATE_FORMAT(create_time, '%Y%m%d'),
      LPAD(
        @seq := IF(
          @grp = CONCAT(tenant_no, '|', DATE_FORMAT(create_time, '%Y%m%d')),
          @seq + 1,
          1
        ),
        3,
        '0'
      )
    ) AS new_no,
    @grp := CONCAT(tenant_no, '|', DATE_FORMAT(create_time, '%Y%m%d')) AS _grp
  FROM biz_sample_freight
  ORDER BY tenant_no ASC, create_time ASC, id ASC
) t ON f.id = t.id
SET f.freight_no = t.new_no;

ALTER TABLE biz_sample_freight
  MODIFY COLUMN freight_no VARCHAR(32) NOT NULL DEFAULT '' COMMENT '运费批次号 YF+YYYYMMDD+序号';