如何用正则表达式在MySQL中按系列、年月查询产品记录？

问题场景 #

数据库products表存储产品唯一编号，示例如下：

H-E223I22A1234
RW-E123I22B2345
E223I22A4545

编号格式规则：

• (H|RW)-：可选前缀
• E1|E2...：2字符系列（字母+数字组合）
• 23：年份后两位
• I：月份字母标识（示例中I代表9月）
• 22：日期（01-31）
• A|B：生产侧标识（固定A/B）
• 1234：4位随机数

需求：查询2023年9月E2系列的产品，尝试用正则实现但MySQL返回所有记录，原正则：

Select * from products where code REGEXP '((H|RW)\-)?(([A-Z]{1}[1-9]{1}){1})(23)(I)(0?[1-9]|[12][0-9]|3[01])[A|B](\d*)'

原正则问题分析 #

1. 未锚定首尾：正则没有用^（开头）和$（结尾），导致只要字符串包含匹配片段就会被命中，而非完全匹配
2. 字符组错误：[A|B]会匹配A、|、B三个字符，正确写法是[AB]
3. 随机数匹配过宽：(\d*)匹配任意长度数字（包括0位），应该限定为4位\d{4}
4. 未指定目标系列：原正则匹配所有字母+数字的2字符系列，没有锁定E2

正确的查询方案 #

方案1：修正后的正则查询 #

添加首尾锚定，修正字符组和随机数匹配，同时指定目标系列E2：

SELECT * FROM products 
WHERE code REGEXP '^((H|RW)-)?E223I(0?[1-9]|[12][0-9]|3[01])[AB]\\d{4}$';

注：MySQL中REGEXP里的反斜杠需要转义为\\，如果是MySQL 8.0+使用REGEXP_LIKE可以用单反斜杠：

SELECT * FROM products 
WHERE REGEXP_LIKE(code, '^((H|RW)-)?E223I(0?[1-9]|[12][0-9]|3[01])[AB]\d{4}$');

方案2：使用字符串截取函数（更高效） #

因为编号格式固定，直接截取对应位置的字符进行匹配，性能比正则更好：

SELECT * FROM products 
WHERE 
  -- 匹配系列：去掉可选前缀后，前2位是E2
  (LEFT(IF(code LIKE 'H-%' OR code LIKE 'RW-%', SUBSTRING(code, 3), code), 2) = 'E2')
  -- 匹配年份后两位：系列后的两位是23
  AND SUBSTRING(IF(code LIKE 'H-%' OR code LIKE 'RW-%', SUBSTRING(code, 3), code), 3, 2) = '23'
  -- 匹配月份标识：年份后的字符是I
  AND SUBSTRING(IF(code LIKE 'H-%' OR code LIKE 'RW-%', SUBSTRING(code, 3), code), 5, 1) = 'I';

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