简介这份资源面向金融数据分析、风控开发及数据库运维人员提供2020年4月25日版银行卡BIN号数据集合用于识别发卡行、卡片类型与归属机构支撑交易验证、反欺诈与客户分析等场景。压缩包共7个文件包含5个xls表格与2个sql脚本整体约1.4MBxls覆盖农民工卡、跨行转账卡、非标卡、单位结算卡等分类卡表sql脚本可直接导入MySQL或PostgreSQL便于快速建库建表。已有859人学习下载适合需要现成BIN基础数据、想对比Excel与关系型数据库管理方式的读者。通过该资源可获取结构化的银行卡归属行数据用于搭建本地查询表、验证SQL导入流程并作为后续定期更新BIN列表的参考样本。1. 银行卡BIN号数据落地从Excel到MySQL与PostgreSQL的完整迁移方案做支付、风控或者清结算的开发者大概率都遇到过这个场景业务方甩过来一个几十万行的Excel里面是银行卡BIN号发卡行识别码和对应的归属行信息要求你尽快导进数据库供接口查询。Excel打开就卡用Navicat导入还容易乱码字段类型对不上前导零被吃掉——这些都是血泪经验。这份资源提供的正是银行卡BIN号数据的Excel原始文件以及适配MySQL和PostgreSQL两种数据库的建表与导入方案。它解决的核心问题很明确把银行卡归属行查询所需的基础数据从一份容易出错的表格文件变成两个主流关系型数据库里可以直接用SQL查询的结构化数据。适合做支付网关、收单系统、风控规则引擎的开发者也适合需要离线维护BIN号库的运维同学。2. 数据表结构设计BIN号字段类型与索引怎么定2.1 为什么BIN号不能用INT存储银行卡BIN号通常是6到8位数字有些以0开头。如果建表时图省事用INT或者BIGINT前导零直接丢失后续按BIN号精确匹配就永远查不到。常见做法是用CHAR(8)或者VARCHAR(8)来存定长CHAR在MySQL里查询效率略好但PostgreSQL里CHAR类型会自动补空格容易在比较时踩坑。我一般统一用VARCHAR(8)配合应用层做trim处理两边数据库行为一致迁移时少很多玄学问题。-- MySQL 建表语句 CREATE TABLE bank_bin ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT 自增主键, bin_no VARCHAR(8) NOT NULL COMMENT 银行卡BIN号保留前导零, bank_name VARCHAR(64) NOT NULL COMMENT 归属行名称, bank_code VARCHAR(16) DEFAULT NULL COMMENT 银行内部编码, card_type TINYINT DEFAULT 0 COMMENT 卡类型0未知 1借记卡 2信用卡, card_length TINYINT DEFAULT NULL COMMENT 卡号总长度, created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 入库时间, UNIQUE KEY uk_bin (bin_no), KEY idx_bank_name (bank_name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT银行卡BIN号归属行对照表;上面这段MySQL建表语句里bin_no用VARCHAR(8)并加了唯一索引保证同一个BIN号不会重复录入。bank_name上建了普通索引方便按银行名称模糊搜索。card_type和card_length是可选字段如果原始Excel里没有这两列导入时可以忽略或者给默认值。created_at用于记录数据入库时间后续做增量更新时能派上用场。-- PostgreSQL 建表语句 CREATE TABLE bank_bin ( id SERIAL PRIMARY KEY, bin_no VARCHAR(8) NOT NULL, bank_name VARCHAR(64) NOT NULL, bank_code VARCHAR(16), card_type SMALLINT DEFAULT 0, card_length SMALLINT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT uk_bin UNIQUE (bin_no) ); CREATE INDEX idx_bank_name ON bank_bin (bank_name);PostgreSQL版本用SERIAL做主键SMALLINT替代TINYINT整体字段语义和MySQL保持一致。注意PostgreSQL里字符串比较默认区分大小写如果银行名称里有英文查询时要么统一转小写存储要么用ILIKE。两边建表语句的字段顺序和注释建议保持一致这样后续写数据同步脚本时不用做字段映射转换。2.2 索引策略与查询场景匹配BIN号查询的典型场景有两种一是精确匹配用户输入完整卡号后截取前6到8位查归属行二是前缀匹配用于卡号输入框的实时联想。精确匹配走唯一索引uk_bin前缀匹配在MySQL里可以用LIKE 622848%但要注意如果BIN号长度不固定前缀匹配可能命中多条。PostgreSQL里可以用text_pattern_ops索引来加速前缀查询但数据量在几十万级别时普通B-tree索引已经够用不必过度优化。注意如果原始Excel里BIN号长度有6位也有8位建议统一补齐到8位存储查询时也补齐再查避免同一家银行因为BIN长度不同产生重复记录。3. Excel数据清洗与导入从表格到数据库的完整链路3.1 导入前的数据检查清单拿到Excel后别急着往数据库里灌先做几项检查。第一确认BIN号列没有科学计数法显示Excel里长数字经常变成6.22848E11这种格式导入后全是错的。第二检查是否有合并单元格合并单元格会导致部分行BIN号为空。第三看银行名称列有没有多余空格或者不可见字符这些在数据库里会导致匹配失败。第四确认总行数和去重后的行数如果差得多说明有重复BIN号需要先去重。import pandas as pd # 读取Exceldtype指定BIN号为字符串避免被转成浮点数 df pd.read_excel(bank_bin.xlsx, dtype{BIN号: str, 银行名称: str}) # 去除首尾空格和不可见字符 df[BIN号] df[BIN号].str.strip().str.replace(r\s, , regexTrue) df[银行名称] df[银行名称].str.strip() # 过滤掉BIN号为空或长度不在6到8之间的行 df df[df[BIN号].notna() df[BIN号].str.len().between(6, 8)] # 按BIN号去重保留第一条 df df.drop_duplicates(subset[BIN号], keepfirst) print(f清洗后剩余 {len(df)} 条记录) df.to_csv(bank_bin_clean.csv, indexFalse, encodingutf-8)这段Python脚本做了四件事用dtype强制BIN号按字符串读取防止前导零丢失用str.strip()和正则去掉空格过滤掉长度不合规的行按BIN号去重。最后导出成CSV方便后续用数据库的批量导入命令加载。如果你的环境里没有pandas用openpyxl直接读也行但pandas处理起来更顺手。3.2 MySQL的LOAD DATA导入方式MySQL推荐用LOAD DATA LOCAL INFILE导入CSV比逐条INSERT快一个数量级。前提是MySQL服务端和客户端都开启了local_infile。-- 先确认local_infile已开启 SHOW VARIABLES LIKE local_infile; -- 如果为OFF在my.cnf的[mysqld]下加local_infile1并重启 -- 导入CSV LOAD DATA LOCAL INFILE /path/to/bank_bin_clean.csv INTO TABLE bank_bin FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 ROWS (bin_no, bank_name, bank_code, card_type, card_length);FIELDS TERMINATED BY ,指定CSV分隔符ENCLOSED BY 处理字段里可能包含逗号的情况IGNORE 1 ROWS跳过表头。括号里的字段列表要和CSV列顺序对应如果CSV里列顺序不同可以在括号里调整。导入完成后用SELECT COUNT(*) FROM bank_bin确认行数再随机抽几条SELECT * FROM bank_bin LIMIT 10看看数据有没有错位。3.3 PostgreSQL的COPY命令导入PostgreSQL用COPY命令效率同样很高。如果CSV文件在数据库服务器本地直接用COPY如果在客户端用\copy。-- 在psql里执行文件在客户端本地 \copy bank_bin(bin_no, bank_name, bank_code, card_type, card_length) FROM /path/to/bank_bin_clean.csv WITH (FORMAT csv, HEADER true, ENCODING UTF8);FORMAT csv指定CSV格式HEADER true跳过表头ENCODING UTF8确保中文银行名称不乱码。如果导入时报编码错误检查CSV文件本身是不是UTF-8Windows下用Excel另存的CSV默认是GBK需要先用编辑器转成UTF-8。导入后同样用SELECT COUNT(*)验证行数。4. 常见问题排查导入报错、乱码与查询性能4.1 BIN号前导零丢失现象导入后发现以0开头的BIN号变成了少一位的数字。原因Excel读取时把BIN号当成了数值类型或者数据库字段用了INT。解决Excel里先把BIN号列格式设为文本再复制Python读取时用dtypestr数据库字段用VARCHAR。已经导入的数据可以用LPAD(bin_no, 8, 0)更新补零但前提是知道原始长度。4.2 中文银行名称乱码现象查询结果里银行名称显示为问号或者乱码。原因CSV文件编码和数据库字符集不一致。解决MySQL建表时指定CHARSETutf8mb4导入时确认CSV是UTF-8编码PostgreSQL建库时用ENCODING UTF8导入时加ENCODING UTF8参数。Windows下用记事本另存为UTF-8可以解决大部分编码问题。4.3 导入速度慢或者卡死现象几十万行数据导入花了十几分钟甚至更久。原因用了逐条INSERT或者没有关闭自动提交。解决改用LOAD DATA或COPY批量导入MySQL导入前可以临时设置SET autocommit0和SET unique_checks0导入后再改回来。PostgreSQL导入前可以ALTER TABLE bank_bin DISABLE TRIGGER ALL导入后重新启用。4.4 查询时BIN号匹配不到现象明明数据库里有这个BIN号但用卡号前6位查就是查不到。原因卡号前6位可能包含空格或者BIN号在数据库里存的是8位而查询用了6位。解决查询前对卡号做trim和replace去空格查询时用LEFT(bin_no, 6) LEFT(card_no, 6)或者统一补齐到8位再比较。如果业务上BIN号长度不固定建议在应用层做归一化处理。4.5 数据库迁移时字段类型不兼容现象从MySQL导出的数据导入PostgreSQL时报类型错误。原因MySQL的TINYINT在PostgreSQL里对应SMALLINTDATETIME对应TIMESTAMPAUTO_INCREMENT对应SERIAL。解决导出时用mysqldump --compatiblepostgresql或者手动改写建表语句。数据本身用CSV中转最稳妥两边都用文本格式导入避免二进制格式的兼容问题。5. 二次开发与维护增量更新和接口封装5.1 增量更新BIN号库的脚本思路BIN号库不是一次导入就完事银行会发新卡BIN号会增减。我一般会写一个增量更新脚本对比新旧两份Excel找出新增和变更的记录只更新差异部分。核心逻辑是用bin_no做唯一键新数据里有的旧数据没有就INSERT两边都有但银行名称变了就UPDATE。import pandas as pd from sqlalchemy import create_engine, text # 读取新Excel和数据库现有数据 new_df pd.read_excel(bank_bin_new.xlsx, dtype{BIN号: str}) engine create_engine(mysqlpymysql://user:passlocalhost/payment) old_df pd.read_sql(SELECT bin_no, bank_name FROM bank_bin, engine) # 找出新增的BIN号 new_bins set(new_df[BIN号]) - set(old_df[bin_no]) # 找出银行名称变更的BIN号 merged new_df.merge(old_df, left_onBIN号, right_onbin_no, howinner) changed merged[merged[银行名称] ! merged[bank_name]] # 批量插入新增 if new_bins: insert_df new_df[new_df[BIN号].isin(new_bins)] insert_df.to_sql(bank_bin, engine, if_existsappend, indexFalse) # 批量更新变更 with engine.begin() as conn: for _, row in changed.iterrows(): conn.execute( text(UPDATE bank_bin SET bank_name:name WHERE bin_no:bin), {name: row[银行名称], bin: row[BIN号]} )这段脚本用SQLAlchemy连接数据库先拉取现有BIN号集合再和新数据做差集和交集运算。新增部分用to_sql批量插入变更部分逐条UPDATE。数据量不大时逐条更新可以接受如果变更记录超过几千条建议用临时表加INSERT ... ON DUPLICATE KEY UPDATE的方式效率更高。5.2 封装查询接口的注意事项如果要在应用层封装BIN号查询接口有几个点值得注意。第一加缓存。BIN号数据变化频率低适合放Redis或者本地Caffeine缓存设置几小时过期时间避免每次查询都打数据库。第二做降级。数据库查不到时返回“未知银行”而不是抛异常保证支付流程不中断。第三记录查询日志。把查不到的BIN号记下来定期分析是不是数据缺失方便后续补充。// Java示例带缓存的BIN号查询 public String getBankName(String cardNo) { String bin cardNo.length() 8 ? cardNo.substring(0, 8) : cardNo; String cacheKey bin: bin; String bankName redisTemplate.opsForValue().get(cacheKey); if (bankName ! null) { return bankName; } bankName bankBinMapper.selectByBin(bin); if (bankName null bin.length() 8) { // 8位查不到降级用6位再查一次 bankName bankBinMapper.selectByBin(bin.substring(0, 6)); } if (bankName null) { bankName 未知银行; } redisTemplate.opsForValue().set(cacheKey, bankName, 6, TimeUnit.HOURS); return bankName; }这段Java代码展示了缓存加降级的完整逻辑先查Redis命中直接返回未命中查数据库8位查不到时降级用6位再查最终查不到返回“未知银行”并写入缓存防止缓存穿透。缓存过期时间设6小时平衡数据新鲜度和数据库压力。5.3 数据校验与监控BIN号库上线后建议加一个定时校验任务每天跑一次检查数据库里BIN号总数是否在合理范围、有没有重复BIN号、银行名称有没有空值。发现异常时发告警。另外可以在查询接口里埋点统计每天查不到BIN号的请求比例如果比例突然升高说明数据可能过期了需要更新。从那以后我每次导入BIN号数据前都强制走一遍“Excel文本格式检查 → Python清洗 → 数据库导入 → 抽样验证”这个流程再也没出现过前导零丢失或者乱码的问题。希望帮到你。本文还有配套的精品资源点击获取