简介这份面向金融数据从业者、风控与支付清算岗位的资料包围绕银行卡BIN号与归属行识别提供可直接落地的Excel台账和MySQL、PostgreSQL导入脚本适合需要快速搭建BIN号查询、交易归属判断或反欺诈基础数据的环境。包内共7个文件以5个xls数据表与2个sql脚本为主压缩包约1.4MBxls覆盖农民工卡、跨行转账卡、非标卡、单位结算卡等卡表sql脚本便于在两类数据库中建表并批量导入。已有861人学习下载说明其在银行卡BIN号数据整理场景中具有一定参考价值。读者可获得按2020年4月版整理的BIN号字段与卡种信息用于银行归属匹配、卡类型分析及风控规则校验也能借鉴Excel与关系型数据库之间的数据迁移和更新思路减少从零收集与清洗基础数据的时间。1. 银行卡 BIN 号落到工程里为什么第一步是「定长化」运营给过来一份 Excel里面是银行卡 BIN 号、卡品牌、卡类型、发卡机构代码要求当天上线到 MySQL下周再同步一份到 PostgreSQL 做离线分析。看起来是个导入活实际动手就会发现麻烦全在数据本身BIN 号有的写 6 位、有的写 8 位Excel 把它当数字存前导零和末尾精度一起丢查询侧要按卡号前缀匹配写LIKE 6222%能跑但索引失效换成函数匹配更是全表扫。银行卡 BIN 号这套东西真正的工程难点不在 Excel 也不在 MySQL 或 PostgreSQL而在「长度不齐 前缀匹配 多库一致」三件事撞在一起。这篇按我实际做过的一套链路讲先把 BIN 定长化和区间化再分别灌进两个库最后把查询压到内存里。2. 把 Excel 里的 BIN 表变成可查询结构2.1 BIN 号到底存几位6 位、8 位与「补位区间」的取舍银行卡 BIN 号按 ISO/IEC 7812 的定义是卡号的前若干位早期行业习惯用 6 位后来扩展到 8 位于是市面上的 BIN 数据表天然是混长的。这件事直接决定了表结构怎么设计常见有三种做法我一次讲清取舍。第一种是原样存查询时用LEFT(card_no, LENGTH(bin_code)) bin_code动态匹配。可读性最好但LEFT()作用在列上B-Tree 索引直接废掉几万行还能忍几十万行加高并发就是灾难。第二种是全部补齐到 8 位短的右补0。听起来省事实际是错的622202补成62220200只能匹配卡号第 7、8 位恰好是00的情况而622202这个 BIN 覆盖的是第 7、8 位为任意数字的全部卡号。补0会把匹配范围缩小一百倍。第三种是我一般会用的区间化每一行额外存两列bin_low和bin_highbin_low是 BIN 右补0到 8 位bin_high是 BIN 右补9到 8 位。622202就变成[62220200, 62220299]。查询时把卡号截前 8 位判断它落在哪个区间里BETWEEN能走复合索引而且区间天然表达了「这个前缀覆盖哪些卡号」的语义。多位 BIN 并存时按LENGTH(bin_code) DESC排序取第一条就是最长前缀优先。表结构大致是这样字段类型说明bin_codeCHAR(8) / VARCHAR(8)原始 BIN保留 6 或 8 位原貌bin_lowCHAR(8) / VARCHAR(8)右补 0 到 8 位区间下界bin_highCHAR(8) / VARCHAR(8)右补 9 到 8 位区间上界brandVARCHAR(16)卡组织标识如演示值brand_acard_typeVARCHAR(16)借记 / 贷记 / 预付issuer_codeVARCHAR(32)内部发卡机构编码不用真实行名data_versionINT数据版本号用于增量和回滚statusTINYINT1 有效0 停用提示bin_code保留原长度是为了审计和人工核对真正参与匹配的是bin_low和bin_high两列别在这列上做前缀查询。2.2 Excel 侧的三次预处理文本化、去空行、字段收敛Excel 对长数字的处理是第一个翻车点。BIN 号在单元格里如果是「常规」格式超过 11 位会变科学计数法末尾几位直接归零如果 BIN 以0开头虽然少见但不是没有前导零会被吃掉。这两个问题在 Excel 里改格式只能救一部分最稳的做法是读的时候就把整列当字符串。我一般分三步洗第一步用dtypestr强制所有列按文本读关闭 pandas 的自动缺失值推断避免NA、NULL这类字样被吃掉第二步把列名统一小写去空格只保留需要的列其余丢弃减少后续映射出错的面第三步对 BIN 列做正则校验只留 6 到 8 位纯数字其余进异常清单人工看。import pandas as pd # dtypestr 让 Excel 里的长数字保持文本形态不被转成 float # keep_default_naFalse 避免 NA、null 被当成缺失值 df pd.read_excel( bin_source.xlsx, sheet_name0, dtypestr, keep_default_naFalse, ) # 列名统一去首尾空格、转小写防止上游改表头导致 KeyError df.columns [c.strip().lower() for c in df.columns] # 只保留参与入库的列 need_cols [bin_code, brand, card_type, issuer_code] df df[need_cols].copy() # BIN 列去空白字符再做强校验 df[bin_code] ( df[bin_code] .str.strip() .str.replace(r\s, , regexTrue) ) mask df[bin_code].str.fullmatch(r\d{6,8}) dropped df[~mask] df df[mask].copy() # 同一 BIN 出现多次时保留最后一条模拟运营的「后写覆盖」 df df.drop_duplicates(subset[bin_code], keeplast)逻辑上就三件事文本化读入、列名收敛、非法行拦截。参数里keep_default_naFalse是关键默认值会把字符串NA当成缺失发卡机构编码恰好是NA的时候会静默变空。keeplast是我按运营习惯定的覆盖语义如果你们的数据源是「先写的优先」改成first就行但这个语义必须和业务方确认不能拍脑袋。2.3 MySQL 与 PostgreSQL 的表结构差异两个库建同一张逻辑表物理定义要分开写因为字符类型的填充行为不一样。MySQL 侧我倾向CHAR(8)配utf8mb4_0900_ai_ci。这个排序规则是 NO PAD比较时不忽略尾部空格622202 和622202不相等行为符合直觉。如果沿用老的utf8mb4_general_ci它是 PAD SPACE尾部空格参与比较时被忽略看起来「更宽松」实际会让bin_low、bin_high的边界判断出现意外相等区间查询可能多命中一行。CREATE TABLE card_bin ( bin_code CHAR(8) NOT NULL COMMENT 原始BIN6位或8位, bin_low CHAR(8) NOT NULL COMMENT 右补0到8位, bin_high CHAR(8) NOT NULL COMMENT 右补9到8位, brand VARCHAR(16) NOT NULL, card_type VARCHAR(16) NOT NULL, issuer_code VARCHAR(32) NOT NULL, data_version INT NOT NULL DEFAULT 1, status TINYINT NOT NULL DEFAULT 1, PRIMARY KEY (bin_code, data_version), KEY idx_bin_range (bin_low, bin_high) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci;PostgreSQL 侧不要用char(8)用varchar(8)。PG 的char(n)是真正的定长且带空格填充写进去 6 位会补到 8 位空格读出来也带空格跨库比对时会出现「看起来一样的值不相等」。CREATE TABLE card_bin ( bin_code varchar(8) NOT NULL, bin_low varchar(8) NOT NULL, bin_high varchar(8) NOT NULL, brand varchar(16) NOT NULL, card_type varchar(16) NOT NULL, issuer_code varchar(32) NOT NULL, data_version integer NOT NULL DEFAULT 1, status smallint NOT NULL DEFAULT 1, PRIMARY KEY (bin_code, data_version) ); CREATE INDEX idx_bin_range ON card_bin (bin_low, bin_high);两边的字段名、顺序、注释全部对齐主键都带上data_version这是后面做增量和回滚的基础。别小看这个复合主键它让同一 BIN 可以保留多个版本更新时先插新版本再改status出问题切回老版本就行不用改数据。3. 从 xlsx 到 MySQLLOAD DATA 的完整链路与参数3.1 用 Python 把 xlsx 洗成干净 CSV直接从 pandas 写进 MySQL 也能跑但几万行以上我更愿意中间落一份 CSV一是可复查二是LOAD DATA比逐行INSERT快一个数量级三是出问题时能拿着 CSV 单独重放不用重跑整条链路。import csv # 先算出区间边界再落盘避免入库时再算一遍 def pad_right(s: str, ch: str, width: int 8) - str: 把 BIN 右补到 8 位已满 8 位则原样返回 return (s ch * width)[:width] df[bin_low] df[bin_code].map(lambda s: pad_right(s, 0)) df[bin_high] df[bin_code].map(lambda s: pad_right(s, 9)) # 固定列顺序和 LOAD DATA 的字段列表一一对应 out_cols [bin_code, bin_low, bin_high, brand, card_type, issuer_code] # utf-8-sig 让 Excel 打开不乱码但入库时要能吃掉 BOM df[out_cols].to_csv( bin_clean.csv, indexFalse, encodingutf-8-sig, quotingcsv.QUOTE_MINIMAL, lineterminator\n, )三个参数值得说。encodingutf-8-sig会在文件头写一个 BOM好处是运营用 Excel 打开核对时中文不乱码坏处是 MySQL 导入后第一列字段值可能变成bin_code。我的处理是导入语句里显式指定列映射不用 CSV 表头BOM 只影响表头那一行IGNORE 1 LINES直接跳过问题就消失了。lineterminator\n强制 LF避免 Windows 环境下写出\r\n导致最后一个字段尾部带\r。QUOTE_MINIMAL是默认行为但显式写出来后面排查时不用猜。3.2 LOAD DATA LOCAL INFILE 的字段映射与容错-- 客户端侧执行LOCAL 表示文件在应用机器上 LOAD DATA LOCAL INFILE /tmp/bin_clean.csv INTO TABLE card_bin CHARACTER SET utf8mb4 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (bin_code, bin_low, bin_high, brand, card_type, issuer_code) SET bin_code TRIM(bin_code), bin_low TRIM(bin_low), bin_high TRIM(bin_high), data_version 1, status 1;LOCAL和非LOCAL的区别要分清带LOCAL是客户端读文件、通过连接发给服务端受客户端local_infile开关控制不带LOCAL是服务端自己读受secure_file_priv目录限制路径必须是服务端能看到的位置。生产环境里我一般用LOCAL因为文件在应用侧生成改服务端配置的沟通成本更高。变量的写法是为了在SET里做清洗。CSV 里字段如果带空格TRIM能兜住data_version和status不在文件里直接在SET里给常量。执行前记得确认客户端连接串里local_infile1用pymysql的话是local_infileTrue这条不加会直接报LOAD DATA LOCAL INFILE is forbidden。导入完先做三项核对行数是否等于 CSV 数据行数、bin_low bin_high是否全部成立、bin_code是否有重复。SELECT COUNT(*) AS total, SUM(bin_low bin_high) AS bad_range, COUNT(*) - COUNT(DISTINCT bin_code) AS dup_bin FROM card_bin WHERE data_version 1;bad_range应该为 0dup_bin应该为 0。前者大于 0 说明补位逻辑写错后者大于 0 说明去重没生效。这两个指标我每次导入都会看比肉眼看几条数据靠谱得多。3.3 索引与区间列让 BIN 查询走下 B-Tree查询入口一般是「给一个卡号返回卡品牌和卡类型」。卡号可能是完整 16 到 19 位先截前 8 位再拿去匹配区间。SET card8 LEFT(4000001234567890, 8); SELECT bin_code, brand, card_type, issuer_code FROM card_bin WHERE status 1 AND card8 BETWEEN bin_low AND bin_high ORDER BY CHAR_LENGTH(bin_code) DESC LIMIT 1;ORDER BY CHAR_LENGTH(bin_code) DESC是最长前缀优先LIMIT 1保证只取最精确的一条。执行计划里应该看到idx_bin_range的range访问rows是个小数字。如果看到ALL全表扫先检查bin_low、bin_high的字段类型和card8是否一致——类型不一致会触发隐式转换索引直接不生效。status 1这个条件会让优化器在「走索引再过滤」和「全表扫」之间犹豫。数据量小的时候无所谓几十万行时可以把status并进索引建成(status, bin_low, bin_high)让过滤前置。代价是索引变大写少读多的场景值得这么做。还有一点BETWEEN的左右都是字符串比较前提是两边长度一致、字符集一致。如果card8因为卡号不足 8 位比如传了个 6 位测试号变成更短的串比较结果会不符合预期。入口处统一校验「卡号长度 8」再截取能省掉一类诡异问题。4. 同一份数据进 PostgreSQLCOPY、排序规则与前缀索引4.1 COPY 导入与列类型选择PostgreSQL 侧我不用逐行插直接用COPY。同一份bin_clean.csv可以复用只是列类型要按 PG 的规则走。-- psql 客户端执行\copy 读的是客户端本地文件 \copy card_bin (bin_code, bin_low, bin_high, brand, card_type, issuer_code) FROM /tmp/bin_clean.csv WITH (FORMAT csv, HEADER true, ENCODING UTF8, DELIMITER ,);\copy是 psql 的元命令文件在客户端侧权限模型简单不需要动服务端的pg_read_server_files角色。HEADER true会自动跳过第一行BOM 在表头里跳过表头就一并解决了。ENCODING UTF8显式声明避免服务端默认编码不一致导致中文变成问号。表里bin_low、bin_high用varchar(8)这点在 2.3 节已经说过理由。导入后同样做核对SELECT COUNT(*) AS total, COUNT(*) FILTER (WHERE bin_low bin_high) AS bad_range, COUNT(*) - COUNT(DISTINCT bin_code) AS dup_bin FROM card_bin WHERE data_version 1;FILTER是 PG 的写法比SUM(CASE WHEN ...)干净。三个指标的含义和 MySQL 侧完全一致跨库比对时可以直接拿结果对。4.2 text_pattern_ops 与 BETWEEN 的实测差别PG 里做前缀匹配有两条路。一条是按 BIN 原值做LIKESELECT * FROM card_bin WHERE bin_code LIKE 400000 || %;在默认的en_US.UTF-8这类 locale 下普通 B-Tree 索引对LIKE prefix%是不生效的因为排序规则依赖 locale优化器不敢保证前缀连续。要让索引生效得建varchar_pattern_ops操作符类的索引CREATE INDEX idx_bin_prefix ON card_bin (bin_code varchar_pattern_ops);另一条就是我更推荐的区间法和 MySQL 侧保持同一套语义SELECT bin_code, brand, card_type, issuer_code FROM card_bin WHERE status 1 AND LEFT(4000001234567890, 8) BETWEEN bin_low AND bin_high ORDER BY length(bin_code) DESC LIMIT 1;对比一下两种方案在 20 万行模拟表上的表现方案索引6/8 位混长支持典型执行计划备注LIKE 前缀 pattern_ops需要额外建需要多次查询拼最长Index Scan只适合单一长度区间 BETWEEN普通 B-Tree原生支持Index Scan / Bitmap两库语义统一函数 LEFT 匹配无支持Seq Scan只用于小表兜底混长是这套数据的常态LIKE方案要么查两次先查 8 位再查 6 位要么把 6 位 BIN 也补成 8 位再匹配前者多一次往返后者回到 2.1 节说过的错误补位。区间法一次查询解决两库还能共用同一段 SQL 逻辑维护成本低得多。4.3 两库字段对齐与增量同步MySQL 服务在线查询PostgreSQL 服务离线分析两个库的数据必须能对上。我一般写一个轻量同步脚本按data_version拉增量不整表重灌。import pymysql import psycopg2 MYSQL_CONF dict(host127.0.0.1, port3306, userapp, password***, databasepay, charsetutf8mb4) PG_CONF dict(host127.0.0.1, port5432, userapp, password***, dbnamepay) def fetch_since(version: int): 从 MySQL 拉取大于指定版本的记录 sql SELECT bin_code, bin_low, bin_high, brand, card_type, issuer_code, data_version, status FROM card_bin WHERE data_version %s with pymysql.connect(**MYSQL_CONF) as conn: with conn.cursor(pymysql.cursors.DictCursor) as cur: cur.execute(sql, (version,)) return cur.fetchall() def upsert_pg(rows): 按主键冲突更新保证重复执行幂等 if not rows: return 0 sql INSERT INTO card_bin (bin_code, bin_low, bin_high, brand, card_type, issuer_code, data_version, status) VALUES (%(bin_code)s, %(bin_low)s, %(bin_high)s, %(brand)s, %(card_type)s, %(issuer_code)s, %(data_version)s, %(status)s) ON CONFLICT (bin_code, data_version) DO UPDATE SET brand EXCLUDED.brand, card_type EXCLUDED.card_type, issuer_code EXCLUDED.issuer_code, status EXCLUDED.status with psycopg2.connect(**PG_CONF) as conn: with conn.cursor() as cur: cur.executemany(sql, rows) conn.commit() return len(rows)ON CONFLICT ... DO UPDATE是 PG 的 upsert 写法脚本重复跑不会产生重复行这点在定时任务里很重要。同步游标当前最大data_version不能存在内存里要落到一张小表或者配置文件否则进程重启后要么重灌全量要么漏数据。我一般存在 PG 的sync_cursor表里和业务表分开免得清理数据时一起删掉。同步完做一次计数比对-- MySQL 和 PG 各跑一遍结果应该一致 SELECT COUNT(DISTINCT bin_code) FROM card_bin WHERE status 1;5. 银行卡 BIN 匹配的五类常见问题排查5.1 Excel 里 BIN 变成科学计数法末尾几位归零现象导入后bin_code出现4.0000E15这种值或者末尾几位变成0000。原因Excel 单元格默认「常规」格式超过 11 位的数字按浮点存储精度丢失发生在保存文件那一刻读的时候已经救不回来了。解决读入时dtypestr只能救「文件本身还是文本」的情况如果源文件已经被转成数值只能让上游重新导出把 BIN 列格式设为「文本」或者用400000这种公式写法强制文本。入库前加一道fullmatch(r\d{6,8})校验把坏数据挡在表外比事后清洗便宜。5.2 CSV 的 BOM 让 MySQL 首列字段名带现象LOAD DATA报Unknown column bin_code in field list或者导入后第一列全空。原因utf-8-sig写出的文件带 BOMMySQL 客户端按 UTF-8 解析时把 BOM 当成字段名的一部分。解决导入语句不要依赖 CSV 表头显式写列映射配合IGNORE 1 LINES跳过表头行。如果一定要用表头把文件转成无 BOM 的utf-8但这样运营用 Excel 打开中文会乱码两害相权显式列映射更省事。5.3 PostgreSQL 用 char(8) 导致跨库比对不相等现象同一条 BIN 在 MySQL 里是622202在 PG 里查出来是622202脚本比对时判定不一致。原因PG 的char(n)是定长带空格填充6 位值会补到 8 位读出来带尾部空格MySQL 的CHAR在 NO PAD 排序规则下不补。解决PG 侧统一用varchar(8)从建表就避开。已经建了的表可以ALTER COLUMN ... TYPE varchar(8)同时TRIM一遍存量数据。这个坑我在跨库对账脚本里踩过一次血泪经验是字符类型的选择要在建表时定死后期改的数据迁移成本远高于当初多想两分钟。5.4 6 位和 8 位 BIN 同时存在最长匹配顺序写反现象卡号6222021234567890同时命中6222026 位和622202128 位两条记录返回的却是 6 位那条的卡类型。原因区间查询没加排序LIMIT 1拿的是物理顺序上的第一条不一定是 BIN 最长的那条。解决ORDER BY CHAR_LENGTH(bin_code) DESCPG 用length(bin_code) DESC必须加并且要有对应的测试用例覆盖「同一卡号命中多个 BIN」的场景。这类问题不会报错只会静默返回错值比报错难查十倍。5.5 数据更新后缓存还返回旧卡品牌现象运营改了某个 BIN 的卡类型数据库里已经更新线上查询结果还是旧的重启应用才恢复。原因应用侧把 BIN 表全量加载进内存做本地缓存更新后没有失效机制缓存和库各说各话。解决给数据加data_version缓存里同时存版本号定时任务比对版本号变化后整表重载用双缓冲原子替换避免重载瞬间读到半份数据。别用「更新时逐条失效」BIN 表是全量快照语义逐条失效既复杂又容易漏。6. 进阶把 BIN 查询压到微秒级的区间数组与热更新数据库查询再快也是毫秒级支付链路上一次交易要查好几次 BIN累计起来就是可观的延迟。我一般会在应用侧再做一层内存索引把(bin_low, bin_high, payload)按bin_low排序存成数组查询时用二分定位候选区间再在候选里选 BIN 最长的。import bisect from typing import Optional class BinIndex: def __init__(self, rows): # 按 bin_low 升序排列二分才有意义 self._rows sorted(rows, keylambda r: r[bin_low]) self._lows [r[bin_low] for r in self._rows] self.version max((r[data_version] for r in rows), default0) def lookup(self, card_no: str) - Optional[dict]: card8 card_no[:8].ljust(8, 0) # 找到最后一个 bin_low card8 的位置候选都在它左边 pos bisect.bisect_right(self._lows, card8) - 1 best None while pos 0: row self._rows[pos] if row[bin_high] card8: if best is None or len(row[bin_code]) len(best[bin_code]): best row # 区间有序性保证再往左的 bin_high 不会更长可提前退出 if best and len(best[bin_code]) 8: break pos - 1 return bestbisect_right定位的是「所有bin_low不大于card8的行」的最右端候选只可能在这个位置及其左侧不需要从头扫。row[bin_high] card8判断区间是否覆盖满足就参与最长比较。这段代码的边界在混长数据上要格外小心6 位 BIN 的bin_high是62220299可能覆盖到 8 位 BIN 的区间范围之外所以不能只看bin_high排序必须逐个回退比对。数组长度是几万到几十万时二分加少量回退单次查询在微秒级。热更新我一般这么做后台线程每 60 秒拉一次 MySQL 的MAX(data_version)和内存索引的version比对不一致就构建新索引对象构建完成后用一次引用赋值替换旧对象。Python 的引用赋值是原子的读线程要么拿到旧索引要么拿到新索引不会读到构建到一半的中间态。_index BinIndex(load_all_from_mysql()) def refresh_loop(): global _index while True: time.sleep(60) latest query_max_version() if latest _index.version: new_index BinIndex(load_all_from_mysql()) _index new_index # 原子替换读线程无锁验证这套东西有没有做对我会准备一组脱敏卡号样本同时跑「数据库区间查询」和「内存索引查询」逐条比对brand、card_type、issuer_code三个字段要求 100% 一致。样本里特意混入 6 位 BIN、8 位 BIN、边界卡号第 8 位正好是0或9、以及不存在的 BIN这四类能覆盖绝大多数匹配错位。比对脚本放进 CI每次改索引逻辑都跑一遍比人肉点页面靠谱。说个我自己的教训早期做这套表的时候我图省事没存bin_low、bin_high直接拿bin_code做LIKE在测试库几万行上跑得飞快上线后数据涨到几十万行查询延迟从 3ms 涨到 400ms翻遍慢查询日志才发现是全表扫。后来补区间列、加索引、再加内存层才算把延迟压回来。这三步每一步都不复杂难的是当初建表时就得想到「数据会涨、BIN 会长短不一、查询会高频」这三件事。如果你正准备接这份 Excel先把区间列和最长匹配这两件事定下来后面能省掉一大半返工。希望帮到你。本文还有配套的精品资源点击获取
