简介最新银行卡 BIN 号数据包涵盖农民工卡、跨行转账卡、非标卡、单位结算卡及卡表总信息等多类卡种面向银行、支付机构、金融风控与数据分析人员用于快速识别发卡行、卡片类型及归属行校验。包内共七个文件含五个 xls 表格和两个 sql 脚本压缩包仅 1.4MBxls 便于在 Excel 中排序筛选和人工比对sql 文件则可直接导入 MySQL、PostgreSQL建立结构化数据表进行批量查询、反欺诈验证与风险监测。资源以 2020 年 4 月 25 日为版本节点整理了 BIN 号核心表与银行编号映射便于定期更新维护也适合作为账户交易、清结算等场景的基础数据层降低手工匹配成本。已有 852 人学习文件体积小、格式明确适合熟悉 Excel 或数据库操作的数据人员快速上手。1. 项目思路拆解为什么选这三件套最近接了个挺典型的内部数据需求把一份最新的银行卡BIN号清单做成一套可查询、可更新、可跨库使用的数据资产。需求文档写得简单粗暴——Excel一份、MySQL一份、PostgreSQL也来一份。但真上手做的时候才发现这个简单需求背后牵扯到数据清洗、字符集、类型映射、迁移工具选型一堆事整条链路跑通花了两个晚上踩了不少坑。先说说BIN号是什么。BIN是Bank Identification Number的缩写指的是银行卡卡号的前6位用来标识发卡机构、卡组织银联、Visa、Mastercard等、卡种借记卡、贷记卡、预付费卡以及卡等级普卡、金卡、白金卡等。在支付风控、交易路由、商户结算、营销画像这些场景里BIN号几乎是必查的基础数据。比如你在支付系统里收到一笔交易看到卡号前6位是622848立刻就能判断这是农行的借记卡然后决定走哪条清算通道、要不要触发额外的风控校验。这个项目适合谁参考一类是做支付、金融风控、数据分析的工程师另一类是日常要和Excel、SQL数据库打交道的运营或产品同学。前者关注跨库迁移和查询性能后者关注Excel清洗和批量导入的技巧。两条线我都会讲到你可以按需跳着看。整条技术路线我最终定的是Excel做源数据整理和字段规范化生成标准化的CSV或直接拼接SQL脚本MySQL作为第一落库目标建表、导入、索引一步到位PostgreSQL作为第二目标库重点解决数据类型差异和迁移过程中的数据一致性校验。三个环节各有各的坑下面逐个拆开讲。2. Excel端数据清洗决定了后面所有环节的成败2.1 字段设计一张标准BIN号表该有哪些列拿到原始BIN号数据的时候通常是一堆零散的列甚至有些是从网页上直接复制的纯文本。我的习惯是先按最终业务查询需求倒推字段结构。实际项目中我最后用的表结构是这样的字段名类型Excel说明示例bin_number文本BIN号核心字段一律存文本622848card_org文本卡组织如银联、Visa、Mastercard银联card_type文本卡种借记卡/贷记卡/预付费卡借记卡card_level文本卡等级普卡/金卡/白金卡金卡bank_name文本发卡行全称中国农业银行bank_code文本发卡行联行号或内部编码103country_code文本国家或地区代码CNis_valid数字是否有效1有效/0失效1update_time日期数据更新时间2024-06-01字段设计的核心原则是面向查询设计而不是面向录入设计。BIN号数据最核心的查询场景就是给我这个卡号前6位告诉我它属于哪家银行、什么卡种所以bin_number、bank_name、card_type这三个字段必须放在最前面索引也基本围着它们转。card_org单独拆一列是因为不同卡组织的BIN号段规则差异很大后面做分渠道统计时会经常按这个维度group by。2.2 三个最容易翻车的Excel操作第一个坑是Excel科学计数法。BIN号是纯数字构成的文本如果单元格格式不是文本输入622848123456这种长串数字Excel会自动转成6.22848123456E11小数点后精度直接丢失。我见过有人拿这种数据导入数据库结果所有BIN号全都变成了622848123456000——这种脏数据在风控场景里是会出大事的。解决办法是选中整列先把单元格格式设置为文本再粘贴数据或者用分列功能第一步选择分隔符号第三步把列数据格式选成文本。第二个坑是千分符和隐藏空格。从某些报表系统导出的BIN号可能带着千分符比如622848或者Excel显示正常但单元格里有尾随空格这种数据在后续匹配时根本查不到。我用ABAP导数据时也遇到过类似问题Excel里看着有数字导入数据库后字段长度对不上。所以清洗阶段我会用Excel的查找替换把千分符里的逗号全部去掉再用TRIM公式处理空格。第三个坑是重复数据。同一个BIN号可能在不同来源的表中出现多次但更新时间不同需要按最新时间优先去重。Excel里去重用删除重复项功能就行但要注意选对列——只勾选bin_number这一列去重保留更新时间最新的那条记录。更稳妥的做法是用Power Query做分组排序取首行这样逻辑可复现下次数据更新时刷新一下就能重跑。2.3 用Excel批量拼接SQL的野路子如果你手头有几千条BIN号数据但不想折腾LOAD DATA这类导入方式或者目标库是Oracle这些用起来麻烦的可以借助Excel批量生成INSERT语句。方法不复杂在最后一列空白处输入这个公式INSERT INTO bin_info(bin_number, card_org, card_type, bank_name, bank_code, country_code, is_valid) VALUES(A2, B2, C2, E2, F2, G2, 1);向下拖动填充后把所有生成的行复制到一个SQL脚本文件里就能直接执行。这个办法的好处是不依赖导入工具缺点是量大时性能一般而且如果字段里有单引号会非常痛苦。我个人建议数据量在5000行以内可以用这个方案超过这个量直接走MySQL的LOAD DATA更干净。3. MySQL建表与批量导入一步到位的正确姿势3.1 建表SQL类型和索引不能凭感觉来MySQL这张表的设计我给出一个经过实测的方案CREATE TABLE bin_info ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bin_number CHAR(6) NOT NULL COMMENT BIN号前6位, card_org VARCHAR(32) NOT NULL DEFAULT COMMENT 卡组织, card_type VARCHAR(32) NOT NULL DEFAULT COMMENT 卡种, card_level VARCHAR(32) NOT NULL DEFAULT COMMENT 卡等级, bank_name VARCHAR(128) NOT NULL DEFAULT COMMENT 发卡行, bank_code VARCHAR(32) NOT NULL DEFAULT COMMENT 行号, country_code CHAR(2) NOT NULL DEFAULT CN COMMENT 国家代码, is_valid TINYINT NOT NULL DEFAULT 1 COMMENT 是否有效, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, UNIQUE KEY uk_bin (bin_number), KEY idx_bank (bank_name), KEY idx_card_org (card_org) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT银行卡BIN号表;两个容易忽略的细节说一下。第一bin_number用CHAR(6)而不是VARCHAR或者INT。BIN号固定6位CHAR(6)定长存储查询效率更高索引空间也更小。VARCHAR(6)虽然只多了1个字节的长度前缀但在几百万行数据量的表上索引大小差距会放大得很明显。更关键的是不能用INT存储BIN号——虽然BIN号确实是数字但它是编码不是数值如果BIN号以0开头用INT存储会把前导0丢掉。这个跟存身份证号一个道理能用定长字符串就别用数值类型。第二UNIQUE KEY加在bin_number上这是为了后续更新数据时可以直接用INSERT ... ON DUPLICATE KEY UPDATE做幂等写入避免重复导入把数据搞脏。业务查询通常按卡号前6位精确匹配这个唯一索引同时也覆盖了最核心的查询路径。3.2 LOAD DATA导入一条命令干完的活我把清洗好的Excel另存为CSV格式记得编码选UTF-8然后用MySQL的LOAD DATA命令批量导入几万行数据几秒钟就搞定LOAD DATA LOCAL INFILE /tmp/bin_data.csv INTO TABLE bin_info CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (bin_number, card_org, card_type, card_level, bank_name, bank_code, country_code, is_valid, update_time) ;注意命令末尾的字段列表是可选的但如果CSV的列顺序和表结构不完全一致就一定要显式写出。还有一个比较隐蔽的地方如果CSV里有空字符串LOAD DATA会把空串变成空字符串而不会自动转成NULL如果你的表字段有NOT NULL约束导入时就会报错。解决办法是在LOAD DATA语句里用变量接收字段值再用NULLIF函数转换LOAD DATA LOCAL INFILE /tmp/bin_data.csv INTO TABLE bin_info CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (bin_number, card_org, card_type, card_level, bank_name, bank_code, country_code, is_valid, update_time) SET card_level NULLIF(card_level, ), country_code NULLIF(country_code, CN);第一次用LOAD DATA的时候如果开启了MySQL的secure-file-priv限制会报The MySQL server is running with the --secure-file-priv option这时候两个选择要么把CSV文件放到secure-file-priv指定的目录下要么像我一样加上LOCAL关键字从客户端本地上传。3.3 导入后的三件事验证、抽样、记数导入不是跑完命令就完事。我每次导入完必做三件事。第一查总数和预期对得上不SELECT COUNT(*) FROM bin_info;第二抽样看几行关键字段重点看BIN号和卡组织有没有串位。SELECT bin_number, card_org, card_type, bank_name FROM bin_info ORDER BY id DESC LIMIT 10;第三验证唯一索引是否生效防止有重复BIN号混进来。SELECT bin_number, COUNT(*) AS cnt FROM bin_info GROUP BY bin_number HAVING cnt 1;导入过程中最常见的报错就两类。一类是Data too long for column通常是某一行的bank_name超长或者字段错位把长文本塞进了短字段排查技巧是先看CSV里对应列的最大长度然后逐个增加字段长度测试。另一类是Duplicate entry说明源数据里有重复BIN号需要回到Excel清洗阶段做一次去重。4. 从MySQL迁移到PostgreSQL类型差异和工具选型4.1 为什么要折腾到PG这一层有人会问MySQL都搞好了非要把数据再导到PostgreSQL这不是重复造轮子吗实际情况是同一个项目里MySQL往往是业务主库承担在线交易和实时查询而PostgreSQL经常是分析环境或GIS相关模块的底层存储。我的场景里PG库那边要跑一些复杂的地理聚合和JSON解析任务MySQL搞起来太费劲数据必须同步一份过去。MySQL和PG在功能上各有千秋但在把一份标准结构化数据迁移过去这件事上主要要解决两个问题一是数据类型映射二是数据一致性校验。搞定了这两点迁移就算成功了一大半。4.2 工具选型对比pgloader vs 手工导入导出我试过三种迁移路线各有适合的场景。最简单粗暴的是从MySQL导出CSV再在PG里用COPY命令导入。这种方式的优点是完全可控、不依赖额外工具缺点是表结构要手工在PG里建而且CSV中转容易踩字符集和转义符的坑。第二种是用pgloader这是一款专门做数据库迁移的工具一行命令就能把MySQL的表结构和数据搬到PGpgloader mysql://user:passwordlocalhost/bin_db postgresql://user:passwordlocalhost/bin_dbpgloader会自动做类型转换MySQL的TINYINT变成PG的SMALLINTVARCHAR基本原样保留DATETIME变成TIMESTAMPUNIQUE KEY和索引也会跟着建好。实测下来1万行以内小表迁移非常稳几百MB的大表也体验过只要网络没瓶颈基本不会中断。第三种是手工生成PG的INSERT语句适合不想装任何工具、表结构又有大改的场景。这个方案我只有在PG端表结构重设计的时候才会用因为拼接语句的脚本要自己写维护成本偏高。4.3 PG建表与MySQL的类型对照如果用pgloader就省事儿了它会自动建表。但如果你需要手动在PG端建表参考下面的对照关系基本不会出错MySQLPostgreSQL说明ENGINEInnoDB无对应选项PG默认就是MVCC机制CHARSETutf8mb4无需指定PG默认UTF-8BIGINT UNSIGNEDBIGINTPG没有无符号整型用CHECK约束替代TINYINTSMALLINTPG没有TINYINTDATETIMETIMESTAMP注意时区设置VARCHAR(n)VARCHAR(n)可直接对应CHAR(n)CHAR(n)可直接对应AUTO_INCREMENTSERIAL或IDENTITY自增语法不同手动建表的话PG端可以这样写CREATE TABLE bin_info ( id BIGSERIAL PRIMARY KEY, bin_number CHAR(6) NOT NULL, card_org VARCHAR(32) NOT NULL DEFAULT , card_type VARCHAR(32) NOT NULL DEFAULT , card_level VARCHAR(32) NOT NULL DEFAULT , bank_name VARCHAR(128) NOT NULL DEFAULT , bank_code VARCHAR(32) NOT NULL DEFAULT , country_code CHAR(2) NOT NULL DEFAULT CN, is_valid SMALLINT NOT NULL DEFAULT 1, update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uk_bin UNIQUE (bin_number) );PG里建议加一个CHECK约束保证BIN号格式正确ALTER TABLE bin_info ADD CONSTRAINT chk_bin_format CHECK (bin_number ~ ^[0-9]{6}$);这个约束在MySQL里要额外用触发器或者应用层判断PG内置的正则表达式支持直接就能干这也是数据质量管控上一个很实用的差异点。4.4 迁移后怎么确认数据没丢没坏数据从MySQL搬到PG之后绝对不能只看一眼数量就完事。我的校验清单是以下三条第一两边行数要完全一致第二抽30到50条随机记录比对全字段第三把常用查询SQL在PG端跑一遍确认语法兼容。具体操作上行数对比最简单-- MySQL SELECT COUNT(*) FROM bin_info; -- PostgreSQL SELECT COUNT(*) FROM bin_info;随机抽样比对可以借助MD5聚合函数两边分别计算全表的MD5哈希值比较结果是否一致-- MySQL SELECT MD5(GROUP_CONCAT(CONCAT_WS(|, bin_number, card_org, card_type, bank_name) ORDER BY bin_number)) AS data_hash FROM bin_info; -- PostgreSQL SELECT MD5(STRING_AGG(CONCAT_WS(|, bin_number, card_org, card_type, bank_name), ORDER BY bin_number)) AS data_hash FROM bin_info;哈希值一致说明迁移没有丢数据也没有乱数据。如果哈希值不一致再加一个字段逐项对比的步骤用FULL OUTER JOIN找出差异行。5. 常见问题与排查技巧速查整个项目跑下来我把高频问题整理成了一张表以后做同类数据迁移可以直接对着排问题现象可能原因排查方法与解决建议Excel中BIN号变科学计数法单元格格式是常规先设文本格式再粘贴或分列避免事后补救导入MySQL报Data too long字段错位或源数据含有超长文本用awk或文本编辑器检查CSV各列最大长度临时扩大字段类型定位导入后中文乱码字符集不匹配CSV另存为UTF-8连接串加characterEncodingutf8LOAD DATA报secure-file-priv服务器开启了文件导入限制加LOCAL关键字或把CSV放到指定目录BIN号前导0丢失用了数值类型存储bin_number统一用CHAR(6)导入前用Excel设置文本格式MySQL到PG自增主键冲突迁移时序列未同步迁移后执行setval函数把序列值调整到当前最大id游标/函数兼容性报错MySQL和PG语法差异LOAD DATA改成COPY命令自增语法改成SERIAL分页LIMIT基本通用有个我亲身踩过的坑要单独提一下从MySQL迁移到PG时如果表里已经有数据PG的BIGSERIAL序列初始值不会自动更新插入新数据时会报主键冲突unique constraint violation。迁移后一定要执行这个语句同步序列SELECT setval(bin_info_id_seq, (SELECT MAX(id) FROM bin_info));不然第二天业务一写入就炸排查半天还以为数据迁移出了问题实际只是序列断档。6. 实操过程中的一点体会这个项目本身不复杂但它把Excel清洗、MySQL导入、PG迁移这条常见数据链路完整串了一遍。我个人最大的体会是写代码的时间其实只占三成剩下七成全耗在数据清洗和数据校验上。Excel那一遍整干净了后面两个数据库基本是水到渠成的事如果Excel这关偷懒后面每到一个库都要再返工一遍工作量直接翻三倍。最后分享一个小技巧BIN号数据是持续更新的建议把整个流程封装成脚本Excel模板固定格式CSV导出固定编码MySQL的LOAD DATA语句和PG的COPY命令写成可重复执行的脚本每次拿到新数据后一键刷新。这样既保证了数据时效性也避免了手工操作带来的脏数据问题。我自己的做法是配了一个简单的shell脚本自动监控CSV文件的更新时间一旦有新文件进来就自动跑导入和校验流程下了班也能更新数据。本文还有配套的精品资源点击获取