简介2020年4月版银行卡BIN号规范数据以压缩包形式发布面向金融风控、支付结算、数据分析等需要识别发卡行与卡片类型的从业者。数据覆盖信用卡、借记卡、农民工卡、跨行转账卡、非标卡及单位结算卡等分类可用于本地建表、关联查询、反欺诈识别和客户画像。压缩包共7个文件含5个Excel表格和2个SQL脚本Excel便于人工排序筛选与分析核对SQL脚本可直接导入MySQL或PostgreSQL快速生成bin_number、bank_name、card_type等字段的标准化表整体仅1.4MB。已有856人学习下载在银行卡归属行识别与交易校验场景中具备参考价值。使用时可依据银行卡前6位BIN快速匹配发卡机构也可借助SQL完成定期更新维护为交易安全校验和风险管理提供基础数据支撑。1. 最新银行卡BIN号数据是什么先搞清楚你缺哪块做支付对账的人最怕碰到这种场景一笔交易拦在风控规则里工单转过来查了半天发现是发卡行识别不出来本地库里根本没有这张卡的BIN记录。多数时候不是没数据而是本地那张BIN号表还是两个季度前的快照新发卡行、新卡种全都没跟上。这里说的BIN号就是银行卡号前6到8位识别码它决定这张卡属于哪个卡组织、由哪家银行发出、是借记卡还是贷记卡。标题里的「最新银行卡bin号 excel mysql pg」拆开看就是一条完整链路把整理好的BIN号清单从Excel出发分别灌进MySQL和PostgreSQL让交易和风控系统用得上。这篇文章按我实际推进的顺序来写适合正在做支付接入、交易路由、风控规则维护、以及要维护多套数据库环境的同学。2. 把BIN号整理成Excel清单字段设计、去重与CSV导出2.1 先定字段一张BIN号表至少要有这9列很多从公开渠道抓回来的BIN数据原始格式往往是网页表格或者文本文件字段乱且名称不统一。直接进库之前第一步是把它整理成一张标准Excel清单。我这里有一套固定字段跑过多次数据迁移按这套来两个数据库端到端不会出大乱子。字段名类型建议必填说明bin_numberVARCHAR(16)必填银行卡识别码6到8位数字一律按文本处理card_brandVARCHAR(32)必填卡组织枚举VISA/MASTERCARD/UNIONPAY/AMEX等card_typeVARCHAR(16)必填借记卡/贷记卡/准贷记卡bank_nameVARCHAR(64)必填发卡行名称bank_codeVARCHAR(16)选填发卡行行号或联行号card_levelVARCHAR(32)选填卡等级普卡/金卡/白金/钻石country_codeVARCHAR(8)选填国家或地区ISO代码source_versionVARCHAR(32)必填数据源版本标记比如2025Q3update_timeDATETIME必填这条记录生效的时间有几个字段的选择理由必须说清楚。bin_number用 VARCHAR 而不是数字因为BIN号不参与加减乘除只做前缀匹配存成数字纯属给自己加戏还容易丢前导零。bank_name 和 bank_code 要分开银行改名、合并是常事代码不变名称变拆开之后对账还能靠 code 关联。source_version 这一列最容易被人忽略但它是后面排查「新旧版本混用」的唯一依据没有它出了问题只能认栽。card_brand 和 card_type 是两个维度同一个卡组织既有借记卡也有贷记卡别混成一列。2.2 去重与变更处理同一BIN号出现在两个版本里怎么办整理Excel时最常遇到的就是重复。同一张卡上季度版本里有记录新版本里也有bin_number 完全一样但 bank_name 或者 card_level 可能变了。直接删会造成信息丢失不删进库又会主键冲突。先用公式把重复项标记出来这一步最直观COUNTIF(A:A, A2)1逻辑很简单筛选结果为 TRUE 的行就是这个BIN号在整表里出现了不止一次。公式好用在于你能先看到重复记录各自带了什么字段再判断是直接删还是更新。如果是纯重复每列都一样保留一行即可如果是同一BIN号在不同版本里银行归属变了那不是脏数据而是BIN回收、变更发卡行的正常情况处理原则是以 source_version 较新的为准旧记录不硬删归档到一张历史表里留底。清理完重复还要顺手处理几类脏数据全角空格和不可见字符用 TRIM 和 CLEAN 函数过一遍银行名称里「股份有限公司」「有限责任公司」这类后缀有的源带、有的不带最好统一口径括号和引号都换成半角不然后面CSV转义会坑到你。2.3 导出CSV而不是直接贴库三个必须检查的设置Excel整理好后下一步不是直接连数据库贴而是先另存为CSV。直接贴库是新手才会干的事临时测一下可以正式环境没人敢让Excel直连数据库。导出CSV前请务必检查三个设置缺一个后面就翻车。第一个是强制文本格式。选中所有数据列单元格格式设为「文本」尤其是 bin_number 这一列。Excel会在某一刻悄悄把看起来像数字的内容转成科学计数法6到8位的BIN当前不会溢出但表里如果还带了示例卡号或者完整卡号列16位以上数字就会变成 6.22E18这列数据就算废了。第二个是编码。Excel自带「CSV UTF-8」另存时通常带BOMMySQL的 LOAD DATA 读它第一列会多出一个看不见的\ufeff导致首个BIN号永远匹配不上。我一般不用Excel直接存而是用Python读一遍Excel再统一输出CSV控制编码和换行符import pandas as pd df pd.read_excel(bin_source.xlsx, dtype{bin_number: str, bank_code: str}) df df.drop_duplicates(subsetbin_number, keeplast) df[is_valid] df.get(is_valid, 1) df df[[bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version]] df.to_csv(bin_info_2025q3.csv, indexFalse, encodingutf-8)这里 dtype 参数强制把 bin_number 读成字符串避免pandas自动推断成int64列顺序固定是为了后面 MySQL 和 PG 的导入语句都不用再调列映射。第三个是换行符。pandas默认输出\nExcel另存是\r\n写数据库导入语句时要知道自己文件是什么换行MySQL端LINES TERMINATED BY写错轻则多出一列重则整文件导入失败。3. 导入MySQL建表、LOAD DATA 与临时表合并3.1 建表BIN号为什么用VARCHAR当主键MySQL这边我一般先建一个独立库避免和业务表混在一起。建表语句如下CREATE DATABASE IF NOT EXISTS pay_base DEFAULT CHARSET utf8mb4; CREATE TABLE pay_base.bin_info ( bin_number VARCHAR(16) NOT NULL COMMENT 银行卡识别码6到8位按文本处理, card_brand VARCHAR(32) NOT NULL COMMENT 卡组织枚举VISA/MASTERCARD/UNIONPAY/AMEX, card_type VARCHAR(16) NOT NULL COMMENT 借记卡/贷记卡/准贷记卡, bank_name VARCHAR(64) NOT NULL COMMENT 发卡行名称, bank_code VARCHAR(16) DEFAULT NULL COMMENT 发卡行行号, card_level VARCHAR(32) DEFAULT NULL COMMENT 卡等级, country_code VARCHAR(8) DEFAULT NULL COMMENT ISO国家代码, is_valid TINYINT NOT NULL DEFAULT 1 COMMENT 是否在用1在用 0已废弃, source_version VARCHAR(32) NOT NULL COMMENT 数据源版本标记, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (bin_number), KEY idx_brand (card_brand) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci;几个设计点说明一下。bin_number 做主键是因为业务查询永远是通过卡号前缀去精确命中的主键直接覆盖不需要多余回表。VARCHAR(16) 不是拍脑袋识别码目前最长8位留一倍余量足够太长反而浪费索引空间。is_valid 字段用来标记废弃BIN而不是直接删行因为历史流水可能还挂在旧BIN上删了就查不到对应关系。card_brand 上建普通索引是为了后面做统计报表不建也能跑但分组统计会全表扫几万行无所谓几十万行就有感知了。如果是接已有的库注意原表可能已经有 bin_number 列且不是主键这时候别直接改表结构先导出一份旧数据备份再扩列加主键避免在线上生产表上做高风险DDL。3.2 LOAD DATA INFILE 完整导入参数、权限与常见中断CSV准备好了最顺的导入方式就是 MySQL 的 LOAD DATA。完整语句LOAD DATA INFILE /opt/bin_data/bin_info_2025q3.csv INTO TABLE pay_base.bin_info CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version) SET update_time NOW();参数逐个解释CHARACTER SET utf8mb4必须和CSV实际编码一致文件是UTF-8就写utf8mb4不小心写成latin1银行名会出现乱码且不容易察觉FIELDS TERMINATED BY ,是列分隔符如果你CSV用的是分号就得改OPTIONALLY ENCLOSED BY 处理带引号的字段银行名称里有逗号时靠它保命IGNORE 1 LINES跳过CSV表头最后列清单要和CSV列顺序完全一致顺序不对数据错位比导入失败更可怕因为库里看起来是满的实际匹配全错。权限问题是最常见的拦路虎。MySQL 8 默认开了secure-file-priv服务端 LOAD DATA 只能读指定目录的数据文件可以先查一下再决定文件放哪SHOW VARIABLES LIKE secure_file_priv;结果是目录就把CSV放进去结果是空值就无限制。如果你没有服务端文件系统的访问权限就改用LOAD DATA LOCAL INFILE它走客户端上传但需要客户端连接时加--local-infile1参数MySQL 8 默认这个开关是关的。生产环境里我优先用服务端版本LOCAL版本在跨机房、大文件场景下速度差不少。3.3 导入核对与增量更新stage表加ON DUPLICATE KEY UPDATE直接LOAD DATA进正式表有个风险文件里有一行主键冲突整个导入就中断前面导入的行全部留下库变成半新半旧。我从不直接LOAD正式表而是先建一张一模一样的stage表导到stage里核对无误后再合并进正式表。CREATE TABLE pay_base.bin_info_stage LIKE pay_base.bin_info; LOAD DATA INFILE /opt/bin_data/bin_info_2025q3.csv INTO TABLE pay_base.bin_info_stage CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version) SET update_time NOW();合并更新用 INSERT ... ON DUPLICATE KEY UPDATEINSERT INTO pay_base.bin_info (bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version, update_time) SELECT bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version, NOW() FROM pay_base.bin_info_stage ON DUPLICATE KEY UPDATE card_brand VALUES(card_brand), card_type VALUES(card_type), bank_name VALUES(bank_name), bank_code VALUES(bank_code), card_level VALUES(card_level), country_code VALUES(country_code), is_valid VALUES(is_valid), source_version VALUES(source_version), update_time NOW();这样做的价值在于如果 stage 表导入时报错正式表完全不受影响修好文件重跑就行合并阶段即使有冲突也是按版本直接覆盖不会中断。导完后跑三句核对SQL确认行数和版本分布正常SELECT COUNT(*) FROM pay_base.bin_info; SELECT source_version, COUNT(*) FROM pay_base.bin_info GROUP BY source_version; SELECT card_brand, card_type, COUNT(*) FROM pay_base.bin_info GROUP BY card_brand, card_type;第一句看总行数是否和源文件一致第二句确认库里到底混了几个版本第三句快速判断卡组织分布是否符合预期。如果总行数对不上大概率是CSV里本身有重复行或者stage表没清空。4. 导入PostgreSQLCOPY、upsert 与两库同步4.1 PG建表与COPY权限模型和MySQL差在哪PostgreSQL这边结构大同小异但权限模型完全是另一套逻辑。建表语句CREATE TABLE pay_base.bin_info ( bin_number VARCHAR(16) PRIMARY KEY, card_brand VARCHAR(32) NOT NULL, card_type VARCHAR(16) NOT NULL, bank_name VARCHAR(64) NOT NULL, bank_code VARCHAR(16), card_level VARCHAR(32), country_code VARCHAR(8), is_valid BOOLEAN NOT NULL DEFAULT TRUE, source_version VARCHAR(32) NOT NULL, update_time TIMESTAMPTZ NOT NULL DEFAULT NOW() );对比MySQL版本差异点不需要 ENGINE 和 COLLATEPG默认就是事务性表BOOLEAN 直接用 bool 类型TIMESTAMPTZ 自带时区写入时自动转UTC存储查询时按会话时区返回。核心差异在导入权限上。PG服务端COPY命令读的是数据库服务器上的文件普通用户没有pg_read_server_files权限会直接报 permission denied。如果你只是临时导入用\copy更省事它是psql客户端命令读的是客户端本机文件不需要服务端文件权限-- psql内执行读客户端本地文件 \copy pay_base.bin_info FROM /opt/bin_data/bin_info_2025q3.csv WITH (FORMAT csv, HEADER true, DELIMITER ,, ENCODING UTF8);服务端版本长这样适合把文件放在数据库服务器上的批量场景COPY pay_base.bin_info FROM /opt/bin_data/bin_info_2025q3.csv WITH (FORMAT csv, HEADER true, DELIMITER ,, ENCODING UTF8);两者性能差异不算大几十万行都是秒级完成。区别在于\copy 适合开发机、临时环境随手用COPY 适合自动化脚本里稳定跑但前提是数据库账号有权限。数据库账号是业务应用自己用的别给它开pg_read_server_files建议单独建一个数据维护账号专门用来跑这类导入任务。4.2 用临时表和ON CONFLICT做PG版幂等更新PG没有 MySQLON DUPLICATE KEY UPDATE但它的INSERT ... ON CONFLICT更灵活可以直接写条件。我的标准操作是建stage表、灌数据、合并、删stage。CREATE TABLE pay_base.bin_info_stage (LIKE pay_base.bin_info INCLUDING DEFAULTS); \copy pay_base.bin_info_stage FROM /opt/bin_data/bin_info_2025q3.csv WITH (FORMAT csv, HEADER true, DELIMITER ,, ENCODING UTF8);合并语句INSERT INTO pay_base.bin_info AS b (bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version, update_time) SELECT s.bin_number, s.card_brand, s.card_type, s.bank_name, s.bank_code, s.card_level, s.country_code, s.is_valid, s.source_version, NOW() FROM pay_base.bin_info_stage s ON CONFLICT (bin_number) DO UPDATE SET card_brand EXCLUDED.card_brand, card_type EXCLUDED.card_type, bank_name EXCLUDED.bank_name, bank_code EXCLUDED.bank_code, card_level EXCLUDED.card_level, country_code EXCLUDED.country_code, is_valid EXCLUDED.is_valid, source_version EXCLUDED.source_version, update_time NOW() WHERE b.source_version IS DISTINCT FROM EXCLUDED.source_version;关键在最后这个 WHERE 子句只有新版本和库里已有版本不一致时才更新 update_time。不加的话每次全量重跑都会把所有行的 update_time 刷一遍后面排查「这条记录到底什么时候更新的」就失去意义了。这一点和MySQL不同MySQL的ON DUPLICATE KEY UPDATE没有这种条件能力只能在SET里写update_time IF(VALUES(source_version) source_version, NOW(), update_time)来实现类似效果但可读性差很多。合并完成后删掉stage表避免下次误用旧数据DROP TABLE pay_base.bin_info_stage;4.3 双库并行维护同一份CSV怎么同时喂给MySQL和PG实际环境里MySQL和PG通常不是二选一而是并存业务库在MySQL数据分析在PG或者不同机房各用一套。BIN号数据是同一份两边都要保持最新不然同一个卡号在不同库查出来的发卡行不一样审计都说不清。我的做法是让CSV成为唯一中间产物一份文件喂两个库。先跑Python脚本从Excel生成标准CSV然后分别执行MySQL的 stageLOAD 和 PG 的 stage\copy 两条链路。不要试图用SQLAlchemy的to_sql一把梭几十万行还能忍上百万行明显变慢而且它定义字段类型时容易和你手工建的表结构不一致后续踩坑成本远高于省下的那点代码量。双库同步最大的风险不是导入本身而是两边导入完成后没人校验。我固定会在两个库各跑一条相同逻辑的汇总SQL人工比一下数字SELECT source_version, COUNT(*) FROM pay_base.bin_info GROUP BY source_version ORDER BY source_version;MySQL和PG都可以执行这句PG把反引号去掉。输出的数字一致才认为这次更新闭环了。如果两边数字不一致优先检查是不是有一边stage表没清空导致重复导入。5. BIN号数据维护避坑实录长度识别、版本混用和编码翻车5.1 坑一BIN识别码不止6位按6位匹配会漏掉新卡现象某张卡走VISA渠道本地BIN表里按卡号前6位查得到业务系统却不认日志显示匹配记录的发卡行不对。原因卡组织标准里识别码并不总固定在6位近年的发卡方案已经出现7位、8位识别码银联卡也有一部分按7位分配只存6位等于把一批新卡全切掉了。解决表结构里 bin_number 按 VARCHAR(16) 存匹配时按卡号前8、7、6位逐级往下试取最长命中而不是只截前6位。这条规则对所有库都适用和数据表本身无关属于业务代码层必须做的调整。5.2 坑二新旧版本混用上半月用新表下半月用旧表现象同一个月里一批交易按新发卡行识别另一批交易查出来是旧银行风控规则对同一张卡的判断结果前后不一致工单来回踢。原因两个环境的BIN表更新步骤不同步或者有人手动往生产表里刷了一个旧CSV把新版本覆盖了。解决source_version 字段必须在每次导入时强制写入并在合并阶段用版本号做比较只允许新版本覆盖旧版本更新任务固定写成脚本MySQL和PG同一套逻辑分别跑、分别校验杜绝手工SQL。另外建议在正式表上加一层视图业务查询走视图表结构怎么改都不影响上层应用。5.3 坑三Excel科学计数法毁掉一整列卡号现象从Excel导出的CSV里卡号列变成6.22E18导入后所有长卡号尾数全是0匹配永远失败。原因Excel默认把超过11位的数字转成科学计数法数字精度在17位左右就开始丢16位银行卡号正好踩在危险区。解决源数据里所有卡号、BIN号列在Excel里全部设为文本格式再处理用Python读Excel时dtype 必须显式指定为 str导入前抽查CSV里是否有E字符。这个坑看起来低级但几乎每换一个数据源就会复发一次必须在流程里固定检查项。5.4 坑四LOAD DATA一行报错前面导入的行全留下现象LOAD DATA执行到一半报ERROR 1062: Duplicate entry然后整个语句终止但库里的数据已经是半新半旧状态。原因CSV里有重复BIN号而目标表主键是 bin_number默认行为是直接报错并停止。解决不要直接LOAD正式表先导stage表stage表导入时加IGNORE关键字跳过冲突行LOAD DATA INFILE /opt/bin_data/bin_info_2025q3.csv IGNORE INTO TABLE pay_base.bin_info_stage ...然后对stage表做一次分组统计找出重复来源决定保留哪条再合并正式表。这样任何异常都发生在stage层正式表永远只有完整的新版本或完整的旧版本不会有中间态。5.5 坑五中文银行名乱码源头在CSV编码不在SQL现象PG查 bank_name 显示æµ·å¾...之类的乱码MySQL那边同一个CSV却是正常中文。原因CSV文件本身是GBK或GB2312编码MySQL的CHARACTER SET utf8mb4跟实际编码不匹配时往往直接报错但PG的COPY在某些环境下不报错硬把GBK字节按UTF8解码就存入了乱码。解决所有CSV统一从源头转成UTF-8我的习惯是用Python转不用Excel另存因为转换完用file命令或者直接head -c 100 bin.csv看一眼也能确认导入语句里编码参数固定写 UTF8两个库都统一乱码问题基本消失。6. 把BIN号表变成业务能力匹配、缓存与自动更新6.1 前缀匹配算法识别码长度不齐时按最长优先表建好、数据进库只是第一步真正让BIN号发挥价值的是业务代码里的匹配逻辑。最稳的前缀匹配是贪心算法先从卡号最长的8位开始查查不到就试7位再试6位def match_bin(card_no: str, cache: dict) - dict: for n in (8, 7, 6): key card_no[:n] info cache.get(key) if info: return info return None这段代码的前提是缓存里有按 bin_number 为key的记录。注意不要去数据库里执行WHERE 6228481234567890 LIKE CONCAT(bin_number, %)这个写法在MySQL和PG里都会让索引失效全表扫描一次几万行还能扛并发一上来就雪崩。正确姿势是上面这种应用层截位、字典查询、逐级降位。识别码长度这块银联老卡基本6位能命中新发卡方案和VISA、MC的8位识别码靠这个循环就能兜住。6.2 缓存与自动更新链路BIN号全量数据量其实很小几十万行撑死几十MB完全可以全量加载到应用内存里。启动时从数据库查出所有is_valid 1的记录构建成字典日常查询走内存命中率极高数据库端几乎零压力。更新时不需要重启服务加载完新表后标记一个版本号下次查询用新的dict旧dict等正在处理的请求结束后再释放。自动更新链路我固定成三条命令从源头下载新CSV、分别灌入MySQL和PG的stage表、合并并校验版本计数。全程不手工操作避免哪次忘了更新测试环境。这一套跑顺之后BIN号数据就不再是「偶尔手动更一次」的运维杂活而是和业务系统同步演进的底层能力发卡行一换交易链路立刻感知。过来人的教训是早期我只更生产库、漏了测试库某次联调用新卡测试上游识别到了新发卡行测试环境却一查一个空双方互相甩锅排查了半天最后发现是两边BIN表版本不一致。那之后所有环境强制同一套更新脚本、同一个source_version校验不过不上线。希望帮到你。本文还有配套的精品资源点击获取
简介2020年4月版银行卡BIN号规范数据以压缩包形式发布面向金融风控、支付结算、数据分析等需要识别发卡行与卡片类型的从业者。数据覆盖信用卡、借记卡、农民工卡、跨行转账卡、非标卡及单位结算卡等分类可用于本地建表、关联查询、反欺诈识别和客户画像。压缩包共7个文件含5个Excel表格和2个SQL脚本Excel便于人工排序筛选与分析核对SQL脚本可直接导入MySQL或PostgreSQL快速生成bin_number、bank_name、card_type等字段的标准化表整体仅1.4MB。已有856人学习下载在银行卡归属行识别与交易校验场景中具备参考价值。使用时可依据银行卡前6位BIN快速匹配发卡机构也可借助SQL完成定期更新维护为交易安全校验和风险管理提供基础数据支撑。1. 最新银行卡BIN号数据是什么先搞清楚你缺哪块做支付对账的人最怕碰到这种场景一笔交易拦在风控规则里工单转过来查了半天发现是发卡行识别不出来本地库里根本没有这张卡的BIN记录。多数时候不是没数据而是本地那张BIN号表还是两个季度前的快照新发卡行、新卡种全都没跟上。这里说的BIN号就是银行卡号前6到8位识别码它决定这张卡属于哪个卡组织、由哪家银行发出、是借记卡还是贷记卡。标题里的「最新银行卡bin号 excel mysql pg」拆开看就是一条完整链路把整理好的BIN号清单从Excel出发分别灌进MySQL和PostgreSQL让交易和风控系统用得上。这篇文章按我实际推进的顺序来写适合正在做支付接入、交易路由、风控规则维护、以及要维护多套数据库环境的同学。2. 把BIN号整理成Excel清单字段设计、去重与CSV导出2.1 先定字段一张BIN号表至少要有这9列很多从公开渠道抓回来的BIN数据原始格式往往是网页表格或者文本文件字段乱且名称不统一。直接进库之前第一步是把它整理成一张标准Excel清单。我这里有一套固定字段跑过多次数据迁移按这套来两个数据库端到端不会出大乱子。字段名类型建议必填说明bin_numberVARCHAR(16)必填银行卡识别码6到8位数字一律按文本处理card_brandVARCHAR(32)必填卡组织枚举VISA/MASTERCARD/UNIONPAY/AMEX等card_typeVARCHAR(16)必填借记卡/贷记卡/准贷记卡bank_nameVARCHAR(64)必填发卡行名称bank_codeVARCHAR(16)选填发卡行行号或联行号card_levelVARCHAR(32)选填卡等级普卡/金卡/白金/钻石country_codeVARCHAR(8)选填国家或地区ISO代码source_versionVARCHAR(32)必填数据源版本标记比如2025Q3update_timeDATETIME必填这条记录生效的时间有几个字段的选择理由必须说清楚。bin_number用 VARCHAR 而不是数字因为BIN号不参与加减乘除只做前缀匹配存成数字纯属给自己加戏还容易丢前导零。bank_name 和 bank_code 要分开银行改名、合并是常事代码不变名称变拆开之后对账还能靠 code 关联。source_version 这一列最容易被人忽略但它是后面排查「新旧版本混用」的唯一依据没有它出了问题只能认栽。card_brand 和 card_type 是两个维度同一个卡组织既有借记卡也有贷记卡别混成一列。2.2 去重与变更处理同一BIN号出现在两个版本里怎么办整理Excel时最常遇到的就是重复。同一张卡上季度版本里有记录新版本里也有bin_number 完全一样但 bank_name 或者 card_level 可能变了。直接删会造成信息丢失不删进库又会主键冲突。先用公式把重复项标记出来这一步最直观COUNTIF(A:A, A2)1逻辑很简单筛选结果为 TRUE 的行就是这个BIN号在整表里出现了不止一次。公式好用在于你能先看到重复记录各自带了什么字段再判断是直接删还是更新。如果是纯重复每列都一样保留一行即可如果是同一BIN号在不同版本里银行归属变了那不是脏数据而是BIN回收、变更发卡行的正常情况处理原则是以 source_version 较新的为准旧记录不硬删归档到一张历史表里留底。清理完重复还要顺手处理几类脏数据全角空格和不可见字符用 TRIM 和 CLEAN 函数过一遍银行名称里「股份有限公司」「有限责任公司」这类后缀有的源带、有的不带最好统一口径括号和引号都换成半角不然后面CSV转义会坑到你。2.3 导出CSV而不是直接贴库三个必须检查的设置Excel整理好后下一步不是直接连数据库贴而是先另存为CSV。直接贴库是新手才会干的事临时测一下可以正式环境没人敢让Excel直连数据库。导出CSV前请务必检查三个设置缺一个后面就翻车。第一个是强制文本格式。选中所有数据列单元格格式设为「文本」尤其是 bin_number 这一列。Excel会在某一刻悄悄把看起来像数字的内容转成科学计数法6到8位的BIN当前不会溢出但表里如果还带了示例卡号或者完整卡号列16位以上数字就会变成 6.22E18这列数据就算废了。第二个是编码。Excel自带「CSV UTF-8」另存时通常带BOMMySQL的 LOAD DATA 读它第一列会多出一个看不见的\ufeff导致首个BIN号永远匹配不上。我一般不用Excel直接存而是用Python读一遍Excel再统一输出CSV控制编码和换行符import pandas as pd df pd.read_excel(bin_source.xlsx, dtype{bin_number: str, bank_code: str}) df df.drop_duplicates(subsetbin_number, keeplast) df[is_valid] df.get(is_valid, 1) df df[[bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version]] df.to_csv(bin_info_2025q3.csv, indexFalse, encodingutf-8)这里 dtype 参数强制把 bin_number 读成字符串避免pandas自动推断成int64列顺序固定是为了后面 MySQL 和 PG 的导入语句都不用再调列映射。第三个是换行符。pandas默认输出\nExcel另存是\r\n写数据库导入语句时要知道自己文件是什么换行MySQL端LINES TERMINATED BY写错轻则多出一列重则整文件导入失败。3. 导入MySQL建表、LOAD DATA 与临时表合并3.1 建表BIN号为什么用VARCHAR当主键MySQL这边我一般先建一个独立库避免和业务表混在一起。建表语句如下CREATE DATABASE IF NOT EXISTS pay_base DEFAULT CHARSET utf8mb4; CREATE TABLE pay_base.bin_info ( bin_number VARCHAR(16) NOT NULL COMMENT 银行卡识别码6到8位按文本处理, card_brand VARCHAR(32) NOT NULL COMMENT 卡组织枚举VISA/MASTERCARD/UNIONPAY/AMEX, card_type VARCHAR(16) NOT NULL COMMENT 借记卡/贷记卡/准贷记卡, bank_name VARCHAR(64) NOT NULL COMMENT 发卡行名称, bank_code VARCHAR(16) DEFAULT NULL COMMENT 发卡行行号, card_level VARCHAR(32) DEFAULT NULL COMMENT 卡等级, country_code VARCHAR(8) DEFAULT NULL COMMENT ISO国家代码, is_valid TINYINT NOT NULL DEFAULT 1 COMMENT 是否在用1在用 0已废弃, source_version VARCHAR(32) NOT NULL COMMENT 数据源版本标记, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (bin_number), KEY idx_brand (card_brand) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci;几个设计点说明一下。bin_number 做主键是因为业务查询永远是通过卡号前缀去精确命中的主键直接覆盖不需要多余回表。VARCHAR(16) 不是拍脑袋识别码目前最长8位留一倍余量足够太长反而浪费索引空间。is_valid 字段用来标记废弃BIN而不是直接删行因为历史流水可能还挂在旧BIN上删了就查不到对应关系。card_brand 上建普通索引是为了后面做统计报表不建也能跑但分组统计会全表扫几万行无所谓几十万行就有感知了。如果是接已有的库注意原表可能已经有 bin_number 列且不是主键这时候别直接改表结构先导出一份旧数据备份再扩列加主键避免在线上生产表上做高风险DDL。3.2 LOAD DATA INFILE 完整导入参数、权限与常见中断CSV准备好了最顺的导入方式就是 MySQL 的 LOAD DATA。完整语句LOAD DATA INFILE /opt/bin_data/bin_info_2025q3.csv INTO TABLE pay_base.bin_info CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version) SET update_time NOW();参数逐个解释CHARACTER SET utf8mb4必须和CSV实际编码一致文件是UTF-8就写utf8mb4不小心写成latin1银行名会出现乱码且不容易察觉FIELDS TERMINATED BY ,是列分隔符如果你CSV用的是分号就得改OPTIONALLY ENCLOSED BY 处理带引号的字段银行名称里有逗号时靠它保命IGNORE 1 LINES跳过CSV表头最后列清单要和CSV列顺序完全一致顺序不对数据错位比导入失败更可怕因为库里看起来是满的实际匹配全错。权限问题是最常见的拦路虎。MySQL 8 默认开了secure-file-priv服务端 LOAD DATA 只能读指定目录的数据文件可以先查一下再决定文件放哪SHOW VARIABLES LIKE secure_file_priv;结果是目录就把CSV放进去结果是空值就无限制。如果你没有服务端文件系统的访问权限就改用LOAD DATA LOCAL INFILE它走客户端上传但需要客户端连接时加--local-infile1参数MySQL 8 默认这个开关是关的。生产环境里我优先用服务端版本LOCAL版本在跨机房、大文件场景下速度差不少。3.3 导入核对与增量更新stage表加ON DUPLICATE KEY UPDATE直接LOAD DATA进正式表有个风险文件里有一行主键冲突整个导入就中断前面导入的行全部留下库变成半新半旧。我从不直接LOAD正式表而是先建一张一模一样的stage表导到stage里核对无误后再合并进正式表。CREATE TABLE pay_base.bin_info_stage LIKE pay_base.bin_info; LOAD DATA INFILE /opt/bin_data/bin_info_2025q3.csv INTO TABLE pay_base.bin_info_stage CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version) SET update_time NOW();合并更新用 INSERT ... ON DUPLICATE KEY UPDATEINSERT INTO pay_base.bin_info (bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version, update_time) SELECT bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version, NOW() FROM pay_base.bin_info_stage ON DUPLICATE KEY UPDATE card_brand VALUES(card_brand), card_type VALUES(card_type), bank_name VALUES(bank_name), bank_code VALUES(bank_code), card_level VALUES(card_level), country_code VALUES(country_code), is_valid VALUES(is_valid), source_version VALUES(source_version), update_time NOW();这样做的价值在于如果 stage 表导入时报错正式表完全不受影响修好文件重跑就行合并阶段即使有冲突也是按版本直接覆盖不会中断。导完后跑三句核对SQL确认行数和版本分布正常SELECT COUNT(*) FROM pay_base.bin_info; SELECT source_version, COUNT(*) FROM pay_base.bin_info GROUP BY source_version; SELECT card_brand, card_type, COUNT(*) FROM pay_base.bin_info GROUP BY card_brand, card_type;第一句看总行数是否和源文件一致第二句确认库里到底混了几个版本第三句快速判断卡组织分布是否符合预期。如果总行数对不上大概率是CSV里本身有重复行或者stage表没清空。4. 导入PostgreSQLCOPY、upsert 与两库同步4.1 PG建表与COPY权限模型和MySQL差在哪PostgreSQL这边结构大同小异但权限模型完全是另一套逻辑。建表语句CREATE TABLE pay_base.bin_info ( bin_number VARCHAR(16) PRIMARY KEY, card_brand VARCHAR(32) NOT NULL, card_type VARCHAR(16) NOT NULL, bank_name VARCHAR(64) NOT NULL, bank_code VARCHAR(16), card_level VARCHAR(32), country_code VARCHAR(8), is_valid BOOLEAN NOT NULL DEFAULT TRUE, source_version VARCHAR(32) NOT NULL, update_time TIMESTAMPTZ NOT NULL DEFAULT NOW() );对比MySQL版本差异点不需要 ENGINE 和 COLLATEPG默认就是事务性表BOOLEAN 直接用 bool 类型TIMESTAMPTZ 自带时区写入时自动转UTC存储查询时按会话时区返回。核心差异在导入权限上。PG服务端COPY命令读的是数据库服务器上的文件普通用户没有pg_read_server_files权限会直接报 permission denied。如果你只是临时导入用\copy更省事它是psql客户端命令读的是客户端本机文件不需要服务端文件权限-- psql内执行读客户端本地文件 \copy pay_base.bin_info FROM /opt/bin_data/bin_info_2025q3.csv WITH (FORMAT csv, HEADER true, DELIMITER ,, ENCODING UTF8);服务端版本长这样适合把文件放在数据库服务器上的批量场景COPY pay_base.bin_info FROM /opt/bin_data/bin_info_2025q3.csv WITH (FORMAT csv, HEADER true, DELIMITER ,, ENCODING UTF8);两者性能差异不算大几十万行都是秒级完成。区别在于\copy 适合开发机、临时环境随手用COPY 适合自动化脚本里稳定跑但前提是数据库账号有权限。数据库账号是业务应用自己用的别给它开pg_read_server_files建议单独建一个数据维护账号专门用来跑这类导入任务。4.2 用临时表和ON CONFLICT做PG版幂等更新PG没有 MySQLON DUPLICATE KEY UPDATE但它的INSERT ... ON CONFLICT更灵活可以直接写条件。我的标准操作是建stage表、灌数据、合并、删stage。CREATE TABLE pay_base.bin_info_stage (LIKE pay_base.bin_info INCLUDING DEFAULTS); \copy pay_base.bin_info_stage FROM /opt/bin_data/bin_info_2025q3.csv WITH (FORMAT csv, HEADER true, DELIMITER ,, ENCODING UTF8);合并语句INSERT INTO pay_base.bin_info AS b (bin_number, card_brand, card_type, bank_name, bank_code, card_level, country_code, is_valid, source_version, update_time) SELECT s.bin_number, s.card_brand, s.card_type, s.bank_name, s.bank_code, s.card_level, s.country_code, s.is_valid, s.source_version, NOW() FROM pay_base.bin_info_stage s ON CONFLICT (bin_number) DO UPDATE SET card_brand EXCLUDED.card_brand, card_type EXCLUDED.card_type, bank_name EXCLUDED.bank_name, bank_code EXCLUDED.bank_code, card_level EXCLUDED.card_level, country_code EXCLUDED.country_code, is_valid EXCLUDED.is_valid, source_version EXCLUDED.source_version, update_time NOW() WHERE b.source_version IS DISTINCT FROM EXCLUDED.source_version;关键在最后这个 WHERE 子句只有新版本和库里已有版本不一致时才更新 update_time。不加的话每次全量重跑都会把所有行的 update_time 刷一遍后面排查「这条记录到底什么时候更新的」就失去意义了。这一点和MySQL不同MySQL的ON DUPLICATE KEY UPDATE没有这种条件能力只能在SET里写update_time IF(VALUES(source_version) source_version, NOW(), update_time)来实现类似效果但可读性差很多。合并完成后删掉stage表避免下次误用旧数据DROP TABLE pay_base.bin_info_stage;4.3 双库并行维护同一份CSV怎么同时喂给MySQL和PG实际环境里MySQL和PG通常不是二选一而是并存业务库在MySQL数据分析在PG或者不同机房各用一套。BIN号数据是同一份两边都要保持最新不然同一个卡号在不同库查出来的发卡行不一样审计都说不清。我的做法是让CSV成为唯一中间产物一份文件喂两个库。先跑Python脚本从Excel生成标准CSV然后分别执行MySQL的 stageLOAD 和 PG 的 stage\copy 两条链路。不要试图用SQLAlchemy的to_sql一把梭几十万行还能忍上百万行明显变慢而且它定义字段类型时容易和你手工建的表结构不一致后续踩坑成本远高于省下的那点代码量。双库同步最大的风险不是导入本身而是两边导入完成后没人校验。我固定会在两个库各跑一条相同逻辑的汇总SQL人工比一下数字SELECT source_version, COUNT(*) FROM pay_base.bin_info GROUP BY source_version ORDER BY source_version;MySQL和PG都可以执行这句PG把反引号去掉。输出的数字一致才认为这次更新闭环了。如果两边数字不一致优先检查是不是有一边stage表没清空导致重复导入。5. BIN号数据维护避坑实录长度识别、版本混用和编码翻车5.1 坑一BIN识别码不止6位按6位匹配会漏掉新卡现象某张卡走VISA渠道本地BIN表里按卡号前6位查得到业务系统却不认日志显示匹配记录的发卡行不对。原因卡组织标准里识别码并不总固定在6位近年的发卡方案已经出现7位、8位识别码银联卡也有一部分按7位分配只存6位等于把一批新卡全切掉了。解决表结构里 bin_number 按 VARCHAR(16) 存匹配时按卡号前8、7、6位逐级往下试取最长命中而不是只截前6位。这条规则对所有库都适用和数据表本身无关属于业务代码层必须做的调整。5.2 坑二新旧版本混用上半月用新表下半月用旧表现象同一个月里一批交易按新发卡行识别另一批交易查出来是旧银行风控规则对同一张卡的判断结果前后不一致工单来回踢。原因两个环境的BIN表更新步骤不同步或者有人手动往生产表里刷了一个旧CSV把新版本覆盖了。解决source_version 字段必须在每次导入时强制写入并在合并阶段用版本号做比较只允许新版本覆盖旧版本更新任务固定写成脚本MySQL和PG同一套逻辑分别跑、分别校验杜绝手工SQL。另外建议在正式表上加一层视图业务查询走视图表结构怎么改都不影响上层应用。5.3 坑三Excel科学计数法毁掉一整列卡号现象从Excel导出的CSV里卡号列变成6.22E18导入后所有长卡号尾数全是0匹配永远失败。原因Excel默认把超过11位的数字转成科学计数法数字精度在17位左右就开始丢16位银行卡号正好踩在危险区。解决源数据里所有卡号、BIN号列在Excel里全部设为文本格式再处理用Python读Excel时dtype 必须显式指定为 str导入前抽查CSV里是否有E字符。这个坑看起来低级但几乎每换一个数据源就会复发一次必须在流程里固定检查项。5.4 坑四LOAD DATA一行报错前面导入的行全留下现象LOAD DATA执行到一半报ERROR 1062: Duplicate entry然后整个语句终止但库里的数据已经是半新半旧状态。原因CSV里有重复BIN号而目标表主键是 bin_number默认行为是直接报错并停止。解决不要直接LOAD正式表先导stage表stage表导入时加IGNORE关键字跳过冲突行LOAD DATA INFILE /opt/bin_data/bin_info_2025q3.csv IGNORE INTO TABLE pay_base.bin_info_stage ...然后对stage表做一次分组统计找出重复来源决定保留哪条再合并正式表。这样任何异常都发生在stage层正式表永远只有完整的新版本或完整的旧版本不会有中间态。5.5 坑五中文银行名乱码源头在CSV编码不在SQL现象PG查 bank_name 显示æµ·å¾...之类的乱码MySQL那边同一个CSV却是正常中文。原因CSV文件本身是GBK或GB2312编码MySQL的CHARACTER SET utf8mb4跟实际编码不匹配时往往直接报错但PG的COPY在某些环境下不报错硬把GBK字节按UTF8解码就存入了乱码。解决所有CSV统一从源头转成UTF-8我的习惯是用Python转不用Excel另存因为转换完用file命令或者直接head -c 100 bin.csv看一眼也能确认导入语句里编码参数固定写 UTF8两个库都统一乱码问题基本消失。6. 把BIN号表变成业务能力匹配、缓存与自动更新6.1 前缀匹配算法识别码长度不齐时按最长优先表建好、数据进库只是第一步真正让BIN号发挥价值的是业务代码里的匹配逻辑。最稳的前缀匹配是贪心算法先从卡号最长的8位开始查查不到就试7位再试6位def match_bin(card_no: str, cache: dict) - dict: for n in (8, 7, 6): key card_no[:n] info cache.get(key) if info: return info return None这段代码的前提是缓存里有按 bin_number 为key的记录。注意不要去数据库里执行WHERE 6228481234567890 LIKE CONCAT(bin_number, %)这个写法在MySQL和PG里都会让索引失效全表扫描一次几万行还能扛并发一上来就雪崩。正确姿势是上面这种应用层截位、字典查询、逐级降位。识别码长度这块银联老卡基本6位能命中新发卡方案和VISA、MC的8位识别码靠这个循环就能兜住。6.2 缓存与自动更新链路BIN号全量数据量其实很小几十万行撑死几十MB完全可以全量加载到应用内存里。启动时从数据库查出所有is_valid 1的记录构建成字典日常查询走内存命中率极高数据库端几乎零压力。更新时不需要重启服务加载完新表后标记一个版本号下次查询用新的dict旧dict等正在处理的请求结束后再释放。自动更新链路我固定成三条命令从源头下载新CSV、分别灌入MySQL和PG的stage表、合并并校验版本计数。全程不手工操作避免哪次忘了更新测试环境。这一套跑顺之后BIN号数据就不再是「偶尔手动更一次」的运维杂活而是和业务系统同步演进的底层能力发卡行一换交易链路立刻感知。过来人的教训是早期我只更生产库、漏了测试库某次联调用新卡测试上游识别到了新发卡行测试环境却一查一个空双方互相甩锅排查了半天最后发现是两边BIN表版本不一致。那之后所有环境强制同一套更新脚本、同一个source_version校验不过不上线。希望帮到你。本文还有配套的精品资源点击获取
