MySQL如何为不同格式的客户条码设置对应自动解析规则？

完全可以在MySQL中通过规则匹配先识别条码归属的客户类型，再执行对应解析逻辑，核心思路是先提取不同客户条码的独有特征作为判定依据，再对应解析字段，以下是两种可落地的实现方案：

方案1：硬编码CASE逻辑（适合客户数量少、规则固定的场景） #

你举的两个客户条码都是空格分隔的三段结构，且中段特征差异明显：客户1中段是「数字+英文」的颜色格式，客户2中段是纯大写英文的供应商格式，直接用正则匹配中段特征判定客户类型即可，示例SQL如下：

SELECT *,
  -- 判定客户类型
  CASE 
    WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(CODE, ' ', 2), ' ', -1) REGEXP '^[0-9]+[a-zA-Z]+$' THEN '客户1'
    WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(CODE, ' ', 2), ' ', -1) REGEXP '^[A-Z]+$' THEN '客户2'
    ELSE '未知客户'
  END AS customer_type,
  -- 解析各段对应字段
  CASE 
    WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(CODE, ' ', 2), ' ', -1) REGEXP '^[0-9]+[a-zA-Z]+$' THEN SUBSTRING_INDEX(CODE, ' ', 1)
    ELSE NULL
  END AS invoice_no,
  CASE 
    WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(CODE, ' ', 2), ' ', -1) REGEXP '^[A-Z]+$' THEN SUBSTRING_INDEX(CODE, ' ', 1)
    ELSE NULL
  END AS tray_no,
  CASE 
    WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(CODE, ' ', 2), ' ', -1) REGEXP '^[0-9]+[a-zA-Z]+$' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(CODE, ' ', 2), ' ', -1)
    ELSE NULL
  END AS color,
  CASE 
    WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(CODE, ' ', 2), ' ', -1) REGEXP '^[A-Z]+$' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(CODE, ' ', 2), ' ', -1)
    ELSE NULL
  END AS supplier_name,
  -- 两个客户的尺码都在最后一段，逻辑通用
  SUBSTRING_INDEX(CODE, ' ', -1) AS SIZE
FROM barcode;

后续新增客户只要补充对应的WHEN分支、调整正则匹配规则即可。

方案2：维护条码规则配置表（适合客户多、规则频繁变动的场景，易扩展） #

单独建一张规则配置表存储所有客户的条码匹配规则和字段含义，后续新增客户不需要改SQL，只需要新增规则记录即可，步骤如下：

1. 建规则表

CREATE TABLE barcode_rule (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(50) NOT NULL COMMENT '客户名称',
  match_regex VARCHAR(200) NOT NULL COMMENT '匹配该客户条码的正则',
  first_segment_desc VARCHAR(50) NOT NULL COMMENT '首段字段含义',
  middle_segment_desc VARCHAR(50) NOT NULL COMMENT '中段字段含义',
  last_segment_desc VARCHAR(50) NOT NULL COMMENT '末段字段含义',
  sort INT DEFAULT 10 COMMENT '匹配优先级，数字越小越优先'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 插入现有客户的规则

INSERT INTO barcode_rule (customer_name, match_regex, first_segment_desc, middle_segment_desc, last_segment_desc, sort) VALUES
('客户1', '^[0-9]+ [0-9]+[a-zA-Z]+ [0-9]+$', '发票号', '颜色', '尺码', 1),
('客户2', '^[0-9]+ [A-Z]+ [0-9]+$', '托盘号', '供应商名称', '尺码', 2);

3. 关联查询自动解析

SELECT 
  b.*,
  r.customer_name,
  CASE r.first_segment_desc WHEN '发票号' THEN SUBSTRING_INDEX(b.code, ' ', 1) END AS invoice_no,
  CASE r.first_segment_desc WHEN '托盘号' THEN SUBSTRING_INDEX(b.code, ' ', 1) END AS tray_no,
  CASE r.middle_segment_desc WHEN '颜色' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(b.code, ' ', 2), ' ', -1) END AS color,
  CASE r.middle_segment_desc WHEN '供应商名称' THEN SUBSTRING_INDEX(SUBSTRING_INDEX(b.code, ' ', 2), ' ', -1) END AS supplier_name,
  SUBSTRING_INDEX(b.code, ' ', -1) AS size
FROM barcode b
LEFT JOIN barcode_rule r ON b.code REGEXP r.match_regex
ORDER BY r.sort;

补充优化建议 #

• 条码如果存在连续多个空格，解析前可以先把连续空格替换为单个空格，避免SUBSTRING_INDEX取错段，比如用REPLACE(REPLACE(REPLACE(code, ' ', ' '), ' ', ' '), ' ', ' ')预处理，或者自定义函数处理连续空格更简洁
• 如果后续条码格式更复杂（比如段数不固定），可以用JSON类型存储解析后的自定义字段，不需要单独加列适配不同客户的字段需求

内容的提问来源于stack exchange，提问作者Ali Im