简介围绕MPN制造商零件编号在库存管理中的落地这份Word技术文档面向SAP物料、采购与供应链相关岗位的顾问和业务人员重点解决同一物料对应多个制造商编号时的数据对接、独立库存与替换管理问题。文档包含1个doc文件压缩包大小566KB。内容概述了库存管理型MPN应用的总体架构覆盖物料、采购、库存、MPN管理等核心模块并通过完整实例串联创建制造商XK01与供应商、建立部物料号MM01及关联物料、维护替换关系PIC01、创建采购订单ME21N、收货MIGO、库存查询MMBE到采购替换件ME22N等关键操作便于读者按步骤理解系统配置与业务流。相比仅介绍外部编码对接的方案文档更强调Inventory managed MPN的落地路径可支撑替换件库存共享与替换作业。当前已有222人学习适合希望快速掌握SAP库存管理MPN应用路径的实施顾问、物料管理和采购人员。1. 库存管理中的MPN应用为什么要把一串零件号当系统一等公民在仓库收货区最让人头疼的不是货多而是同一只料换了供应商以后MPN从ABC-123变成ABC123系统里却只有一条SKU记录。MPN是制造商零件编号在采购、质检、领料、追溯这些环节它才是供应链里最接近真实物料的身份标识。很多库存系统把MPN当作一个可填可不填的备注字段结果对账时全靠人眼匹配出错了只能翻聊天记录。这篇文章要讲的是如何把MPN从备注提升为库存管理的主数据覆盖表结构设计、数据清洗、批量修改、查询匹配和最终验证。适合正在做WMS、ERP、进销存或供应链中台的开发与运维人员尤其是被多供应商、多编码规则折腾过的人。2. MPN主数据建模把库存管理的物料标识装进表里在设计库存管理应用时第一个要做的决定不是写接口而是决定MPN存在哪。把MPN直接挂在SKU表的varchar列上确实简单但很快就会发现同一个SKU对应多个MPN或者不同厂家用了同一串数字。正确做法是把MPN抽出来作为独立的主数据表和SKU建立多对多关系。2.1 MPN与SKU、UPC的关系模型SKU库存单位是公司内部对可存储、可销售物品的编号一个SKU对应一个仓内管理粒度UPC/EAN是零售条码主要在扫码环节使用MPN则是制造商给出的零件编号。三者并不是一一对应。举个例子一家电子贸易商销售同一种10uF电容可能同时从村田和三星进货同一个SKU就需要挂两条MPNGRM188R71C104KA01D和CL10B104KA8NNNC。反过来同一颗芯片被用在两条不同产品线里公司可能建了两个SKU但MPN只有一个。因此常见的做法是建立mpn_catalog映射表把MPN作为独立实体同时保留与SKU的多对多关系而不是在SKU表里加一列mpn。这样当供应商切换、或者同一颗料被多个SKU引用时只需要维护映射表。下面是对照关系示例表作用主键说明sku内部库存单位sku_id每条代表一个可管理物料mpn_catalogMPN主数据mpn_id每条代表一个制造商零件编号sku_mpn_mapSKU与MPN映射sku_idmpn_id处理多对多关系这种三个表的结构比在SKU表上堆MPN字段更清晰也方便用外键约束保证数据完整。如果业务规模不大可以把前两个表合一只保留mpn_catalog里的sku_id外键此时一个SKU对应多行MPN记录即可但反向多对多会比较麻烦。我一般建议直接用独立映射表理由在后续查询章节就能看见。2.2 创建MPN主数据表字段长度、字符集与唯一约束下面这段SQL可以作为库存管理应用的起点。CREATE TABLE mpn_catalog ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sku_id BIGINT UNSIGNED NOT NULL COMMENT 关联SKU主键, mpn_raw VARCHAR(128) NOT NULL COMMENT 制造商给出的原始MPN, mpn_normalized VARCHAR(128) NOT NULL COMMENT 清洗后的MPN查询用, manufacturer VARCHAR(128) NOT NULL DEFAULT COMMENT 制造商名称, mpn_status TINYINT NOT NULL DEFAULT 1 COMMENT 1启用 0停用, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_mpn_norm_manu (mpn_normalized, manufacturer), KEY idx_sku (sku_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENTMPN主数据表与SKU映射;这段SQL里有几个参数值得细看。mpn_raw存原始字符串mpn_normalized存统一格式后的值唯一约束加在normalized和manufacturer上而不是raw字段上。原因是ABC-123和ABC123对人类是同一个零件但字符串不同如果对raw加唯一约束导入时会频繁报冲突。mpn_normalized会根据清洗规则把大小写、分隔符去掉比如ABC-123变成ABC123这样同一事实只占一行。字段长度128字符覆盖绝大多数半导体、连接器和机械件编号如果业务明确有更长编号可以改成256但不要用TEXT因为索引长度有限并且比较时会先做排序规则转换。字符集用utf8mb4因为一些欧洲厂家在MPN里带了°、Ω这类符号utf8mb4能存下。排序规则建议utf8mb4_general_ci配合mpn_normalized存储大小写在应用层已经统一不会影响查询。创建完表之后建议再加上外键约束。但在生产环境里我更推荐不用物理外键而是在应用层校验sku_id是否存在否则大批量导入时外键会拖慢速度。2.3 用Python从Excel导入MPN并写进库存管理库实际项目里MPN主数据往往以Excel文件形式从供应商系统导出需要先导入数据库。下面是一段可跑的Python脚本使用pandas和pymysql。import pandas as pd import pymysql def normalize_mpn(s: str) - str: if not s: return return .join(ch for ch in s.strip().upper() if ch.isalnum()) df pd.read_excel(mpn_upload.xlsx, dtype{mpn: str, manufacturer: str}) conn pymysql.connect(hostlocalhost, userinv_user, password***, databaseinventory, charsetutf8mb4, autocommitFalse) cur conn.cursor() insert_sql ( INSERT INTO mpn_catalog (sku_id, mpn_raw, mpn_normalized, manufacturer, mpn_status) VALUES (%s, %s, %s, %s, 1) ON DUPLICATE KEY UPDATE mpn_raw VALUES(mpn_raw) ) rows [] for _, row in df.iterrows(): raw str(row[mpn]).strip() rows.append((row[sku_id], raw, normalize_mpn(raw), str(row[manufacturer]).strip())) # 分批提交避免一次性executemany造成锁等待 for i in range(0, len(rows), 1000): cur.executemany(insert_sql, rows[i:i1000]) conn.commit() cur.close() conn.close()代码逻辑并不复杂但有两个参数容易被忽略。第一read_excel里必须用dtype{mpn: str}否则Excel里“00123”会被pandas读成数字123导入后MPN直接丢失前导零。第二ON DUPLICATE KEY UPDATE只在唯一键匹配到已有行时更新mpn_raw不改变sku_id和manufacturer避免把一个已经绑定到其他SKU的MPN误改掉。需要说明的是上面代码中的sku_id必须预先存在于sku表否则外键或应用层校验会失败。建议导入前先用set对比一遍Excel里的sku_id和数据库里的sku_id把不存在的行单独输出到错误清单。3. MPN清洗与标准化批量修改MPN的可用方法库存系统上线一段时间后你会发现表里已经攒下一堆从旧系统迁移来的MPN。这些数据长度不一、大小写混杂、有的带横线有的不带。如果前面建表时没有mpn_normalized列现在就需要一个可重复执行的清洗流程把“修改MPN”这件事变成能在生产环境安全操作的过程。3.1 脏MPN数据长什么样脏数据的核心特征是同物多码。我把日常最容易碰到的几种情况整理成一张表问题类型原始示例标准形式说明大小写混用grm188r71c104ka01dGRM188R71C104KA01D需要统一大写分隔符不统一ABC-123 / ABC_123 / ABC123ABC123横线、下划线、空格都应去掉全角字符-ABC123全角字母数字要转半角前导零丢失0123 - Excel读出12300123导入时强制按字符串处理多余后缀ABC123-REV1ABC123需要根据规则决定是否保留版本号这里要注意第5种后缀不能一刀切去掉。比如有的厂家把REV1当成生命周期版本它与ABC123是不同批次。最常见做法是增加一个mpn_extra列存储后缀而不是直接删除。清洗规则要和业务部门确认不要仅靠开发自己猜。3.2 用Python正则表达式统一MPN格式统一的清洗函数是库存管理应用的公共基础代码所有写入和查询入口都要复用同一个实现。import re import unicodedata def normalize_mpn(s: str) - str: if not s: return # NFKC把全角字母数字转成半角也处理一些不可见字符 s unicodedata.normalize(NFKC, str(s)) # 去首尾空格再转大写 s s.strip().upper() # 把中间所有非字母数字字符去掉包括连字符、下划线、空格、点号 s re.sub(r[^A-Z0-9], , s) return s这个函数与第2章里的简单版本相比增加了NFKC归一化和正则去除所有非字母数字。NFKC会把全角“”转成半角“A”也会把全角空格转成普通空格这比单独replace更彻底。正则里的[^A-Z0-9]表示匹配一个或多个不是大写字母或数字的字符统一用空串替换。之所以要用正则而不是直接replace(-, )是因为脏数据里的分隔符种类很多空格、点、斜杠、反斜杠都可能出现写一串replace冗长且容易漏。这段函数在清洗和查询时都要用因此建议单独放在一个模块里导入到所有需要处理MPN的脚本中。3.3 批量修改MPNSQL直接UPDATE与Python事务回写直接在数据库里执行UPDATE语句很快但风险极高。如果清洗规则写错或者数据中存在正常重复一条UPDATE会把全表改错而且MySQL没有自动回滚。更安全的做法是用Python先读出来、逐行清洗、最后检查重复再提交。import pymysql from mpn_clean import normalize_mpn conn pymysql.connect(hostlocalhost, userinv_user, password***, databaseinventory, autocommitFalse) cur conn.cursor(pymysql.cursors.DictCursor) # 第一步备份备份表名带上日期方便回退 cur.execute(CREATE TABLE mpn_catalog_bak_20250101 AS SELECT * FROM mpn_catalog) cur.execute(SELECT id, mpn_raw, mpn_normalized FROM mpn_catalog WHERE mpn_status1) rows cur.fetchall() to_update [] for row in rows: new_norm normalize_mpn(row[mpn_raw]) if new_norm ! row[mpn_normalized]: to_update.append((new_norm, row[id])) # 用executemany比逐条execute快但需要保持事务边界 update_sql UPDATE mpn_catalog SET mpn_normalized%s WHERE id%s cur.executemany(update_sql, to_update) # 更新后查重如果同厂、同normalized出现多条说明清洗规则导致合并过宽 cur.execute( SELECT mpn_normalized, manufacturer, COUNT(*) AS cnt FROM mpn_catalog GROUP BY mpn_normalized, manufacturer HAVING COUNT(*) 1 ) duplicates cur.fetchall() if duplicates: print(发现重复已回滚请检查清洗规则, duplicates) conn.rollback() else: conn.commit() print(成功更新, len(to_update), 条MPN)这段脚本的步骤顺序是有讲究的备份在最前删除或修改在后全部更新完成后再进行重复检查一旦发现问题回滚可以让整个操作像没发生过一样。很多意外都出在“先更新再查重”的顺序上等发现重复时事务已经提交。另外to_update列表可能非常大此时应分批commit比如每5000行一次避免单个事务持有过多行锁。回滚语句只在重复检测这一步生效前面已经提交过的分批事务不能回滚所以如果要严格保证原子性就不应该在循环里commit。4. MPN在库存业务里的查询匹配与索引调优库存管理应用里的MPN查询往往不是简单等值匹配。库内操作人员直接扫描MPN上的条码供应商发来的对账单里又是另一种写法。这一章把精确查询、模糊匹配和索引性能放在一起说因为实际调优时这三者互相影响。4.1 按MPN精确查询库存的SQL与参数绑定最常见操作是前端输入一个MPN立即显示这个零件的可用库存。下面这条SQL是标准的关联查询。SELECT ib.sku_id, ib.warehouse_code, ib.quantity, mc.mpn_raw, mc.mpn_normalized, mc.manufacturer FROM inventory_balance ib JOIN mpn_catalog mc ON ib.sku_id mc.sku_id WHERE mc.mpn_normalized %s;这里的参数%s来自应用层使用者输入的MPN先经过normalize_mpn再传入SQL而不是直接拼接字符串。使用参数绑定一方面防止SQL注入另一方面让MySQL可以复用预编译执行计划。如果用户在页面上输入“ABC-123”应用层把它换成“ABC123”这条查询就能命中mpn_normalized上的唯一索引返回速度在几十万行表上也只有毫秒级。需要留意的是不能写成WHERE mc.mpn_normalized LIKE %ABC123%这会让索引失效精确等值才是默认路径。4.2 模糊匹配MPN什么时候用LIKE与REGEXP精确匹配要求用户输对字符但供应商导出的对账单经常带着版本后缀比如ABC123REV1实际要找的零件是ABC123。此时可以先精确匹配查不到再走模糊。下面SQL用于按前缀匹配SELECT sku_id, mpn_raw, mpn_normalized, manufacturer FROM mpn_catalog WHERE mpn_normalized LIKE ABC123%;这种写法只在前缀固定时能走索引如果改用LIKE %ABC123%MySQL会放弃索引做全表扫描。另一种更灵活的写法是REGEXPSELECT sku_id, mpn_raw, mpn_normalized FROM mpn_catalog WHERE mpn_normalized REGEXP ^(ABC[-_]?123|ABC123)[A-Z0-9]*$;REGEXP适合处理分隔符差异但要注意两点第一正则中.、[、]等元字符都要转义否则会把它们当通配符第二REGEXP无法利用普通B树索引数据量大时很慢。我建议把模糊匹配限定在管理端或对账页面让库存操作员依然使用精确扫码路径。下表是三种方式的性能对比查询方式示例能否用索引适用场景等值匹配mpn_normalized %s是扫码、精确搜索前缀LIKELIKE ABC123%是版本号搜索全模糊LIKE/REGEXPLIKE %ABC123%否低频对账4.3 性能检查用EXPLAIN看MPN索引有没有走很多系统慢在关联查询没走主键索引而开发者只盯着WHERE条件。可以用EXPLAIN快速定位。EXPLAIN SELECT ib.sku_id, ib.quantity FROM inventory_balance ib JOIN mpn_catalog mc ON ib.sku_id mc.sku_id WHERE mc.mpn_normalized ABC123;结果里重点看type列如果mc表是const或ref说明mpn_normalized的唯一索引被使用如果typeALL就是全表扫描。另一个常见坑是关联字段字符集不一致。比如mpn_catalog.sku_id是BIGINTinventory_balance.sku_id是INT或者VARCHARMySQL在join时会对每行做隐式转换导致索引失效。解决办法是让两张表的sku_id类型完全一致。此外如果查询里写成WHERE UPPER(mpn_normalized) ABC123函数包裹字段会使索引失效应该像4.1那样在应用层把参数转成大写。库存管理应用后期数据量上了百万再回头补这种索引就很痛苦所以建表时就应确认字段类型与字符集处处一致。5. 验证库存管理中的MPN应用效果三个边界技巧写完清洗、导入、查询并不代表任务完成。更需要在边界条件下验证比如有人绕过应用程序直接改数据库、对账单里出现重复MPN、导出的Excel让采购肉眼找错。下面三个技巧可以帮你在这些边界上兜住问题。5.1 用CHECK约束锁住写入入口应用层清洗做得再完善也挡不住DBA手动执行一条INSERT。MySQL 8.0.16及以上版本支持CHECK约束可以在数据库层挡掉格式非法的MPNALTER TABLE mpn_catalog ADD CONSTRAINT chk_mpn_norm_format CHECK (mpn_normalized UPPER(mpn_normalized) AND mpn_normalized );这条约束保证不会插入小写normalized或空字符串。参数说明UPPER是MySQL函数在CHECK中允许使用但不能使用函数索引因此约束只做校验不负责清洗仍需要应用层提前处理。生产环境如果担心迁移麻烦也可以改为在插入前触发器和定期巡检脚本约束语法更简洁。5.2 每日对账脚本统计重复率和孤儿MPN库存管理不只关心现在有多少货也关心数据质量是否在恶化。一个定时脚本可以统计重复率和孤儿数据cur.execute( SELECT COUNT(*) AS total, COUNT(DISTINCT mpn_normalized, manufacturer) AS unique_cnt FROM mpn_catalog WHERE mpn_status1 ) row cur.fetchone() print(f重复率: {100 - row[unique_cnt] * 100.0 / row[total]:.2f}%) cur.execute( SELECT mc.id, mc.sku_id FROM mpn_catalog mc LEFT JOIN sku s ON mc.sku_id s.sku_id WHERE s.sku_id IS NULL )这里的核心是先看总记录数与去重数两者差就是重复行占用。如果重复率突然升高说明最近有脏数据进入需要检查导入脚本是否没走normalize函数。孤儿MPN则说明SKU已经被删除但映射还留着这类记录会在联合查询中出现空行应该清掉或置为停用状态。5.3 导Excel时用条件格式标出重复MPN给采购导出的MPN清单里最怕上下两行长得不一样但其实是同一个零件。在Excel中加一个辅助列写入公式COUNTIF(A:A,A2)然后对这个辅助列设置条件格式值大于1的标红。具体操作是在B2输入公式双击填充选中B列在开始菜单的条件格式里选择突出显示单元格规则-大于填入1。这样即使不懂SQL的人也能一眼看出对账单里哪些MPN已经重复。条件格式的范围不要选整列否则Excel会把空白单元格也算进去选实际数据行A2:A10000即可。本文还有配套的精品资源点击获取
库存管理中的MPN主数据建模与查询优化实战
简介围绕MPN制造商零件编号在库存管理中的落地这份Word技术文档面向SAP物料、采购与供应链相关岗位的顾问和业务人员重点解决同一物料对应多个制造商编号时的数据对接、独立库存与替换管理问题。文档包含1个doc文件压缩包大小566KB。内容概述了库存管理型MPN应用的总体架构覆盖物料、采购、库存、MPN管理等核心模块并通过完整实例串联创建制造商XK01与供应商、建立部物料号MM01及关联物料、维护替换关系PIC01、创建采购订单ME21N、收货MIGO、库存查询MMBE到采购替换件ME22N等关键操作便于读者按步骤理解系统配置与业务流。相比仅介绍外部编码对接的方案文档更强调Inventory managed MPN的落地路径可支撑替换件库存共享与替换作业。当前已有222人学习适合希望快速掌握SAP库存管理MPN应用路径的实施顾问、物料管理和采购人员。1. 库存管理中的MPN应用为什么要把一串零件号当系统一等公民在仓库收货区最让人头疼的不是货多而是同一只料换了供应商以后MPN从ABC-123变成ABC123系统里却只有一条SKU记录。MPN是制造商零件编号在采购、质检、领料、追溯这些环节它才是供应链里最接近真实物料的身份标识。很多库存系统把MPN当作一个可填可不填的备注字段结果对账时全靠人眼匹配出错了只能翻聊天记录。这篇文章要讲的是如何把MPN从备注提升为库存管理的主数据覆盖表结构设计、数据清洗、批量修改、查询匹配和最终验证。适合正在做WMS、ERP、进销存或供应链中台的开发与运维人员尤其是被多供应商、多编码规则折腾过的人。2. MPN主数据建模把库存管理的物料标识装进表里在设计库存管理应用时第一个要做的决定不是写接口而是决定MPN存在哪。把MPN直接挂在SKU表的varchar列上确实简单但很快就会发现同一个SKU对应多个MPN或者不同厂家用了同一串数字。正确做法是把MPN抽出来作为独立的主数据表和SKU建立多对多关系。2.1 MPN与SKU、UPC的关系模型SKU库存单位是公司内部对可存储、可销售物品的编号一个SKU对应一个仓内管理粒度UPC/EAN是零售条码主要在扫码环节使用MPN则是制造商给出的零件编号。三者并不是一一对应。举个例子一家电子贸易商销售同一种10uF电容可能同时从村田和三星进货同一个SKU就需要挂两条MPNGRM188R71C104KA01D和CL10B104KA8NNNC。反过来同一颗芯片被用在两条不同产品线里公司可能建了两个SKU但MPN只有一个。因此常见的做法是建立mpn_catalog映射表把MPN作为独立实体同时保留与SKU的多对多关系而不是在SKU表里加一列mpn。这样当供应商切换、或者同一颗料被多个SKU引用时只需要维护映射表。下面是对照关系示例表作用主键说明sku内部库存单位sku_id每条代表一个可管理物料mpn_catalogMPN主数据mpn_id每条代表一个制造商零件编号sku_mpn_mapSKU与MPN映射sku_idmpn_id处理多对多关系这种三个表的结构比在SKU表上堆MPN字段更清晰也方便用外键约束保证数据完整。如果业务规模不大可以把前两个表合一只保留mpn_catalog里的sku_id外键此时一个SKU对应多行MPN记录即可但反向多对多会比较麻烦。我一般建议直接用独立映射表理由在后续查询章节就能看见。2.2 创建MPN主数据表字段长度、字符集与唯一约束下面这段SQL可以作为库存管理应用的起点。CREATE TABLE mpn_catalog ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, sku_id BIGINT UNSIGNED NOT NULL COMMENT 关联SKU主键, mpn_raw VARCHAR(128) NOT NULL COMMENT 制造商给出的原始MPN, mpn_normalized VARCHAR(128) NOT NULL COMMENT 清洗后的MPN查询用, manufacturer VARCHAR(128) NOT NULL DEFAULT COMMENT 制造商名称, mpn_status TINYINT NOT NULL DEFAULT 1 COMMENT 1启用 0停用, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uk_mpn_norm_manu (mpn_normalized, manufacturer), KEY idx_sku (sku_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENTMPN主数据表与SKU映射;这段SQL里有几个参数值得细看。mpn_raw存原始字符串mpn_normalized存统一格式后的值唯一约束加在normalized和manufacturer上而不是raw字段上。原因是ABC-123和ABC123对人类是同一个零件但字符串不同如果对raw加唯一约束导入时会频繁报冲突。mpn_normalized会根据清洗规则把大小写、分隔符去掉比如ABC-123变成ABC123这样同一事实只占一行。字段长度128字符覆盖绝大多数半导体、连接器和机械件编号如果业务明确有更长编号可以改成256但不要用TEXT因为索引长度有限并且比较时会先做排序规则转换。字符集用utf8mb4因为一些欧洲厂家在MPN里带了°、Ω这类符号utf8mb4能存下。排序规则建议utf8mb4_general_ci配合mpn_normalized存储大小写在应用层已经统一不会影响查询。创建完表之后建议再加上外键约束。但在生产环境里我更推荐不用物理外键而是在应用层校验sku_id是否存在否则大批量导入时外键会拖慢速度。2.3 用Python从Excel导入MPN并写进库存管理库实际项目里MPN主数据往往以Excel文件形式从供应商系统导出需要先导入数据库。下面是一段可跑的Python脚本使用pandas和pymysql。import pandas as pd import pymysql def normalize_mpn(s: str) - str: if not s: return return .join(ch for ch in s.strip().upper() if ch.isalnum()) df pd.read_excel(mpn_upload.xlsx, dtype{mpn: str, manufacturer: str}) conn pymysql.connect(hostlocalhost, userinv_user, password***, databaseinventory, charsetutf8mb4, autocommitFalse) cur conn.cursor() insert_sql ( INSERT INTO mpn_catalog (sku_id, mpn_raw, mpn_normalized, manufacturer, mpn_status) VALUES (%s, %s, %s, %s, 1) ON DUPLICATE KEY UPDATE mpn_raw VALUES(mpn_raw) ) rows [] for _, row in df.iterrows(): raw str(row[mpn]).strip() rows.append((row[sku_id], raw, normalize_mpn(raw), str(row[manufacturer]).strip())) # 分批提交避免一次性executemany造成锁等待 for i in range(0, len(rows), 1000): cur.executemany(insert_sql, rows[i:i1000]) conn.commit() cur.close() conn.close()代码逻辑并不复杂但有两个参数容易被忽略。第一read_excel里必须用dtype{mpn: str}否则Excel里“00123”会被pandas读成数字123导入后MPN直接丢失前导零。第二ON DUPLICATE KEY UPDATE只在唯一键匹配到已有行时更新mpn_raw不改变sku_id和manufacturer避免把一个已经绑定到其他SKU的MPN误改掉。需要说明的是上面代码中的sku_id必须预先存在于sku表否则外键或应用层校验会失败。建议导入前先用set对比一遍Excel里的sku_id和数据库里的sku_id把不存在的行单独输出到错误清单。3. MPN清洗与标准化批量修改MPN的可用方法库存系统上线一段时间后你会发现表里已经攒下一堆从旧系统迁移来的MPN。这些数据长度不一、大小写混杂、有的带横线有的不带。如果前面建表时没有mpn_normalized列现在就需要一个可重复执行的清洗流程把“修改MPN”这件事变成能在生产环境安全操作的过程。3.1 脏MPN数据长什么样脏数据的核心特征是同物多码。我把日常最容易碰到的几种情况整理成一张表问题类型原始示例标准形式说明大小写混用grm188r71c104ka01dGRM188R71C104KA01D需要统一大写分隔符不统一ABC-123 / ABC_123 / ABC123ABC123横线、下划线、空格都应去掉全角字符-ABC123全角字母数字要转半角前导零丢失0123 - Excel读出12300123导入时强制按字符串处理多余后缀ABC123-REV1ABC123需要根据规则决定是否保留版本号这里要注意第5种后缀不能一刀切去掉。比如有的厂家把REV1当成生命周期版本它与ABC123是不同批次。最常见做法是增加一个mpn_extra列存储后缀而不是直接删除。清洗规则要和业务部门确认不要仅靠开发自己猜。3.2 用Python正则表达式统一MPN格式统一的清洗函数是库存管理应用的公共基础代码所有写入和查询入口都要复用同一个实现。import re import unicodedata def normalize_mpn(s: str) - str: if not s: return # NFKC把全角字母数字转成半角也处理一些不可见字符 s unicodedata.normalize(NFKC, str(s)) # 去首尾空格再转大写 s s.strip().upper() # 把中间所有非字母数字字符去掉包括连字符、下划线、空格、点号 s re.sub(r[^A-Z0-9], , s) return s这个函数与第2章里的简单版本相比增加了NFKC归一化和正则去除所有非字母数字。NFKC会把全角“”转成半角“A”也会把全角空格转成普通空格这比单独replace更彻底。正则里的[^A-Z0-9]表示匹配一个或多个不是大写字母或数字的字符统一用空串替换。之所以要用正则而不是直接replace(-, )是因为脏数据里的分隔符种类很多空格、点、斜杠、反斜杠都可能出现写一串replace冗长且容易漏。这段函数在清洗和查询时都要用因此建议单独放在一个模块里导入到所有需要处理MPN的脚本中。3.3 批量修改MPNSQL直接UPDATE与Python事务回写直接在数据库里执行UPDATE语句很快但风险极高。如果清洗规则写错或者数据中存在正常重复一条UPDATE会把全表改错而且MySQL没有自动回滚。更安全的做法是用Python先读出来、逐行清洗、最后检查重复再提交。import pymysql from mpn_clean import normalize_mpn conn pymysql.connect(hostlocalhost, userinv_user, password***, databaseinventory, autocommitFalse) cur conn.cursor(pymysql.cursors.DictCursor) # 第一步备份备份表名带上日期方便回退 cur.execute(CREATE TABLE mpn_catalog_bak_20250101 AS SELECT * FROM mpn_catalog) cur.execute(SELECT id, mpn_raw, mpn_normalized FROM mpn_catalog WHERE mpn_status1) rows cur.fetchall() to_update [] for row in rows: new_norm normalize_mpn(row[mpn_raw]) if new_norm ! row[mpn_normalized]: to_update.append((new_norm, row[id])) # 用executemany比逐条execute快但需要保持事务边界 update_sql UPDATE mpn_catalog SET mpn_normalized%s WHERE id%s cur.executemany(update_sql, to_update) # 更新后查重如果同厂、同normalized出现多条说明清洗规则导致合并过宽 cur.execute( SELECT mpn_normalized, manufacturer, COUNT(*) AS cnt FROM mpn_catalog GROUP BY mpn_normalized, manufacturer HAVING COUNT(*) 1 ) duplicates cur.fetchall() if duplicates: print(发现重复已回滚请检查清洗规则, duplicates) conn.rollback() else: conn.commit() print(成功更新, len(to_update), 条MPN)这段脚本的步骤顺序是有讲究的备份在最前删除或修改在后全部更新完成后再进行重复检查一旦发现问题回滚可以让整个操作像没发生过一样。很多意外都出在“先更新再查重”的顺序上等发现重复时事务已经提交。另外to_update列表可能非常大此时应分批commit比如每5000行一次避免单个事务持有过多行锁。回滚语句只在重复检测这一步生效前面已经提交过的分批事务不能回滚所以如果要严格保证原子性就不应该在循环里commit。4. MPN在库存业务里的查询匹配与索引调优库存管理应用里的MPN查询往往不是简单等值匹配。库内操作人员直接扫描MPN上的条码供应商发来的对账单里又是另一种写法。这一章把精确查询、模糊匹配和索引性能放在一起说因为实际调优时这三者互相影响。4.1 按MPN精确查询库存的SQL与参数绑定最常见操作是前端输入一个MPN立即显示这个零件的可用库存。下面这条SQL是标准的关联查询。SELECT ib.sku_id, ib.warehouse_code, ib.quantity, mc.mpn_raw, mc.mpn_normalized, mc.manufacturer FROM inventory_balance ib JOIN mpn_catalog mc ON ib.sku_id mc.sku_id WHERE mc.mpn_normalized %s;这里的参数%s来自应用层使用者输入的MPN先经过normalize_mpn再传入SQL而不是直接拼接字符串。使用参数绑定一方面防止SQL注入另一方面让MySQL可以复用预编译执行计划。如果用户在页面上输入“ABC-123”应用层把它换成“ABC123”这条查询就能命中mpn_normalized上的唯一索引返回速度在几十万行表上也只有毫秒级。需要留意的是不能写成WHERE mc.mpn_normalized LIKE %ABC123%这会让索引失效精确等值才是默认路径。4.2 模糊匹配MPN什么时候用LIKE与REGEXP精确匹配要求用户输对字符但供应商导出的对账单经常带着版本后缀比如ABC123REV1实际要找的零件是ABC123。此时可以先精确匹配查不到再走模糊。下面SQL用于按前缀匹配SELECT sku_id, mpn_raw, mpn_normalized, manufacturer FROM mpn_catalog WHERE mpn_normalized LIKE ABC123%;这种写法只在前缀固定时能走索引如果改用LIKE %ABC123%MySQL会放弃索引做全表扫描。另一种更灵活的写法是REGEXPSELECT sku_id, mpn_raw, mpn_normalized FROM mpn_catalog WHERE mpn_normalized REGEXP ^(ABC[-_]?123|ABC123)[A-Z0-9]*$;REGEXP适合处理分隔符差异但要注意两点第一正则中.、[、]等元字符都要转义否则会把它们当通配符第二REGEXP无法利用普通B树索引数据量大时很慢。我建议把模糊匹配限定在管理端或对账页面让库存操作员依然使用精确扫码路径。下表是三种方式的性能对比查询方式示例能否用索引适用场景等值匹配mpn_normalized %s是扫码、精确搜索前缀LIKELIKE ABC123%是版本号搜索全模糊LIKE/REGEXPLIKE %ABC123%否低频对账4.3 性能检查用EXPLAIN看MPN索引有没有走很多系统慢在关联查询没走主键索引而开发者只盯着WHERE条件。可以用EXPLAIN快速定位。EXPLAIN SELECT ib.sku_id, ib.quantity FROM inventory_balance ib JOIN mpn_catalog mc ON ib.sku_id mc.sku_id WHERE mc.mpn_normalized ABC123;结果里重点看type列如果mc表是const或ref说明mpn_normalized的唯一索引被使用如果typeALL就是全表扫描。另一个常见坑是关联字段字符集不一致。比如mpn_catalog.sku_id是BIGINTinventory_balance.sku_id是INT或者VARCHARMySQL在join时会对每行做隐式转换导致索引失效。解决办法是让两张表的sku_id类型完全一致。此外如果查询里写成WHERE UPPER(mpn_normalized) ABC123函数包裹字段会使索引失效应该像4.1那样在应用层把参数转成大写。库存管理应用后期数据量上了百万再回头补这种索引就很痛苦所以建表时就应确认字段类型与字符集处处一致。5. 验证库存管理中的MPN应用效果三个边界技巧写完清洗、导入、查询并不代表任务完成。更需要在边界条件下验证比如有人绕过应用程序直接改数据库、对账单里出现重复MPN、导出的Excel让采购肉眼找错。下面三个技巧可以帮你在这些边界上兜住问题。5.1 用CHECK约束锁住写入入口应用层清洗做得再完善也挡不住DBA手动执行一条INSERT。MySQL 8.0.16及以上版本支持CHECK约束可以在数据库层挡掉格式非法的MPNALTER TABLE mpn_catalog ADD CONSTRAINT chk_mpn_norm_format CHECK (mpn_normalized UPPER(mpn_normalized) AND mpn_normalized );这条约束保证不会插入小写normalized或空字符串。参数说明UPPER是MySQL函数在CHECK中允许使用但不能使用函数索引因此约束只做校验不负责清洗仍需要应用层提前处理。生产环境如果担心迁移麻烦也可以改为在插入前触发器和定期巡检脚本约束语法更简洁。5.2 每日对账脚本统计重复率和孤儿MPN库存管理不只关心现在有多少货也关心数据质量是否在恶化。一个定时脚本可以统计重复率和孤儿数据cur.execute( SELECT COUNT(*) AS total, COUNT(DISTINCT mpn_normalized, manufacturer) AS unique_cnt FROM mpn_catalog WHERE mpn_status1 ) row cur.fetchone() print(f重复率: {100 - row[unique_cnt] * 100.0 / row[total]:.2f}%) cur.execute( SELECT mc.id, mc.sku_id FROM mpn_catalog mc LEFT JOIN sku s ON mc.sku_id s.sku_id WHERE s.sku_id IS NULL )这里的核心是先看总记录数与去重数两者差就是重复行占用。如果重复率突然升高说明最近有脏数据进入需要检查导入脚本是否没走normalize函数。孤儿MPN则说明SKU已经被删除但映射还留着这类记录会在联合查询中出现空行应该清掉或置为停用状态。5.3 导Excel时用条件格式标出重复MPN给采购导出的MPN清单里最怕上下两行长得不一样但其实是同一个零件。在Excel中加一个辅助列写入公式COUNTIF(A:A,A2)然后对这个辅助列设置条件格式值大于1的标红。具体操作是在B2输入公式双击填充选中B列在开始菜单的条件格式里选择突出显示单元格规则-大于填入1。这样即使不懂SQL的人也能一眼看出对账单里哪些MPN已经重复。条件格式的范围不要选整列否则Excel会把空白单元格也算进去选实际数据行A2:A10000即可。本文还有配套的精品资源点击获取
