简介：这份面向金融数据从业者、风控与支付清算岗位的资料包，围绕银行卡BIN号与归属行识别，提供可直接落地的Excel台账和MySQL、PostgreSQL导入脚本，适合需要快速搭建BIN号查询、交易归属判断或反欺诈基础数据的环境。包内共7个文件，以5个xls数据表与2个sql脚本为主，压缩包约1.4MB，xls覆盖农民工卡、跨行转账卡、非标卡、单位结算卡等卡表，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_high，bin_low是 BIN 右补0到 8 位，bin_high是 BIN 右补9到 8 位。622202就变成[62220200, 62220299]。查询时把卡号截前 8 位，判断它落在哪个区间里，BETWEEN能走复合索引，而且区间天然表达了「这个前缀覆盖哪些卡号」的语义。多位 BIN 并存时，按LENGTH(bin_code) DESC排序取第一条，就是最长前缀优先。

表结构大致是这样：

提示：bin_code保留原长度是为了审计和人工核对，真正参与匹配的是bin_low和bin_high两列，别在这列上做前缀查询。

2.2 Excel 侧的三次预处理：文本化、去空行、字段收敛

Excel 对长数字的处理是第一个翻车点。BIN 号在单元格里如果是「常规」格式，超过 11 位会变科学计数法，末尾几位直接归零；如果 BIN 以0开头（虽然少见但不是没有），前导零会被吃掉。这两个问题在 Excel 里改格式只能救一部分，最稳的做法是读的时候就把整列当字符串。

我一般分三步洗：第一步，用dtype=str强制所有列按文本读，关闭 pandas 的自动缺失值推断，避免NA、NULL这类字样被吃掉；第二步，把列名统一小写去空格，只保留需要的列，其余丢弃，减少后续映射出错的面；第三步，对 BIN 列做正则校验，只留 6 到 8 位纯数字，其余进异常清单人工看。

import pandas as pd # dtype=str 让 Excel 里的长数字保持文本形态，不被转成 float # keep_default_na=False 避免 "NA"、"null" 被当成缺失值 df = pd.read_excel( "bin_source.xlsx", sheet_name=0, dtype=str, keep_default_na=False, ) # 列名统一：去首尾空格、转小写，防止上游改表头导致 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+", "", regex=True) ) 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"], keep="last")

逻辑上就三件事：文本化读入、列名收敛、非法行拦截。参数里keep_default_na=False是关键，默认值会把字符串"NA"当成缺失，发卡机构编码恰好是NA的时候会静默变空。keep="last"是我按运营习惯定的覆盖语义，如果你们的数据源是「先写的优先」，改成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 '原始BIN，6位或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) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_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 到 MySQL：LOAD 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", index=False, encoding="utf-8-sig", quoting=csv.QUOTE_MINIMAL, lineterminator="\n", )

三个参数值得说。encoding="utf-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_infile=1，用pymysql的话是local_infile=True，这条不加会直接报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应该为 0，dup_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. 同一份数据进 PostgreSQL：COPY、排序规则与前缀索引

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 原值做LIKE：

SELECT * 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 万行模拟表上的表现：

混长是这套数据的常态，LIKE方案要么查两次（先查 8 位再查 6 位），要么把 6 位 BIN 也补成 8 位再匹配，前者多一次往返，后者回到 2.1 节说过的错误补位。区间法一次查询解决，两库还能共用同一段 SQL 逻辑，维护成本低得多。

4.3 两库字段对齐与增量同步

MySQL 服务在线查询，PostgreSQL 服务离线分析，两个库的数据必须能对上。我一般写一个轻量同步脚本，按data_version拉增量，不整表重灌。

import pymysql import psycopg2 MYSQL_CONF = dict(host="127.0.0.1", port=3306, user="app", password="***", database="pay", charset="utf8mb4") PG_CONF = dict(host="127.0.0.1", port=5432, user="app", password="***", dbname="pay") 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.0000E+15这种值，或者末尾几位变成0000。

原因：Excel 单元格默认「常规」格式，超过 11 位的数字按浮点存储，精度丢失发生在保存文件那一刻，读的时候已经救不回来了。

解决：读入时dtype=str只能救「文件本身还是文本」的情况；如果源文件已经被转成数值，只能让上游重新导出，把 BIN 列格式设为「文本」，或者用="400000"这种公式写法强制文本。入库前加一道fullmatch(r"\d{6,8}")校验，把坏数据挡在表外，比事后清洗便宜。

5.2 CSV 的 BOM 让 MySQL 首列字段名带

现象：LOAD DATA报Unknown column 'bin_code' in 'field list'，或者导入后第一列全空。

原因：utf-8-sig写出的文件带 BOM，MySQL 客户端按 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同时命中622202（6 位）和62220212（8 位）两条记录，返回的却是 6 位那条的卡类型。

原因：区间查询没加排序，LIMIT 1拿的是物理顺序上的第一条，不一定是 BIN 最长的那条。

解决：ORDER BY CHAR_LENGTH(bin_code) DESC（PG 用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, key=lambda r: r["bin_low"]) self._lows = [r["bin_low"] for r in self._rows] self.version = max((r["data_version"] for r in rows), default=0) 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 best

bisect_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，先把区间列和最长匹配这两件事定下来，后面能省掉一大半返工。希望帮到你。

本文还有配套的精品资源，点击获取