简介这份资源是银联官方于2020年4月25日发布的银行卡BIN数据合集面向支付系统开发、金融风控、银行卡识别与清算对账等场景的工程师和数据分析人员可解决BIN查询、发卡行匹配、卡种判定等基础数据需求。压缩包共7个文件以xls表格和sql脚本为主另含少量系统隐藏文件整体约1.12MB其中Excel表覆盖非标卡、农民工卡、跨行转账卡、单位结算卡及通用卡表等多个维度sql文件已整理为9868条记录可直接导入MySQL使用。数据字段包含银行卡BIN、BIN长度、发卡行、银行卡名、银行卡类型及银行卡长度等官方来源保证了齐全度与准确性。目前已有3491人学习下载适合需要快速搭建BIN库、做卡bin校验或对账系统原型的开发者参考使用。1. 银行卡BIN数据到底存了什么从一串卡号前六位说起很多人第一次接触银行卡BIN数据是因为业务系统里要做卡种识别、发卡行匹配或者风控规则。BIN全称Bank Identification Number也就是卡号前6到8位它决定了这张卡属于哪家发卡机构、是借记卡还是信用卡、卡组织是银联还是其他。2020年前后银联官方发布过一批相对完整的BIN对照数据常见形态就是一份Excel加一个MySQL导入脚本。拿到手之后你会发现真正麻烦的不是数据本身而是怎么把它从Excel干净地灌进MySQL再让业务查询跑得动。这篇笔记就按这个路径走先看清字段结构再落地建表和导入最后把查询和更新机制讲透。适合正在做支付、清结算、风控或者卡管理系统的工程师也适合需要自己维护一份BIN库的团队。2. 先看清Excel里的字段哪些列能直接用哪些必须清洗2.1 一份典型BIN表的列结构与含义银联官方发布的BIN数据通常以Excel形式分发常见列包括BIN号、卡种、发卡行名称、发卡行标识、卡组织、卡类型借记/信用、卡号长度、校验方式等。不同批次的列名可能有细微差异但核心字段基本固定。下面这张表是我整理过多次之后认为最需要关注的列列名是否必填说明常见问题BIN是6到8位数字有的行是8位有的只有6位卡种是如借记卡、信用卡、预付卡存在中英文混用发卡行名称是中文全称有空格和不可见字符发卡行标识否内部编码部分行为空卡组织是银联为主少数行有其他组织卡号长度是通常16到19位有的写成范围如“16-19”校验方式否Luhn等多数为空拿到Excel后不要急着导入先做三件事确认BIN列没有前导零丢失、确认卡号长度列是纯数字还是范围、确认发卡行名称里有没有换行符。前导零丢失是Excel最常见的坑因为Excel会把“012345”当成数字12345。解决办法是在读取时强制按字符串处理。2.2 用Python做一次字段体检在导入之前我一般会先用pandas跑一遍字段体检把异常行筛出来。下面这段代码可以直接抄import pandas as pd # 读取时强制BIN为字符串避免前导零丢失 df pd.read_excel(bin_2020.xlsx, dtype{BIN: str}) # 基础体检 print(总行数:, len(df)) print(BIN列空值:, df[BIN].isna().sum()) print(BIN长度分布:) print(df[BIN].str.len().value_counts()) # 检查是否有非数字字符 bad_bin df[~df[BIN].str.match(r^\d{6,8}$, naFalse)] print(异常BIN行数:, len(bad_bin)) print(bad_bin[[BIN, 发卡行名称]].head(10)) # 检查发卡行名称中的不可见字符 df[发卡行名称] df[发卡行名称].astype(str).str.strip() df[发卡行名称] df[发卡行名称].str.replace(r\s, , regexTrue)这段代码的逻辑很直接先强制BIN为字符串再用正则筛出不符合6到8位纯数字的行最后把发卡行名称里的空白字符全部去掉。参数上唯一需要注意的是dtype{BIN: str}如果不加这一句后面所有关于前导零的处理都是白费。体检完之后把异常行单独导出人工确认不要直接删因为有些异常行可能是数据本身的问题删了会丢信息。2.3 卡号长度列的范围值怎么拆卡号长度列经常写成“16-19”这种范围直接存进MySQL的int字段会报错。我的做法是拆成两个字段card_len_min和card_len_max。如果只有一个值两个字段都填同一个数。这样查询的时候可以用BETWEEN比存字符串灵活得多。拆分逻辑用一行代码就能搞定def parse_len(val): val str(val).strip() if - in val: parts val.split(-) return int(parts[0]), int(parts[1]) return int(val), int(val) df[[card_len_min, card_len_max]] df[卡号长度].apply( lambda x: pd.Series(parse_len(x)) )这里要注意如果卡号长度列里有非数字字符int()会抛异常。所以实际跑之前先加一层try/except把失败的行记下来单独处理。我一般会把失败行写到一个bin_parse_failed.csv里导入完成后再回头看。3. 建MySQL表字段类型和索引怎么定才不翻车3.1 表结构设计BIN做主键还是唯一索引BIN数据的特点是读多写少查询几乎都是按BIN精确匹配或者按发卡行模糊搜索。所以表结构设计的第一原则是BIN列必须走索引。我一般把BIN设为主键因为BIN本身在数据里应该是唯一的。但实际数据里偶尔会有重复BIN这时候主键会报错所以更稳妥的做法是加一个自增id做主键BIN上加唯一索引。下面是我常用的建表语句CREATE TABLE bank_bin ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, bin CHAR(8) NOT NULL COMMENT 卡号前6到8位, card_type VARCHAR(32) DEFAULT NULL COMMENT 卡种, bank_name VARCHAR(128) DEFAULT NULL COMMENT 发卡行名称, bank_code VARCHAR(32) DEFAULT NULL COMMENT 发卡行标识, card_org VARCHAR(32) DEFAULT NULL COMMENT 卡组织, card_category VARCHAR(16) DEFAULT NULL COMMENT 借记/信用, card_len_min TINYINT UNSIGNED DEFAULT NULL, card_len_max TINYINT UNSIGNED DEFAULT NULL, luhn_flag TINYINT DEFAULT 0 COMMENT 是否校验Luhn, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_bin (bin), KEY idx_bank_name (bank_name), KEY idx_card_org (card_org) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT银行卡BIN对照表;字段类型上有几个点值得说bin用CHAR(8)而不是VARCHAR因为长度固定CHAR在索引里效率更高card_len_min和card_len_max用TINYINT UNSIGNED因为卡号长度不会超过255bank_name上加了普通索引因为按发卡行搜索是常见需求。字符集用utf8mb4避免发卡行名称里有生僻字导致导入失败。3.2 用LOAD DATA LOCAL INFILE批量导入Excel转成CSV之后最快的导入方式是LOAD DATA LOCAL INFILE。但这里有个坑MySQL 8.0默认关闭了local_infile需要先在服务端和客户端都打开。下面是我常用的导入流程# 1. Excel转CSV注意用utf-8编码 python -c import pandas as pd df pd.read_excel(bin_2020.xlsx, dtype{BIN: str}) df.to_csv(bin_2020.csv, indexFalse, encodingutf-8) # 2. 登录MySQL开启local_infile mysql -u root -p --local-infile1-- 3. 服务端开启 SET GLOBAL local_infile 1; -- 4. 导入 LOAD DATA LOCAL INFILE bin_2020.csv INTO TABLE bank_bin CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (bin, card_type, bank_name, bank_code, card_org, card_category, card_len_min, card_len_max) SET luhn_flag 0;这段导入语句里IGNORE 1 LINES跳过CSV表头SET luhn_flag 0给所有行一个默认值。如果CSV里有空值MySQL会按字段默认值处理所以建表时DEFAULT NULL很重要。导入完成后用SELECT COUNT(*)核对行数和Excel行数对不上就说明有行被截断或者报错被跳过。3.3 导入后必做的三项校验导入完成不代表数据可用我一般会跑三个校验查询-- 校验1BIN长度是否都在6到8位 SELECT COUNT(*) FROM bank_bin WHERE CHAR_LENGTH(bin) NOT BETWEEN 6 AND 8; -- 校验2发卡行名称为空的行 SELECT COUNT(*) FROM bank_bin WHERE bank_name IS NULL OR bank_name ; -- 校验3重复BIN检查理论上唯一索引会拦住但导入时可能被忽略 SELECT bin, COUNT(*) AS cnt FROM bank_bin GROUP BY bin HAVING cnt 1;校验1如果结果不为0说明有BIN长度异常需要回Excel里找原因。校验2的结果通常是0因为发卡行名称是核心字段。校验3在唯一索引生效的情况下也应该是0但如果导入时用了IGNORE关键字重复行会被静默跳过这时候要对比导入前后的行数差。4. 查询与更新BIN库上线后怎么维护才不变成黑匣子4.1 按卡号前缀匹配BIN的两种写法业务系统里最常见的查询是给一个完整卡号返回发卡行信息。因为BIN可能是6位也可能是8位所以匹配逻辑要兼容两种长度。我一般用两种写法按性能需求选-- 写法1精确匹配6位和8位 SELECT * FROM bank_bin WHERE bin LEFT(6222021234567890, 6) OR bin LEFT(6222021234567890, 8) LIMIT 1; -- 写法2用LIKE前缀匹配数据量大时不推荐 SELECT * FROM bank_bin WHERE 6222021234567890 LIKE CONCAT(bin, %) ORDER BY CHAR_LENGTH(bin) DESC LIMIT 1;写法1的问题是如果BIN库里同时存在6位和8位的记录可能返回两条需要业务层再判断。写法2用LIKE会导致全表扫描数据量超过10万行就明显变慢。我的做法是在应用层先取卡号前8位再依次尝试8位和6位精确查询这样能走唯一索引性能最好。4.2 增量更新新BIN怎么合并进现有表银联的BIN数据不是一成不变的新发卡行和新卡种会不断出现。增量更新的常见做法是把新数据导入一张临时表然后用INSERT ... ON DUPLICATE KEY UPDATE合并。下面是我常用的合并语句-- 创建临时表并导入新数据 CREATE TABLE bank_bin_tmp LIKE bank_bin; LOAD DATA LOCAL INFILE bin_2021_new.csv INTO TABLE bank_bin_tmp FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (bin, card_type, bank_name, bank_code, card_org, card_category, card_len_min, card_len_max); -- 合并BIN存在则更新不存在则插入 INSERT INTO bank_bin (bin, card_type, bank_name, bank_code, card_org, card_category, card_len_min, card_len_max) SELECT bin, card_type, bank_name, bank_code, card_org, card_category, card_len_min, card_len_max FROM bank_bin_tmp ON DUPLICATE KEY UPDATE card_type VALUES(card_type), bank_name VALUES(bank_name), bank_code VALUES(bank_code), card_org VALUES(card_org), card_category VALUES(card_category), card_len_min VALUES(card_len_min), card_len_max VALUES(card_len_max), updated_at CURRENT_TIMESTAMP; -- 清理临时表 DROP TABLE bank_bin_tmp;这里的关键是ON DUPLICATE KEY UPDATE依赖唯一索引uk_bin所以建表时那个唯一索引不能省。另外VALUES()函数在MySQL 8.0.20之后被标记为废弃新版本可以用别名写法但为了兼容性我一般还是用VALUES()。4.3 用视图封装常用查询如果业务系统频繁按发卡行或者卡组织做统计可以建一个视图把常用字段组合起来减少重复SQLCREATE VIEW v_bin_summary AS SELECT bin, bank_name, card_org, card_category, CONCAT(card_len_min, -, card_len_max) AS card_len_range FROM bank_bin WHERE bank_name IS NOT NULL;视图的好处是业务方不用关心底层字段变化坏处是视图查询不能走索引优化。所以视图只适合做展示层核心匹配逻辑还是要直接查表。5. 避坑与排查BIN数据导入和查询中最容易翻车的5个点5.1 前导零丢失导致BIN匹配不上现象导入后发现某些BIN在业务系统里查不到但Excel里明明有。原因Excel把BIN列当成数字前导零被吃掉比如“012345”变成“12345”。解决读取Excel时强制dtype{BIN: str}导入MySQL时用CHAR类型而不是INT。如果已经导入了错误数据用LPAD(bin, 6, 0)批量修复。5.2 字符集不一致导致发卡行名称乱码现象导入后发卡行名称显示为问号或者乱码。原因CSV文件是GBK编码MySQL表是utf8mb4导入时没有指定字符集。解决Excel转CSV时统一用utf-8编码LOAD DATA语句里加CHARACTER SET utf8mb4。如果已经乱码只能重新导入因为乱码后的数据无法逆向恢复。5.3 唯一索引冲突导致导入中断现象LOAD DATA执行到一半报Duplicate entry错误后续行全部跳过。原因Excel里有重复BIN或者之前已经导入过部分数据。解决导入前先用GROUP BY bin HAVING COUNT(*) 1筛出重复行人工确认后去重。如果不想中断导入可以在LOAD DATA里加IGNORE关键字但这样会静默跳过重复行需要导入后核对行数。5.4 卡号长度范围值导致查询报错现象业务查询里用card_len做数值比较时报Truncated incorrect DOUBLE value。原因卡号长度列存的是“16-19”这种字符串直接和数字比较会触发隐式转换。解决建表时就拆成card_len_min和card_len_max两个数值字段查询时用BETWEEN。如果已经存了字符串用SUBSTRING_INDEX拆分后更新。5.5 增量更新时覆盖了人工修正的数据现象每次增量合并后之前人工修正过的发卡行名称又被旧数据覆盖了。原因ON DUPLICATE KEY UPDATE会无条件覆盖所有字段。解决在表里加一个manual_verified标记字段合并时用IF(manual_verified 1, bank_name, VALUES(bank_name))保留人工修正值。这个字段平时不用但关键时刻能当后悔药用。6. 进阶技巧把BIN库变成可验证、可回滚的数据资产6.1 用校验和验证BIN数据的完整性BIN数据导入后除了行数核对还可以用校验和做一次完整性验证。具体做法是对BIN列按顺序拼接后取MD5和Excel源文件的MD5对比。如果一致说明导入过程中没有丢行或改行。下面这段Python可以直接用import pandas as pd import hashlib df pd.read_excel(bin_2020.xlsx, dtype{BIN: str}) bins .join(df[BIN].sort_values().tolist()) md5 hashlib.md5(bins.encode(utf-8)).hexdigest() print(源文件BIN校验和:, md5) # 从MySQL导出后再算一次 # SELECT GROUP_CONCAT(bin ORDER BY bin SEPARATOR ) FROM bank_bin;这个技巧的好处是不管中间经过多少次转换只要校验和一致就能确认数据没丢。我一般会把校验和写进导入日志方便后续排查。6.2 用版本表做可回滚的增量更新增量更新最大的风险是更新错了没法回滚。我的做法是每次更新前把当前表快照到一张版本表里版本表命名用bank_bin_20240101这种日期后缀。更新出错时直接RENAME TABLE换回来就行。快照用一条SQL就能完成CREATE TABLE bank_bin_20240101 AS SELECT * FROM bank_bin;版本表不需要索引所以创建很快。保留最近3到5个版本就够了太老的版本定期清理。这个习惯让我在一次误操作后5分钟内恢复了数据比从备份恢复快得多。6.3 把BIN查询封装成应用层缓存BIN数据的特点是读多写少而且单条记录很小非常适合放在应用层缓存里。我一般用本地缓存加定期刷新而不是每次查MySQL。下面是一个简单的Python缓存实现import pymysql import time class BinCache: def __init__(self, refresh_interval3600): self.cache {} self.last_refresh 0 self.refresh_interval refresh_interval def _load(self): conn pymysql.connect(hostlocalhost, userroot, password, databasebin_db) with conn.cursor() as cur: cur.execute(SELECT bin, bank_name, card_org FROM bank_bin) for row in cur.fetchall(): self.cache[row[0]] {bank_name: row[1], card_org: row[2]} conn.close() self.last_refresh time.time() def query(self, card_no): if time.time() - self.last_refresh self.refresh_interval: self._load() for length in (8, 6): prefix card_no[:length] if prefix in self.cache: return self.cache[prefix] return None这个缓存的逻辑是每小时刷新一次全量数据查询时先试8位再试6位。参数refresh_interval可以根据数据更新频率调整如果BIN数据每月才更新一次设成86400也没问题。缓存的好处是把查询耗时从毫秒级降到微秒级坏处是内存占用10万条BIN大约占20MB内存对大多数服务来说可以接受。6.4 一个我踩过的坑不要用BIN做唯一身份标识最后说一个血泪经验。早期我做风控系统时曾经把BIN当成发卡行的唯一标识结果后来发现同一家发卡行有多个BIN同一个BIN也可能因为卡种不同而对应不同规则。BIN只能用来做卡种和发卡行的初步识别不能当业务主键。如果你的系统里用BIN做了唯一约束趁早改成用发卡行标识加卡种组合。这个坑我踩过一次排查了两天才定位到问题希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/10/18 10:22:24 · 阅读 1.2k 银行卡BIN数据从Excel到MySQL：导入、查询与维护实战
简介这份资源是银联官方于2020年4月25日发布的银行卡BIN数据合集面向支付系统开发、金融风控、银行卡识别与清算对账等场景的工程师和数据分析人员可解决BIN查询、发卡行匹配、卡种判定等基础数据需求。压缩包共7个文件以xls表格和sql脚本为主另含少量系统隐藏文件整体约1.12MB其中Excel表覆盖非标卡、农民工卡、跨行转账卡、单位结算卡及通用卡表等多个维度sql文件已整理为9868条记录可直接导入MySQL使用。数据字段包含银行卡BIN、BIN长度、发卡行、银行卡名、银行卡类型及银行卡长度等官方来源保证了齐全度与准确性。目前已有3491人学习下载适合需要快速搭建BIN库、做卡bin校验或对账系统原型的开发者参考使用。1. 银行卡BIN数据到底存了什么从一串卡号前六位说起很多人第一次接触银行卡BIN数据是因为业务系统里要做卡种识别、发卡行匹配或者风控规则。BIN全称Bank Identification Number也就是卡号前6到8位它决定了这张卡属于哪家发卡机构、是借记卡还是信用卡、卡组织是银联还是其他。2020年前后银联官方发布过一批相对完整的BIN对照数据常见形态就是一份Excel加一个MySQL导入脚本。拿到手之后你会发现真正麻烦的不是数据本身而是怎么把它从Excel干净地灌进MySQL再让业务查询跑得动。这篇笔记就按这个路径走先看清字段结构再落地建表和导入最后把查询和更新机制讲透。适合正在做支付、清结算、风控或者卡管理系统的工程师也适合需要自己维护一份BIN库的团队。2. 先看清Excel里的字段哪些列能直接用哪些必须清洗2.1 一份典型BIN表的列结构与含义银联官方发布的BIN数据通常以Excel形式分发常见列包括BIN号、卡种、发卡行名称、发卡行标识、卡组织、卡类型借记/信用、卡号长度、校验方式等。不同批次的列名可能有细微差异但核心字段基本固定。下面这张表是我整理过多次之后认为最需要关注的列列名是否必填说明常见问题BIN是6到8位数字有的行是8位有的只有6位卡种是如借记卡、信用卡、预付卡存在中英文混用发卡行名称是中文全称有空格和不可见字符发卡行标识否内部编码部分行为空卡组织是银联为主少数行有其他组织卡号长度是通常16到19位有的写成范围如“16-19”校验方式否Luhn等多数为空拿到Excel后不要急着导入先做三件事确认BIN列没有前导零丢失、确认卡号长度列是纯数字还是范围、确认发卡行名称里有没有换行符。前导零丢失是Excel最常见的坑因为Excel会把“012345”当成数字12345。解决办法是在读取时强制按字符串处理。2.2 用Python做一次字段体检在导入之前我一般会先用pandas跑一遍字段体检把异常行筛出来。下面这段代码可以直接抄import pandas as pd # 读取时强制BIN为字符串避免前导零丢失 df pd.read_excel(bin_2020.xlsx, dtype{BIN: str}) # 基础体检 print(总行数:, len(df)) print(BIN列空值:, df[BIN].isna().sum()) print(BIN长度分布:) print(df[BIN].str.len().value_counts()) # 检查是否有非数字字符 bad_bin df[~df[BIN].str.match(r^\d{6,8}$, naFalse)] print(异常BIN行数:, len(bad_bin)) print(bad_bin[[BIN, 发卡行名称]].head(10)) # 检查发卡行名称中的不可见字符 df[发卡行名称] df[发卡行名称].astype(str).str.strip() df[发卡行名称] df[发卡行名称].str.replace(r\s, , regexTrue)这段代码的逻辑很直接先强制BIN为字符串再用正则筛出不符合6到8位纯数字的行最后把发卡行名称里的空白字符全部去掉。参数上唯一需要注意的是dtype{BIN: str}如果不加这一句后面所有关于前导零的处理都是白费。体检完之后把异常行单独导出人工确认不要直接删因为有些异常行可能是数据本身的问题删了会丢信息。2.3 卡号长度列的范围值怎么拆卡号长度列经常写成“16-19”这种范围直接存进MySQL的int字段会报错。我的做法是拆成两个字段card_len_min和card_len_max。如果只有一个值两个字段都填同一个数。这样查询的时候可以用BETWEEN比存字符串灵活得多。拆分逻辑用一行代码就能搞定def parse_len(val): val str(val).strip() if - in val: parts val.split(-) return int(parts[0]), int(parts[1]) return int(val), int(val) df[[card_len_min, card_len_max]] df[卡号长度].apply( lambda x: pd.Series(parse_len(x)) )这里要注意如果卡号长度列里有非数字字符int()会抛异常。所以实际跑之前先加一层try/except把失败的行记下来单独处理。我一般会把失败行写到一个bin_parse_failed.csv里导入完成后再回头看。3. 建MySQL表字段类型和索引怎么定才不翻车3.1 表结构设计BIN做主键还是唯一索引BIN数据的特点是读多写少查询几乎都是按BIN精确匹配或者按发卡行模糊搜索。所以表结构设计的第一原则是BIN列必须走索引。我一般把BIN设为主键因为BIN本身在数据里应该是唯一的。但实际数据里偶尔会有重复BIN这时候主键会报错所以更稳妥的做法是加一个自增id做主键BIN上加唯一索引。下面是我常用的建表语句CREATE TABLE bank_bin ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, bin CHAR(8) NOT NULL COMMENT 卡号前6到8位, card_type VARCHAR(32) DEFAULT NULL COMMENT 卡种, bank_name VARCHAR(128) DEFAULT NULL COMMENT 发卡行名称, bank_code VARCHAR(32) DEFAULT NULL COMMENT 发卡行标识, card_org VARCHAR(32) DEFAULT NULL COMMENT 卡组织, card_category VARCHAR(16) DEFAULT NULL COMMENT 借记/信用, card_len_min TINYINT UNSIGNED DEFAULT NULL, card_len_max TINYINT UNSIGNED DEFAULT NULL, luhn_flag TINYINT DEFAULT 0 COMMENT 是否校验Luhn, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_bin (bin), KEY idx_bank_name (bank_name), KEY idx_card_org (card_org) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT银行卡BIN对照表;字段类型上有几个点值得说bin用CHAR(8)而不是VARCHAR因为长度固定CHAR在索引里效率更高card_len_min和card_len_max用TINYINT UNSIGNED因为卡号长度不会超过255bank_name上加了普通索引因为按发卡行搜索是常见需求。字符集用utf8mb4避免发卡行名称里有生僻字导致导入失败。3.2 用LOAD DATA LOCAL INFILE批量导入Excel转成CSV之后最快的导入方式是LOAD DATA LOCAL INFILE。但这里有个坑MySQL 8.0默认关闭了local_infile需要先在服务端和客户端都打开。下面是我常用的导入流程# 1. Excel转CSV注意用utf-8编码 python -c import pandas as pd df pd.read_excel(bin_2020.xlsx, dtype{BIN: str}) df.to_csv(bin_2020.csv, indexFalse, encodingutf-8) # 2. 登录MySQL开启local_infile mysql -u root -p --local-infile1-- 3. 服务端开启 SET GLOBAL local_infile 1; -- 4. 导入 LOAD DATA LOCAL INFILE bin_2020.csv INTO TABLE bank_bin CHARACTER SET utf8mb4 FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (bin, card_type, bank_name, bank_code, card_org, card_category, card_len_min, card_len_max) SET luhn_flag 0;这段导入语句里IGNORE 1 LINES跳过CSV表头SET luhn_flag 0给所有行一个默认值。如果CSV里有空值MySQL会按字段默认值处理所以建表时DEFAULT NULL很重要。导入完成后用SELECT COUNT(*)核对行数和Excel行数对不上就说明有行被截断或者报错被跳过。3.3 导入后必做的三项校验导入完成不代表数据可用我一般会跑三个校验查询-- 校验1BIN长度是否都在6到8位 SELECT COUNT(*) FROM bank_bin WHERE CHAR_LENGTH(bin) NOT BETWEEN 6 AND 8; -- 校验2发卡行名称为空的行 SELECT COUNT(*) FROM bank_bin WHERE bank_name IS NULL OR bank_name ; -- 校验3重复BIN检查理论上唯一索引会拦住但导入时可能被忽略 SELECT bin, COUNT(*) AS cnt FROM bank_bin GROUP BY bin HAVING cnt 1;校验1如果结果不为0说明有BIN长度异常需要回Excel里找原因。校验2的结果通常是0因为发卡行名称是核心字段。校验3在唯一索引生效的情况下也应该是0但如果导入时用了IGNORE关键字重复行会被静默跳过这时候要对比导入前后的行数差。4. 查询与更新BIN库上线后怎么维护才不变成黑匣子4.1 按卡号前缀匹配BIN的两种写法业务系统里最常见的查询是给一个完整卡号返回发卡行信息。因为BIN可能是6位也可能是8位所以匹配逻辑要兼容两种长度。我一般用两种写法按性能需求选-- 写法1精确匹配6位和8位 SELECT * FROM bank_bin WHERE bin LEFT(6222021234567890, 6) OR bin LEFT(6222021234567890, 8) LIMIT 1; -- 写法2用LIKE前缀匹配数据量大时不推荐 SELECT * FROM bank_bin WHERE 6222021234567890 LIKE CONCAT(bin, %) ORDER BY CHAR_LENGTH(bin) DESC LIMIT 1;写法1的问题是如果BIN库里同时存在6位和8位的记录可能返回两条需要业务层再判断。写法2用LIKE会导致全表扫描数据量超过10万行就明显变慢。我的做法是在应用层先取卡号前8位再依次尝试8位和6位精确查询这样能走唯一索引性能最好。4.2 增量更新新BIN怎么合并进现有表银联的BIN数据不是一成不变的新发卡行和新卡种会不断出现。增量更新的常见做法是把新数据导入一张临时表然后用INSERT ... ON DUPLICATE KEY UPDATE合并。下面是我常用的合并语句-- 创建临时表并导入新数据 CREATE TABLE bank_bin_tmp LIKE bank_bin; LOAD DATA LOCAL INFILE bin_2021_new.csv INTO TABLE bank_bin_tmp FIELDS TERMINATED BY , ENCLOSED BY LINES TERMINATED BY \n IGNORE 1 LINES (bin, card_type, bank_name, bank_code, card_org, card_category, card_len_min, card_len_max); -- 合并BIN存在则更新不存在则插入 INSERT INTO bank_bin (bin, card_type, bank_name, bank_code, card_org, card_category, card_len_min, card_len_max) SELECT bin, card_type, bank_name, bank_code, card_org, card_category, card_len_min, card_len_max FROM bank_bin_tmp ON DUPLICATE KEY UPDATE card_type VALUES(card_type), bank_name VALUES(bank_name), bank_code VALUES(bank_code), card_org VALUES(card_org), card_category VALUES(card_category), card_len_min VALUES(card_len_min), card_len_max VALUES(card_len_max), updated_at CURRENT_TIMESTAMP; -- 清理临时表 DROP TABLE bank_bin_tmp;这里的关键是ON DUPLICATE KEY UPDATE依赖唯一索引uk_bin所以建表时那个唯一索引不能省。另外VALUES()函数在MySQL 8.0.20之后被标记为废弃新版本可以用别名写法但为了兼容性我一般还是用VALUES()。4.3 用视图封装常用查询如果业务系统频繁按发卡行或者卡组织做统计可以建一个视图把常用字段组合起来减少重复SQLCREATE VIEW v_bin_summary AS SELECT bin, bank_name, card_org, card_category, CONCAT(card_len_min, -, card_len_max) AS card_len_range FROM bank_bin WHERE bank_name IS NOT NULL;视图的好处是业务方不用关心底层字段变化坏处是视图查询不能走索引优化。所以视图只适合做展示层核心匹配逻辑还是要直接查表。5. 避坑与排查BIN数据导入和查询中最容易翻车的5个点5.1 前导零丢失导致BIN匹配不上现象导入后发现某些BIN在业务系统里查不到但Excel里明明有。原因Excel把BIN列当成数字前导零被吃掉比如“012345”变成“12345”。解决读取Excel时强制dtype{BIN: str}导入MySQL时用CHAR类型而不是INT。如果已经导入了错误数据用LPAD(bin, 6, 0)批量修复。5.2 字符集不一致导致发卡行名称乱码现象导入后发卡行名称显示为问号或者乱码。原因CSV文件是GBK编码MySQL表是utf8mb4导入时没有指定字符集。解决Excel转CSV时统一用utf-8编码LOAD DATA语句里加CHARACTER SET utf8mb4。如果已经乱码只能重新导入因为乱码后的数据无法逆向恢复。5.3 唯一索引冲突导致导入中断现象LOAD DATA执行到一半报Duplicate entry错误后续行全部跳过。原因Excel里有重复BIN或者之前已经导入过部分数据。解决导入前先用GROUP BY bin HAVING COUNT(*) 1筛出重复行人工确认后去重。如果不想中断导入可以在LOAD DATA里加IGNORE关键字但这样会静默跳过重复行需要导入后核对行数。5.4 卡号长度范围值导致查询报错现象业务查询里用card_len做数值比较时报Truncated incorrect DOUBLE value。原因卡号长度列存的是“16-19”这种字符串直接和数字比较会触发隐式转换。解决建表时就拆成card_len_min和card_len_max两个数值字段查询时用BETWEEN。如果已经存了字符串用SUBSTRING_INDEX拆分后更新。5.5 增量更新时覆盖了人工修正的数据现象每次增量合并后之前人工修正过的发卡行名称又被旧数据覆盖了。原因ON DUPLICATE KEY UPDATE会无条件覆盖所有字段。解决在表里加一个manual_verified标记字段合并时用IF(manual_verified 1, bank_name, VALUES(bank_name))保留人工修正值。这个字段平时不用但关键时刻能当后悔药用。6. 进阶技巧把BIN库变成可验证、可回滚的数据资产6.1 用校验和验证BIN数据的完整性BIN数据导入后除了行数核对还可以用校验和做一次完整性验证。具体做法是对BIN列按顺序拼接后取MD5和Excel源文件的MD5对比。如果一致说明导入过程中没有丢行或改行。下面这段Python可以直接用import pandas as pd import hashlib df pd.read_excel(bin_2020.xlsx, dtype{BIN: str}) bins .join(df[BIN].sort_values().tolist()) md5 hashlib.md5(bins.encode(utf-8)).hexdigest() print(源文件BIN校验和:, md5) # 从MySQL导出后再算一次 # SELECT GROUP_CONCAT(bin ORDER BY bin SEPARATOR ) FROM bank_bin;这个技巧的好处是不管中间经过多少次转换只要校验和一致就能确认数据没丢。我一般会把校验和写进导入日志方便后续排查。6.2 用版本表做可回滚的增量更新增量更新最大的风险是更新错了没法回滚。我的做法是每次更新前把当前表快照到一张版本表里版本表命名用bank_bin_20240101这种日期后缀。更新出错时直接RENAME TABLE换回来就行。快照用一条SQL就能完成CREATE TABLE bank_bin_20240101 AS SELECT * FROM bank_bin;版本表不需要索引所以创建很快。保留最近3到5个版本就够了太老的版本定期清理。这个习惯让我在一次误操作后5分钟内恢复了数据比从备份恢复快得多。6.3 把BIN查询封装成应用层缓存BIN数据的特点是读多写少而且单条记录很小非常适合放在应用层缓存里。我一般用本地缓存加定期刷新而不是每次查MySQL。下面是一个简单的Python缓存实现import pymysql import time class BinCache: def __init__(self, refresh_interval3600): self.cache {} self.last_refresh 0 self.refresh_interval refresh_interval def _load(self): conn pymysql.connect(hostlocalhost, userroot, password, databasebin_db) with conn.cursor() as cur: cur.execute(SELECT bin, bank_name, card_org FROM bank_bin) for row in cur.fetchall(): self.cache[row[0]] {bank_name: row[1], card_org: row[2]} conn.close() self.last_refresh time.time() def query(self, card_no): if time.time() - self.last_refresh self.refresh_interval: self._load() for length in (8, 6): prefix card_no[:length] if prefix in self.cache: return self.cache[prefix] return None这个缓存的逻辑是每小时刷新一次全量数据查询时先试8位再试6位。参数refresh_interval可以根据数据更新频率调整如果BIN数据每月才更新一次设成86400也没问题。缓存的好处是把查询耗时从毫秒级降到微秒级坏处是内存占用10万条BIN大约占20MB内存对大多数服务来说可以接受。6.4 一个我踩过的坑不要用BIN做唯一身份标识最后说一个血泪经验。早期我做风控系统时曾经把BIN当成发卡行的唯一标识结果后来发现同一家发卡行有多个BIN同一个BIN也可能因为卡种不同而对应不同规则。BIN只能用来做卡种和发卡行的初步识别不能当业务主键。如果你的系统里用BIN做了唯一约束趁早改成用发卡行标识加卡种组合。这个坑我踩过一次排查了两天才定位到问题希望帮到你。本文还有配套的精品资源点击获取