简介面向支付系统开发、金融数据分析等场景的银行卡BIN数据包源自银联官方2020年4月25日发布的最新最全银行卡BIN信息覆盖非标卡、农民工卡、跨行转账卡、单位结算卡及普通卡等各类卡组织。资源共9868条记录完整包含BIN码、BIN长度、发卡行、银行卡名称、银行卡类型、卡号长度等核心字段且已整理为可直接导入MySQL的SQL脚本免去手工清洗与转换的麻烦。压缩包仅1.12MB共7个文件其中5个Excel按卡种分表存放便于分类查阅另附SQL文件用于快速建库加载。当前已有3490人下载学习。对于需要权威卡BIN做支付路由、风控识别或数据分析的开发者而言这份官方口径的数据能直接作为基础库使用按需查询或批量比对都很方便。1. 银行卡BIN数据是什么给卡号做“户籍识别”的地基做支付系统的人迟早会发现区分一张卡是哪个银行发的、是借记卡还是贷记卡最快的方式不是去查开户行而是直接拿卡号前六位去匹配“银行卡bin数据(ExcelMySQL)-2020最新最全-银联官方发布”这类整理好的BIN索引。我第一次拿到这份数据时用本地查询替换了某个收费且响应慢的第三方识别接口把一次支付接口调用的耗时从几百毫秒压到个位数毫秒同时省掉了按次计费的成本。这类数据集在圈子里流通很广核心价值只有一个输入卡号立刻返回发卡行、卡种、卡类型。做支付接口、写风控规则、补交易报表维度的开发者都应该在本地维护一份可查询的BIN数据而不是把每一次判断都交给外部接口。2. 读懂BIN表字段口径、卡号规则与两种格式的差异2.1 BIN的划分逻辑为什么说“前6位”只是一个起点银行卡BINBank Identification Number是卡号前段用于标识发卡机构的部分。早期标准下6位BIN足够区分发卡行但这些年卡组织陆续启用8位BIN来支撑更多发卡机构和产品线。国内很多新发行的卡仍然是6位BIN为主但你在设计表结构和查询逻辑时最好从一开始就兼容8位否则后续维护会很被动。BIN数据解决的是“卡是谁发的”而卡号本身是否合法靠的是Luhn算法。Luhn只能验证卡号是否符合生成规则不能证明卡片真实存在。在业务里一般先做Luhn校验过滤明显乱写的卡号再拿BIN去匹配发卡行。这个先后顺序能省掉大量无意义的BIN查询。def luhn_ok(card_no: str) - bool: 校验银行卡号的Luhn算法合法返回True if not card_no.isdigit(): return False digits [int(c) for c in card_no] # 从右往左数偶数位乘2结果大于9就减9 for i in range(len(digits) - 2, -1, -2): d digits[i] * 2 digits[i] d - 9 if d 9 else d return sum(digits) % 10 0上面这个函数按标准Luhn实现入参是完整卡号字符串。业务里我一般把它放在BIN查询之前先丢给这个函数连Luhn都过不了就直接返回“卡号非法”不再走BIN表。参数说明里唯一要留意的是入参必须传字符串不能传整数否则卡号以0开头时会被Python自动丢掉前导位。2.2 一张能直接上线的BIN表该有哪些字段市面上流通的BIN表字段大同小异但落地的时候不能拿到就用。我建议至少包含以下字段并额外加上数据版本和有效期方便后面排查线上问题。字段类型建议必填说明bin_noCHAR(6)是6位BIN号文本类型存储bin_no_8CHAR(8)否8位BIN号为空时用6位匹配issuer_nameVARCHAR(64)是发卡行标准名称card_productVARCHAR(32)否卡种名如标准借记卡、联名卡card_kindTINYINT是1借记 2贷记 3准贷记 9未知card_orgVARCHAR(16)是卡组织标识provinceVARCHAR(16)否发卡省份可用于区域风控eff_dateDATE否该BIN生效日期exp_dateDATE否失效日期NULL表示长期有效data_versionVARCHAR(16)是数据文件版本如202001注意两件事第一bin_no和bin_no_8都必须用CHAR不能用INT原因后文避坑章节会详细讲第二card_kind不要用中文枚举值直接落库用TINYINT加注释查询时再关联字典这样索引效率和扩展性都好得多。2.3 Excel版和MySQL版先想清楚自己拿来干什么标题里同时给了两种格式这是这类数据包的常见形态。Excel版适合人工核对比如你要向审计解释某条识别结果的依据打开Excel筛选一下很快MySQL版适合直接进业务库联表查询省去转换步骤。我的习惯是两个都要Excel留档MySQL进测试库先验证确认没问题再上生产。两份数据的一致性值得花半小时验一下。最常见的问题是Excel导成MySQL时某些单元格被当作日期或数字处理导致BIN号变形、卡种漏值。验证办法很朴素先数MySQL里的总行数对不对再抽几个特定发卡行的BIN去Excel里反向查找。如果连总数都能对不上后面的查询逻辑做得再花哨也没有意义。3. 把Excel版导入MySQL从建表到数据校验的一次性落地3.1 先建表字段类型错了导入再快也是白干导入之前先建表。下面是我常用的建表语句拆开来看每一条都有目的bin相关字段用CHAR而不是VARCHAR因为BIN长度固定CHAR查询更快card_kind用TINYINT而非字符串压缩存储空间索引只建在查询最频繁的bin_no上不要给每个字段都加索引否则导入慢、占用大。CREATE TABLE card_bin ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bin_no CHAR(6) NOT NULL COMMENT 6位BIN文本存储防丢前导零, bin_no_8 CHAR(8) DEFAULT NULL COMMENT 8位BIN扩展, issuer_name VARCHAR(64) NOT NULL COMMENT 发卡行名称, card_product VARCHAR(32) DEFAULT NULL COMMENT 卡种名, card_kind TINYINT NOT NULL COMMENT 1借记 2贷记 3准贷记 9未知, card_org VARCHAR(16) NOT NULL COMMENT 卡组织标识, province VARCHAR(16) DEFAULT NULL COMMENT 发卡省份, eff_date DATE DEFAULT NULL COMMENT 生效日期, exp_date DATE DEFAULT NULL COMMENT 失效日期NULL长期有效, data_version VARCHAR(16) NOT NULL COMMENT 数据版本号, UNIQUE KEY uk_bin (bin_no, bin_no_8), KEY idx_bin6 (bin_no), KEY idx_issuer (issuer_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT银行卡BIN表;UNIQUE KEY建在(bin_no, bin_no_8)上是因为同一张卡可能同时有6位BIN和8位BIN两行记录单纯给bin_no建唯一索引会造成误判导入中途报错。MySQL版本在8.0以下时utf8mb4的索引长度限制要留意好在这个表字段都不长不会触发。3.2 用Python把Excel批量灌进MySQL最小脚本建完表就直接灌数据。pandas读Excel很快但有一个坑几乎必踩纯数字列会被自动读成int64导致以0开头的BIN号丢失前导零。所以读文件时必须显式指定dtype为str。import pandas as pd import pymysql # 按实际Excel列名调整这里的映射 COLUMN_MAP { BIN: bin_no, BIN8: bin_no_8, 发卡行: issuer_name, 卡种: card_product, 卡类型: card_kind, 卡组织: card_org, 省份: province, 生效日期: eff_date, 失效日期: exp_date, 版本: data_version, } def load_bin_excel(path: str) - pd.DataFrame: # dtypestr 防止BIN被读成int而丢失前导零 df pd.read_excel(path, dtypestr) df df.rename(columnsCOLUMN_MAP) # 统一空值pandas的NaN要让MySQL认成NULL df df.where(pd.notna(df), None) return df def import_to_mysql(df: pd.DataFrame, conn) - int: records df.to_dict(records) sql INSERT INTO card_bin (bin_no, bin_no_8, issuer_name, card_product, card_kind, card_org, province, eff_date, exp_date, data_version) VALUES (%(bin_no)s, %(bin_no_8)s, %(issuer_name)s, %(card_product)s, %(card_kind)s, %(card_org)s, %(province)s, %(eff_date)s, %(exp_date)s, %(data_version)s) cursor conn.cursor() cursor.executemany(sql, records) conn.commit() return cursor.rowcount if __name__ __main__: df load_bin_excel(银行卡BIN表_2020.xlsx) conn pymysql.connect( host127.0.0.1, userroot, passwordchange_me, databasepaydb, charsetutf8mb4 ) total import_to_mysql(df, conn) print(f导入完成共 {total} 行) conn.close()脚本里最关键的两个参数一是read_excel的dtypestr这是保住BIN号前导零的底线二是pymysql连接里要带charsetutf8mb4否则中文发卡行名称在部分MySQL配置下会变成乱码。executemany批量插入比逐行execute快一个数量级几万行数据通常几秒就导完。如果Excel里有合并单元格pandas读进来会出现NaN脚本里的where(pd.notna(df), None)就是处理这个的。3.3 导入后必做的三项校验数据有没有废一眼看出来导入完成别急着接业务。先用四条SQL做完整性检查全部通过再往下一层走。-- 1. 总数核对和Excel行数对不上就是导入丢了行 SELECT COUNT(*) FROM card_bin; -- 2. 重复BIN检查同一BIN出现多次说明源文件或合并逻辑有问题 SELECT bin_no, COUNT(*) AS cnt FROM card_bin GROUP BY bin_no HAVING cnt 1 LIMIT 10; -- 3. 卡类型取值检查值不在1/2/3/9范围内就是洗数据时出了错 SELECT card_kind, COUNT(*) AS cnt FROM card_bin GROUP BY card_kind; -- 4. 抽样核对选定几个发卡行人工去Excel里反查 SELECT bin_no, issuer_name, card_product, card_kind FROM card_bin WHERE issuer_name IN (某城商行, 某股份制银行) LIMIT 20;第一、二条是数量维度第三、四条是质量维度。实际踩过的坑是源Excel里同一BIN对应多个卡种直接导入后GROUP BY会出现重复行如果不加处理线上查询时LIMIT 1取到的可能不是想要的卡种。所以第四条抽样核对里我通常会顺带看一眼相同bin_no下是否有不同card_kind。4. BIN数据在业务里的四个典型用法从“能查”到“好用”4.1 秒级识别输入卡号返回发卡行与卡种最基础也最常用的场景。拿到一个完整卡号先Luhn校验再按BIN匹配。-- 业务层传入完整卡号优先匹配8位BIN取不到再退6位 SELECT issuer_name, card_product, card_kind FROM card_bin WHERE bin_no_8 LEFT(6228480402564890018, 8) OR (bin_no_8 IS NULL AND bin_no LEFT(6228480402564890018, 6)) LIMIT 1;这里用LEFT截取卡号前段配合两个条件的OR优先级要注意bin_no_8有值的先走8位匹配为空的行走6位匹配。单独用WHERE bin_no LEFT(card_no, 6)也能跑通但会把8位BIN的卡也归到6位旧数据上识别结果可能是错的。LIMIT 1是为了防止同BIN多卡种时返回多行至于取哪一行取决于前面建表时UNIQUE KEY的设计。4.2 风控规则把“拒绝贷记卡”变成一条SQL反欺诈风控里BIN最常见的用途是判断卡种。比如某些大额优惠活动只允许借记卡参加风控规则需要把贷记卡挡在门外。-- 判断卡种返回1表示是借记卡非1则拦截 SELECT card_kind FROM card_bin WHERE bin_no LEFT(6228480402564890018, 6) LIMIT 1;落地的做法是在下单或支付接口里加一个前置判断先查BIN表拿card_kind不是借记卡直接返回风控拦截码。这里值得多说一句card_kind字段如果源数据里就有缺失查询会返回NULL代码里一定要用if kind ! 1而不是if kind 2来判断否则NULL值会让所有缺失卡种全部通过风控线上出大事。4.3 通道路由不同BIN走不同支付通道有些支付场景要按发卡行分流。比如某通道对某股份制银行的卡费率低对其它行费率一般路由系统就可以在发起支付前先查BIN命中指定发卡行就走优惠通道否则走默认通道。SELECT issuer_name, card_org FROM card_bin WHERE bin_no LEFT(6217003810023456789, 6) LIMIT 1;这个用法不复杂但有一个配置上的建议把“哪些发卡行走哪个通道”的映射关系放在配置中心不要写死在代码里因为通道费率经常变。BIN表负责告诉你是谁路由配置负责决定你去哪两者职责分开后维护的人才不容易打架。4.4 交易报表用BIN表补全分析维度交易流水表里通常只有card_no没有发卡行维度。要做“各发卡行交易金额排行”最省事的方式是和BIN表做关联补充。SELECT b.issuer_name, COUNT(t.id) AS tx_cnt, SUM(t.amount) AS tx_amt FROM txn t LEFT JOIN card_bin b ON t.card_no LIKE CONCAT(b.bin_no, %) GROUP BY b.issuer_name ORDER BY tx_amt DESC;这里用了LIKE关联在报表场景问题不大但在高频交易表上应避免。更稳的做法是应用层先解析出card_no对应的BIN再拿BIN去精确匹配。报表跑一次无所谓接口每次都LIKE扫描会让数据库CPU迅速拉满属于典型的“能用但不好用”的写法。5. BIN数据落地避坑最容易翻车的五个现场5.1 坑一把2020版“最新最全”当成永远够用的数据现象新发行的银行卡在系统里识别不出发卡行返回“未知银行”。原因银行卡BIN是持续增发的。每年都有新卡种上线有些城商行合并重组后发卡行名称也会变化。标题里的“2020最新最全”在当年靠谱但在今天看一定存在覆盖不到的新BIN。解决不要把BIN数据当成静态表。入库时保存data_version每季度做一次增量对比把新增BIN合入现网表。如果业务对时效敏感可以给查询接口增加“未命中时转第三方查询”的兜底逻辑既保留本地性能又保证新卡可识别。5.2 坑二BIN字段用了INT类型前导零被MySQL吃掉现象所有以0开头的BIN匹配全部失败但纯数字的BIN又正常。原因这是最经典的翻车现场。Excel里的BIN看起来是数字导入MySQL时如果表字段建的是INT前导零会被自动丢弃比如实际BIN是“012345”入库后变成“12345”。线上查询用“012345”去匹配永远匹配不到。解决建表字段统一用CHAR(6)或CHAR(8)导入脚本里指定dtypestr。已经在库里踩坑的先把列类型改成CHAR再用LPAD函数把丢失的前导零补回来。血泪经验任何卡号、BIN、证件号一律按文本处理不要存成整数。5.3 坑三只用6位匹配撞上8位BIN的卡现象某些卡识别出的发卡行和卡面印的银行不一致但不是全部卡都错。原因8位BIN的卡用6位也能匹配到一条旧数据但那条旧数据可能是同一机构早期申请的6位区间归属到具体卡种时会错位。8位BIN是卡组织为解决6位号码不够用而推出的扩展发行时间较晚的卡更容易命中这个坑。解决表结构里预留bin_no_8字段查询逻辑改成“先8位后6位”只有当bin_no_8为空时才退化到6位匹配。源数据里如果没有8位BIN列至少要在匹配逻辑上保留扩展位方便后续补数。5.4 坑四只存发卡行不存卡种和卡组织现象风控想拦截贷记卡但BIN表里查不到卡种只能放行或全部拦截通道想区分卡组织也做不到。原因拿到数据后只保留了自己当时最关注的字段删掉了看似用不到的列。等业务提新需求时原数据包已经不知道丢到哪里去了。解决哪怕暂时用不到也把card_kind、card_org、province这些字段原样入库。多占不了多少空间但将来某个风控或运营需求落地时你不会为了一张旧表重新找数据源、重新清洗。5.5 坑五Excel里有合并单元格或空行直接导入导致数据错位现象导入的总行数比Excel实际数据行数少几百行或某行的发卡行串到了上一行。原因部分表格为了好看发卡行列做了单元格合并pandas读出来之后只有第一行有值后面全为空。直接用这样的DataFrame入库大量行的issuer_name就是NULL抽样核对时才发现。解决读文件后先做空值检查对合并单元格的情况用ffill()向下填充再执行导入。填充前也要确认填充方向正确免得把表头也填进去。# 发卡行列做向下填充解决合并单元格导致的空值 df[issuer_name] df[issuer_name].ffill()6. 让BIN库长期不废三个低成本维护习惯第一每次拿到新版本BIN数据先导入一张bin_new临时表和现网表做对比输出新增、失效、变更三类差异后再决定是否更新现网表。对比SQL用NOT EXISTS就能实现不复杂但能避免“整表覆盖后旧卡全查不到”的惨剧。-- 找出新增BIN SELECT bin_no FROM bin_new n WHERE NOT EXISTS (SELECT 1 FROM card_bin o WHERE o.bin_no n.bin_no);第二维护一组固定卡号作为回归用例。选十几张不同发卡行、不同卡种的测试卡号写进一个配置文件每次更新BIN数据后批量跑一遍查询确认识别结果和更新前一致。这个习惯救过我一次某次更新后某城商行的借记卡全被识别成贷记卡就是靠回归用例第一时间发现的。第三把data_version写进业务日志。生产环境出现识别异常时先看日志里的版本号能立刻判断是数据问题还是代码问题不用靠猜。我现在每个月第一件事就是跑一遍BIN差异对账几分钟的事但能保证线上不会拿着过期数据硬扛。这套方案投入很小胜在稳定可预期希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/10/9 13:21:24 · 阅读 1.2k 银行卡BIN数据实战：从Excel导入MySQL到业务应用与避坑
简介面向支付系统开发、金融数据分析等场景的银行卡BIN数据包源自银联官方2020年4月25日发布的最新最全银行卡BIN信息覆盖非标卡、农民工卡、跨行转账卡、单位结算卡及普通卡等各类卡组织。资源共9868条记录完整包含BIN码、BIN长度、发卡行、银行卡名称、银行卡类型、卡号长度等核心字段且已整理为可直接导入MySQL的SQL脚本免去手工清洗与转换的麻烦。压缩包仅1.12MB共7个文件其中5个Excel按卡种分表存放便于分类查阅另附SQL文件用于快速建库加载。当前已有3490人下载学习。对于需要权威卡BIN做支付路由、风控识别或数据分析的开发者而言这份官方口径的数据能直接作为基础库使用按需查询或批量比对都很方便。1. 银行卡BIN数据是什么给卡号做“户籍识别”的地基做支付系统的人迟早会发现区分一张卡是哪个银行发的、是借记卡还是贷记卡最快的方式不是去查开户行而是直接拿卡号前六位去匹配“银行卡bin数据(ExcelMySQL)-2020最新最全-银联官方发布”这类整理好的BIN索引。我第一次拿到这份数据时用本地查询替换了某个收费且响应慢的第三方识别接口把一次支付接口调用的耗时从几百毫秒压到个位数毫秒同时省掉了按次计费的成本。这类数据集在圈子里流通很广核心价值只有一个输入卡号立刻返回发卡行、卡种、卡类型。做支付接口、写风控规则、补交易报表维度的开发者都应该在本地维护一份可查询的BIN数据而不是把每一次判断都交给外部接口。2. 读懂BIN表字段口径、卡号规则与两种格式的差异2.1 BIN的划分逻辑为什么说“前6位”只是一个起点银行卡BINBank Identification Number是卡号前段用于标识发卡机构的部分。早期标准下6位BIN足够区分发卡行但这些年卡组织陆续启用8位BIN来支撑更多发卡机构和产品线。国内很多新发行的卡仍然是6位BIN为主但你在设计表结构和查询逻辑时最好从一开始就兼容8位否则后续维护会很被动。BIN数据解决的是“卡是谁发的”而卡号本身是否合法靠的是Luhn算法。Luhn只能验证卡号是否符合生成规则不能证明卡片真实存在。在业务里一般先做Luhn校验过滤明显乱写的卡号再拿BIN去匹配发卡行。这个先后顺序能省掉大量无意义的BIN查询。def luhn_ok(card_no: str) - bool: 校验银行卡号的Luhn算法合法返回True if not card_no.isdigit(): return False digits [int(c) for c in card_no] # 从右往左数偶数位乘2结果大于9就减9 for i in range(len(digits) - 2, -1, -2): d digits[i] * 2 digits[i] d - 9 if d 9 else d return sum(digits) % 10 0上面这个函数按标准Luhn实现入参是完整卡号字符串。业务里我一般把它放在BIN查询之前先丢给这个函数连Luhn都过不了就直接返回“卡号非法”不再走BIN表。参数说明里唯一要留意的是入参必须传字符串不能传整数否则卡号以0开头时会被Python自动丢掉前导位。2.2 一张能直接上线的BIN表该有哪些字段市面上流通的BIN表字段大同小异但落地的时候不能拿到就用。我建议至少包含以下字段并额外加上数据版本和有效期方便后面排查线上问题。字段类型建议必填说明bin_noCHAR(6)是6位BIN号文本类型存储bin_no_8CHAR(8)否8位BIN号为空时用6位匹配issuer_nameVARCHAR(64)是发卡行标准名称card_productVARCHAR(32)否卡种名如标准借记卡、联名卡card_kindTINYINT是1借记 2贷记 3准贷记 9未知card_orgVARCHAR(16)是卡组织标识provinceVARCHAR(16)否发卡省份可用于区域风控eff_dateDATE否该BIN生效日期exp_dateDATE否失效日期NULL表示长期有效data_versionVARCHAR(16)是数据文件版本如202001注意两件事第一bin_no和bin_no_8都必须用CHAR不能用INT原因后文避坑章节会详细讲第二card_kind不要用中文枚举值直接落库用TINYINT加注释查询时再关联字典这样索引效率和扩展性都好得多。2.3 Excel版和MySQL版先想清楚自己拿来干什么标题里同时给了两种格式这是这类数据包的常见形态。Excel版适合人工核对比如你要向审计解释某条识别结果的依据打开Excel筛选一下很快MySQL版适合直接进业务库联表查询省去转换步骤。我的习惯是两个都要Excel留档MySQL进测试库先验证确认没问题再上生产。两份数据的一致性值得花半小时验一下。最常见的问题是Excel导成MySQL时某些单元格被当作日期或数字处理导致BIN号变形、卡种漏值。验证办法很朴素先数MySQL里的总行数对不对再抽几个特定发卡行的BIN去Excel里反向查找。如果连总数都能对不上后面的查询逻辑做得再花哨也没有意义。3. 把Excel版导入MySQL从建表到数据校验的一次性落地3.1 先建表字段类型错了导入再快也是白干导入之前先建表。下面是我常用的建表语句拆开来看每一条都有目的bin相关字段用CHAR而不是VARCHAR因为BIN长度固定CHAR查询更快card_kind用TINYINT而非字符串压缩存储空间索引只建在查询最频繁的bin_no上不要给每个字段都加索引否则导入慢、占用大。CREATE TABLE card_bin ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bin_no CHAR(6) NOT NULL COMMENT 6位BIN文本存储防丢前导零, bin_no_8 CHAR(8) DEFAULT NULL COMMENT 8位BIN扩展, issuer_name VARCHAR(64) NOT NULL COMMENT 发卡行名称, card_product VARCHAR(32) DEFAULT NULL COMMENT 卡种名, card_kind TINYINT NOT NULL COMMENT 1借记 2贷记 3准贷记 9未知, card_org VARCHAR(16) NOT NULL COMMENT 卡组织标识, province VARCHAR(16) DEFAULT NULL COMMENT 发卡省份, eff_date DATE DEFAULT NULL COMMENT 生效日期, exp_date DATE DEFAULT NULL COMMENT 失效日期NULL长期有效, data_version VARCHAR(16) NOT NULL COMMENT 数据版本号, UNIQUE KEY uk_bin (bin_no, bin_no_8), KEY idx_bin6 (bin_no), KEY idx_issuer (issuer_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT银行卡BIN表;UNIQUE KEY建在(bin_no, bin_no_8)上是因为同一张卡可能同时有6位BIN和8位BIN两行记录单纯给bin_no建唯一索引会造成误判导入中途报错。MySQL版本在8.0以下时utf8mb4的索引长度限制要留意好在这个表字段都不长不会触发。3.2 用Python把Excel批量灌进MySQL最小脚本建完表就直接灌数据。pandas读Excel很快但有一个坑几乎必踩纯数字列会被自动读成int64导致以0开头的BIN号丢失前导零。所以读文件时必须显式指定dtype为str。import pandas as pd import pymysql # 按实际Excel列名调整这里的映射 COLUMN_MAP { BIN: bin_no, BIN8: bin_no_8, 发卡行: issuer_name, 卡种: card_product, 卡类型: card_kind, 卡组织: card_org, 省份: province, 生效日期: eff_date, 失效日期: exp_date, 版本: data_version, } def load_bin_excel(path: str) - pd.DataFrame: # dtypestr 防止BIN被读成int而丢失前导零 df pd.read_excel(path, dtypestr) df df.rename(columnsCOLUMN_MAP) # 统一空值pandas的NaN要让MySQL认成NULL df df.where(pd.notna(df), None) return df def import_to_mysql(df: pd.DataFrame, conn) - int: records df.to_dict(records) sql INSERT INTO card_bin (bin_no, bin_no_8, issuer_name, card_product, card_kind, card_org, province, eff_date, exp_date, data_version) VALUES (%(bin_no)s, %(bin_no_8)s, %(issuer_name)s, %(card_product)s, %(card_kind)s, %(card_org)s, %(province)s, %(eff_date)s, %(exp_date)s, %(data_version)s) cursor conn.cursor() cursor.executemany(sql, records) conn.commit() return cursor.rowcount if __name__ __main__: df load_bin_excel(银行卡BIN表_2020.xlsx) conn pymysql.connect( host127.0.0.1, userroot, passwordchange_me, databasepaydb, charsetutf8mb4 ) total import_to_mysql(df, conn) print(f导入完成共 {total} 行) conn.close()脚本里最关键的两个参数一是read_excel的dtypestr这是保住BIN号前导零的底线二是pymysql连接里要带charsetutf8mb4否则中文发卡行名称在部分MySQL配置下会变成乱码。executemany批量插入比逐行execute快一个数量级几万行数据通常几秒就导完。如果Excel里有合并单元格pandas读进来会出现NaN脚本里的where(pd.notna(df), None)就是处理这个的。3.3 导入后必做的三项校验数据有没有废一眼看出来导入完成别急着接业务。先用四条SQL做完整性检查全部通过再往下一层走。-- 1. 总数核对和Excel行数对不上就是导入丢了行 SELECT COUNT(*) FROM card_bin; -- 2. 重复BIN检查同一BIN出现多次说明源文件或合并逻辑有问题 SELECT bin_no, COUNT(*) AS cnt FROM card_bin GROUP BY bin_no HAVING cnt 1 LIMIT 10; -- 3. 卡类型取值检查值不在1/2/3/9范围内就是洗数据时出了错 SELECT card_kind, COUNT(*) AS cnt FROM card_bin GROUP BY card_kind; -- 4. 抽样核对选定几个发卡行人工去Excel里反查 SELECT bin_no, issuer_name, card_product, card_kind FROM card_bin WHERE issuer_name IN (某城商行, 某股份制银行) LIMIT 20;第一、二条是数量维度第三、四条是质量维度。实际踩过的坑是源Excel里同一BIN对应多个卡种直接导入后GROUP BY会出现重复行如果不加处理线上查询时LIMIT 1取到的可能不是想要的卡种。所以第四条抽样核对里我通常会顺带看一眼相同bin_no下是否有不同card_kind。4. BIN数据在业务里的四个典型用法从“能查”到“好用”4.1 秒级识别输入卡号返回发卡行与卡种最基础也最常用的场景。拿到一个完整卡号先Luhn校验再按BIN匹配。-- 业务层传入完整卡号优先匹配8位BIN取不到再退6位 SELECT issuer_name, card_product, card_kind FROM card_bin WHERE bin_no_8 LEFT(6228480402564890018, 8) OR (bin_no_8 IS NULL AND bin_no LEFT(6228480402564890018, 6)) LIMIT 1;这里用LEFT截取卡号前段配合两个条件的OR优先级要注意bin_no_8有值的先走8位匹配为空的行走6位匹配。单独用WHERE bin_no LEFT(card_no, 6)也能跑通但会把8位BIN的卡也归到6位旧数据上识别结果可能是错的。LIMIT 1是为了防止同BIN多卡种时返回多行至于取哪一行取决于前面建表时UNIQUE KEY的设计。4.2 风控规则把“拒绝贷记卡”变成一条SQL反欺诈风控里BIN最常见的用途是判断卡种。比如某些大额优惠活动只允许借记卡参加风控规则需要把贷记卡挡在门外。-- 判断卡种返回1表示是借记卡非1则拦截 SELECT card_kind FROM card_bin WHERE bin_no LEFT(6228480402564890018, 6) LIMIT 1;落地的做法是在下单或支付接口里加一个前置判断先查BIN表拿card_kind不是借记卡直接返回风控拦截码。这里值得多说一句card_kind字段如果源数据里就有缺失查询会返回NULL代码里一定要用if kind ! 1而不是if kind 2来判断否则NULL值会让所有缺失卡种全部通过风控线上出大事。4.3 通道路由不同BIN走不同支付通道有些支付场景要按发卡行分流。比如某通道对某股份制银行的卡费率低对其它行费率一般路由系统就可以在发起支付前先查BIN命中指定发卡行就走优惠通道否则走默认通道。SELECT issuer_name, card_org FROM card_bin WHERE bin_no LEFT(6217003810023456789, 6) LIMIT 1;这个用法不复杂但有一个配置上的建议把“哪些发卡行走哪个通道”的映射关系放在配置中心不要写死在代码里因为通道费率经常变。BIN表负责告诉你是谁路由配置负责决定你去哪两者职责分开后维护的人才不容易打架。4.4 交易报表用BIN表补全分析维度交易流水表里通常只有card_no没有发卡行维度。要做“各发卡行交易金额排行”最省事的方式是和BIN表做关联补充。SELECT b.issuer_name, COUNT(t.id) AS tx_cnt, SUM(t.amount) AS tx_amt FROM txn t LEFT JOIN card_bin b ON t.card_no LIKE CONCAT(b.bin_no, %) GROUP BY b.issuer_name ORDER BY tx_amt DESC;这里用了LIKE关联在报表场景问题不大但在高频交易表上应避免。更稳的做法是应用层先解析出card_no对应的BIN再拿BIN去精确匹配。报表跑一次无所谓接口每次都LIKE扫描会让数据库CPU迅速拉满属于典型的“能用但不好用”的写法。5. BIN数据落地避坑最容易翻车的五个现场5.1 坑一把2020版“最新最全”当成永远够用的数据现象新发行的银行卡在系统里识别不出发卡行返回“未知银行”。原因银行卡BIN是持续增发的。每年都有新卡种上线有些城商行合并重组后发卡行名称也会变化。标题里的“2020最新最全”在当年靠谱但在今天看一定存在覆盖不到的新BIN。解决不要把BIN数据当成静态表。入库时保存data_version每季度做一次增量对比把新增BIN合入现网表。如果业务对时效敏感可以给查询接口增加“未命中时转第三方查询”的兜底逻辑既保留本地性能又保证新卡可识别。5.2 坑二BIN字段用了INT类型前导零被MySQL吃掉现象所有以0开头的BIN匹配全部失败但纯数字的BIN又正常。原因这是最经典的翻车现场。Excel里的BIN看起来是数字导入MySQL时如果表字段建的是INT前导零会被自动丢弃比如实际BIN是“012345”入库后变成“12345”。线上查询用“012345”去匹配永远匹配不到。解决建表字段统一用CHAR(6)或CHAR(8)导入脚本里指定dtypestr。已经在库里踩坑的先把列类型改成CHAR再用LPAD函数把丢失的前导零补回来。血泪经验任何卡号、BIN、证件号一律按文本处理不要存成整数。5.3 坑三只用6位匹配撞上8位BIN的卡现象某些卡识别出的发卡行和卡面印的银行不一致但不是全部卡都错。原因8位BIN的卡用6位也能匹配到一条旧数据但那条旧数据可能是同一机构早期申请的6位区间归属到具体卡种时会错位。8位BIN是卡组织为解决6位号码不够用而推出的扩展发行时间较晚的卡更容易命中这个坑。解决表结构里预留bin_no_8字段查询逻辑改成“先8位后6位”只有当bin_no_8为空时才退化到6位匹配。源数据里如果没有8位BIN列至少要在匹配逻辑上保留扩展位方便后续补数。5.4 坑四只存发卡行不存卡种和卡组织现象风控想拦截贷记卡但BIN表里查不到卡种只能放行或全部拦截通道想区分卡组织也做不到。原因拿到数据后只保留了自己当时最关注的字段删掉了看似用不到的列。等业务提新需求时原数据包已经不知道丢到哪里去了。解决哪怕暂时用不到也把card_kind、card_org、province这些字段原样入库。多占不了多少空间但将来某个风控或运营需求落地时你不会为了一张旧表重新找数据源、重新清洗。5.5 坑五Excel里有合并单元格或空行直接导入导致数据错位现象导入的总行数比Excel实际数据行数少几百行或某行的发卡行串到了上一行。原因部分表格为了好看发卡行列做了单元格合并pandas读出来之后只有第一行有值后面全为空。直接用这样的DataFrame入库大量行的issuer_name就是NULL抽样核对时才发现。解决读文件后先做空值检查对合并单元格的情况用ffill()向下填充再执行导入。填充前也要确认填充方向正确免得把表头也填进去。# 发卡行列做向下填充解决合并单元格导致的空值 df[issuer_name] df[issuer_name].ffill()6. 让BIN库长期不废三个低成本维护习惯第一每次拿到新版本BIN数据先导入一张bin_new临时表和现网表做对比输出新增、失效、变更三类差异后再决定是否更新现网表。对比SQL用NOT EXISTS就能实现不复杂但能避免“整表覆盖后旧卡全查不到”的惨剧。-- 找出新增BIN SELECT bin_no FROM bin_new n WHERE NOT EXISTS (SELECT 1 FROM card_bin o WHERE o.bin_no n.bin_no);第二维护一组固定卡号作为回归用例。选十几张不同发卡行、不同卡种的测试卡号写进一个配置文件每次更新BIN数据后批量跑一遍查询确认识别结果和更新前一致。这个习惯救过我一次某次更新后某城商行的借记卡全被识别成贷记卡就是靠回归用例第一时间发现的。第三把data_version写进业务日志。生产环境出现识别异常时先看日志里的版本号能立刻判断是数据问题还是代码问题不用靠猜。我现在每个月第一件事就是跑一遍BIN差异对账几分钟的事但能保证线上不会拿着过期数据硬扛。这套方案投入很小胜在稳定可预期希望帮到你。本文还有配套的精品资源点击获取