外部系统或者前端页面过来的数字看着像数字落到库里就露馅有的带千分位逗号有的写成 1.5E3有的小数位悄悄多了两位还有的整数部分直接超过列定义。这时候Oracle正则判断数据就派上用场了——用REGEXP_LIKE在入库前把格式卡死明确限制整数位数、限制小数位数比事后再去捞脏数据省力得多。这篇内容想聊的就是这件事怎么把一句整数最多几位、小数最多几位翻译成一条能落地的正则怎么把它挂到CHECK约束上以及为什么有些时候我压根不写正则而是靠列类型解决。基础到能照着抄深到能解释每个符号为什么这么写。适合做数据迁移、接口对接、ETL 清洗以及被ORA-01722和静默四舍五入折磨过的朋友。1. 位数限制这件事先想清楚再写正则1.1 三类需求纯整数、定点小数、必须带几位小数很多人一上来就问数字的正则怎么写这个问题没法回答因为数字在业务里有好几种形态各自的约束完全不同。我一般先把需求归到三类里归类清楚了正则基本就出来了。第一类是纯整数只有数字和可选的符号位不允许小数点。典型场景是数量、序号、年份、手机号里的某一段。它的正则是三类里最简单的^[0-9]$。如果要限制位数就是^[0-9]{1,10}$。第二类是定点小数小数位最多几位。也就是123、123.4、123.45都合法123.456不合法。这是金额、重量、单价这类字段最常见的形态。注意最多这个词它意味着小数点和小数部分整体是可选的正则里必须用(\.\d{1,2})?这种可选分组不能写死。第三类是定点小数小数位正好几位。也就是必须带小数点且小数位固定123不合法必须写123.00。做财务对账、导出固定格式文件时经常有这种要求因为它能保证每行的字符宽度一致省掉大量格式化代码。这三类的正则骨架差别就在小数段那一个分组上但语义差别很大。我遇到过需求方说小数两位结果开发理解成最多两位上线后发现100.5也能存报表口径全乱了。所以写正则之前先跟需求方把最多和正好这两个词确认清楚这是最容易返工的地方。1.2 为什么我第一眼看的是列类型而不是正则这一点是我想重点说的因为它决定了很多工作是不是白做。如果目标列本身是NUMBER(12,2)那你其实已经用类型表达了整数最多 10 位、小数正好 2 位这个约束NUMBER会在入库时自动做两件事多余的位数四舍五入超出的整数位报错。问题就出在前半句——它是静默的。你往NUMBER(12,2)里塞1.235Oracle 不报错直接给你存成1.24。塞1.2399999存成1.24。整个过程没有任何提示等到月底对账差了三分钱回头查已经很难定位了。所以我的判断顺序是这样的如果业务允许四舍五入那用NUMBER(p,s)就够了不需要正则如果业务不允许静默舍入必须原样拒绝那正则才有存在意义。金融、计费、库存这类场景基本都属于后者。还有一种情况是列必须用VARCHAR2存。原因很多比如这个字段要保持原始输入的字符形态前导零有意义像工号000123或者它是从外部文件原样落库、后面还要原样抛出。这种时候类型帮不了你CHECK约束加正则就是唯一防线。我自己的习惯是能用类型解决的绝不上正则因为类型约束是原生的性能零损耗还自带索引友好只有在类型表达不了比如禁止前导零、禁止科学计数法、禁止正号或者不能容忍静默舍入时才补一条正则。把这个判断放在动手之前能省掉一大半无效工作。2. 把一条正则拆到不能再拆2.1 锚点、符号位与整数段的三处取舍拿一个最典型的场景举例金额字段整数部分最多 10 位小数部分最多 2 位允许负数。基础版正则是这样^[-]?[0-9]{1,10}(\.[0-9]{1,2})?$这条正则一共四个部分每个部分都有取舍逐个说。开头的^和结尾的$是必须的这是最容易漏的一步。不写锚点REGEXP_LIKE(abc123def, [0-9]{1,10})会返回 TRUE因为它只要在字符串里找到一段连续数字就算匹配。做格式校验必须两头锁死否则等于没校验。符号位[-]?里的问号表示可有可无。这里有个业务决策要不要允许号我一般不允许因为123和123在业务上是同一个值允许只会让后续的比较、去重、拼接多出一堆分支。所以实际我写的是-?。另外-0这种形态正则层面是放过的要不要拦看业务图省事可以后面单独加一条col -0。整数段[0-9]{1,10}用的是{1,10}而不是。为什么要写上限因为不加限制的话123456789012345也能通过落库时才在NUMBER那边炸掉错误信息是ORA-01438跟格式问题混在一起排查时很烦。在正则里一次性卡死错误定位更准。为什么用[0-9]而不是\dOracle 的REGEXP_LIKE确实认\d但它是 Perl 风格的扩展不属于 POSIX 标准字符类。跨数据库迁移脚本、或者别人用别的工具读你的正则时\d可能不被识别。[0-9]是硬编码字符集任何引擎都认而且不依赖 NLS 设置这是它相对[[:digit:]]的另一个优势——[[:digit:]]在某些语言环境下会把全角数字也算进去而全角数字恰恰是我想拦的对象。所以[0-9]是我的默认选择。再补一个严格版如果你还要禁止前导零007、0123这类不合法^-?(0|[1-9][0-9]{0,9})(\.[0-9]{1,2})?$这里的(0|[1-9][0-9]{0,9})是个小巧思要么整个整数部分就是一个0要么第一位是1-9、后面跟 0 到 9 位。这样既保证了0和0.5合法又堵死了00和0123。代价是长度变成两段理解成本稍高所以我一般只在明确有禁止前导零需求时才用。2.2 小数段的两种写法最多 N 位与正好 N 位小数段是区分最多和正好的地方写错了整个约束的语义就变了。最多 N 位(\.[0-9]{1,N})?注意这个分组外面那个?它让整个小数部分变成可选所以123能过。里面的{1,N}表示小数点后至少有 1 位、最多 N 位所以123.点后没数字不匹配.5点前没数字也不匹配——因为整数段要求至少一位。正好 N 位\.[0-9]{N}这里既没有?也没有{1,}意思是小数点必须存在、后面必须正好 N 位。注意不能加问号加了就退化成最多 N 位了。把两种需求分别代进去需求描述正则说明整数 1-10 位小数最多 2 位可负^-?[0-9]{1,10}(\.[0-9]{1,2})?$最常用123/0.5/-1.23都过整数 1-10 位小数正好 2 位可负^-?[0-9]{1,10}\.[0-9]{2}$123不过必须写123.00纯整数1-10 位可负^-?[0-9]{1,10}$不许有小数点正整数1-6 位^[1-9][0-9]{0,5}$排除 0 和前导零禁止前导零整数 1-10 位小数最多 2 位^-?(0|[1-9][0-9]{0,9})(\.[0-9]{1,2})?$严格版金额8 位整数 固定 2 位小数^-?(0|[1-9][0-9]{0,7})\.[0-9]{2}$财务导出常用表格里第六行那个正则我在做对账文件生成时用得最多。它同时管住了前导零、位数、小数位固定三件事一条正则顶三条校验。还有个小边界值得单独提如果要允许0.5但不允许00.5上面的严格版已经处理了。如果要允许小数点前为空.5那整数段要改成([0-9]{1,10})?但我不建议这么放开因为.5这种写法在后续TO_NUMBER转换、字符串比较、导出 CSV 时都会各平台表现不一致规范成0.5更省事。真遇到这种脏数据宁可在 ETL 阶段先规范一遍再校验。2.3 NUMBER(p,s) 到底对应几位整数一张换算表NUMBER(p,s)的p是总有效位数s是小数位数p和s的差值才是整数位。这个换算我见过太多人算错直接把p当整数位用结果列定义偏小数据一多就开始ORA-01438。公式很简单整数位上限 p - s举几个实际的列定义整数位上限小数位对应正则最多形态NUMBER(10,2)82^-?[0-9]{1,8}(\.[0-9]{1,2})?$NUMBER(12,2)102^-?[0-9]{1,10}(\.[0-9]{1,2})?$NUMBER(18,4)144^-?[0-9]{1,14}(\.[0-9]{1,4})?$NUMBER(5)50^-?[0-9]{1,5}$NUMBER(1,0)10^[0-9]$1 位整数NUMBER(38,10)2810^-?[0-9]{1,28}(\.[0-9]{1,10})?$这里有个坑NUMBER(10,2)能存的最大值是99999999.99不是9999999999.99。如果你按p写正则写成{1,10}正则放过了9999999999落库时 Oracle 会报ORA-01438因为整数部分超了。所以正则的整数位必须等于p - s不能等于p。反过来也有个陷阱NUMBER(10,2)存-99999999.99是合法的负号不占位数。所以正则里符号位和位数限制要分开-?写在外面{1,8}只管数字部分。有人把符号也算进长度里结果负数全被拒这种低级错误我见过不止一次。如果你的位数是配置项不同客户精度要求不一样可以把正则拼出来。Oracle 里拼正则没什么特别的就是字符串连接CREATE OR REPLACE FUNCTION f_num_pattern ( p_int_len IN NUMBER, p_dec_len IN NUMBER, p_allow_sign IN VARCHAR2 DEFAULT Y ) RETURN VARCHAR2 IS l_pat VARCHAR2(200); BEGIN l_pat : ^; IF p_allow_sign Y THEN l_pat : l_pat || -?; END IF; l_pat : l_pat || [0-9]{1, || p_int_len || }; IF p_dec_len 0 THEN l_pat : l_pat || (\.[0-9]{1, || p_dec_len || })?; END IF; RETURN l_pat || $; END; /调用f_num_pattern(10, 2)返回^-?[0-9]{1,10}(\.[0-9]{1,2})?$。这种函数化写法在配置化的校验框架里特别有用不用每次改精度都去翻代码改字符串常量。不过要注意如果拼接的参数来自用户输入得做类型校验p_int_len声明成NUMBER本身就是一层保护——传非数字进去在参数绑定阶段就报错了。3. 从建表到约束完整跑一遍3.1 测试表和覆盖边界的样本数据光写正则不够得跑一遍才知道有没有漏。我习惯先建一张临时表把所有能想到的边界样本都塞进去跑一遍看结果对不对再决定要不要挂到正式表上。CREATE TABLE t_num_check ( id NUMBER GENERATED ALWAYS AS IDENTITY, raw_val VARCHAR2(100), memo VARCHAR2(200) ); INSERT ALL INTO t_num_check (raw_val, memo) VALUES (123, 普通整数) INTO t_num_check (raw_val, memo) VALUES (1234567890, 整数刚好10位) INTO t_num_check (raw_val, memo) VALUES (12345678901, 整数11位应拒) INTO t_num_check (raw_val, memo) VALUES (-123, 负数) INTO t_num_check (raw_val, memo) VALUES (123, 正号开头) INTO t_num_check (raw_val, memo) VALUES (0, 零) INTO t_num_check (raw_val, memo) VALUES (0.00, 零带小数) INTO t_num_check (raw_val, memo) VALUES (1.5, 一位小数) INTO t_num_check (raw_val, memo) VALUES (1.23, 两位小数) INTO t_num_check (raw_val, memo) VALUES (1.234, 三位小数应拒) INTO t_num_check (raw_val, memo) VALUES (1., 点后无数字应拒) INTO t_num_check (raw_val, memo) VALUES (.5, 点前无数字应拒) INTO t_num_check (raw_val, memo) VALUES (00, 前导零) INTO t_num_check (raw_val, memo) VALUES (0123, 前导零) INTO t_num_check (raw_val, memo) VALUES (1e5, 科学计数法应拒) INTO t_num_check (raw_val, memo) VALUES (1.5E3, 科学计数法应拒) INTO t_num_check (raw_val, memo) VALUES (1,234, 千分位应拒) INTO t_num_check (raw_val, memo) VALUES ( 123, 前导空格应拒) INTO t_num_check (raw_val, memo) VALUES (123 , 尾随空格应拒) INTO t_num_check (raw_val, memo) VALUES (, 全角数字应拒) INTO t_num_check (raw_val, memo) VALUES (, 空串即NULL) INTO t_num_check (raw_val, memo) VALUES (NULL, NULL) SELECT 1 FROM dual;样本设计有几个原则。一是边界值要卡在临界点上所以123456789010 位和1234567890111 位都放进去只有这样才能验证{1,10}是不是真的按预期工作。二是每种看起来像数字但不是的形态都要有样本科学计数法、千分位、空格、全角数字这四个是最容易漏的。三是空串和 NULL 都要有因为在 Oracle 里就是NULL两者的行为需要实测确认。3.2 逐条跑正则对照预期结果基础版的校验跑起来很简单SELECT id, memo, raw_val, CASE WHEN REGEXP_LIKE(raw_val, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN PASS ELSE REJECT END AS basic_result, CASE WHEN REGEXP_LIKE(raw_val, ^-?(0|[1-9][0-9]{0,9})(\.[0-9]{1,2})?$) THEN PASS ELSE REJECT END AS strict_result FROM t_num_check ORDER BY id;跑完之后逐条对我按实际行为整理成表重点看三个意外样本基础版严格版说明123PASSPASS正常1234567890PASSPASS临界10 位12345678901REJECTREJECT临界11 位符合预期-123PASSPASS负号不占位123REJECTREJECT我用的-?正号被拒0/0.00PASSPASS正常1.5PASSPASS一位小数最多两位允许1.234REJECTREJECT三位小数被拒1.REJECTREJECT点后无数字.5REJECTREJECT点前无数字00/0123PASSREJECT基础版放过前导零这是两个版本的唯一实质差异1e5/1.5E3REJECTREJECT科学计数法被拒1,234REJECTREJECT千分位被拒123/123REJECTREJECT空格被拒REJECTREJECT全角数字被拒/NULLNULLNULL结果为 NULL不是 TRUE 也不是 FALSE两个NULL结果要特别说明。REGEXP_LIKE(NULL, ...)返回NULL在WHERE子句里NULL不算通过所以查非法数据时会被漏掉但在CHECK约束里NULL被视为不违反约束所以能通过。这个不对称是 Oracle 三值逻辑的基本行为但很多人第一次遇到会懵。再看前导零那一行这是基础版和严格版的唯一区别。要不要禁止前导零取决于字段语义如果这个字段是金额0123和123是同一个值禁止更好如果这个字段是编号前导零有意义那就必须允许。我一般默认用基础版只在明确需要时才切严格版——因为基础版误杀的概率更低CHECK约束误杀存量数据的代价是ALTER TABLE直接失败。还有个额外的保险我通常会加上长度校验SELECT id, memo, raw_val, CASE WHEN REGEXP_LIKE(raw_val, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) AND LENGTH(raw_val) 14 THEN PASS ELSE REJECT END AS result FROM t_num_check;LENGTH上限怎么算最长的合法形态是- 10 位整数 . 2 位小数 14 个字符。加上这道校验能挡住一种特殊情况字符串末尾带换行符CHR(10)时$在某些情况下可能匹配到换行符之前的位置导致123||CHR(10)意外通过。这个行为在不同版本、不同客户端下表现不一定一致我不想去赌直接用长度兜底更踏实。更彻底的做法是在校验前先清洗REGEXP_LIKE(REPLACE(REPLACE(raw_val, CHR(10)), CHR(13)), ^-?[0-9]{1,10}(\.[0-9]{1,2})?$)清洗和长度校验二选一即可两个都上也不亏成本很低。3.3 落成 CHECK 约束与通用校验函数验证通过之后就可以挂到正式表上了。最直接的方式是CHECK约束ALTER TABLE t_amount ADD CONSTRAINT ck_amount_format CHECK (REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$));加之前必须做一件事先查出存量非法数据否则ALTER TABLE会直接抛ORA-02293无法验证约束在几千万行的表上这个报错能让你等半天再白等一场。SELECT COUNT(*) FROM t_amount WHERE NOT REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) OR amount_str IS NULL;注意这里的写法NOT REGEXP_LIKE(...)对NULL返回NULLWHERE里不成立所以NULL行不会被选出来。想要连NULL一起查必须显式加OR ... IS NULL。这个细节决定了你查出来的非法数据到底完不完整。还有一个选择是ENABLE NOVALIDATE让约束只对新增数据生效、跳过存量校验ALTER TABLE t_amount ADD CONSTRAINT ck_amount_format CHECK (REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$)) ENABLE NOVALIDATE;这个做法在上线维度的取舍很明显好处是立刻能加、不用等全表扫描坏处是存量脏数据依然在后面清洗时还要单独处理。我的经验是迁移场景用NOVALIDATE先止血业务系统新建表直接VALIDATE因为新表没存量没理由留口子。如果校验逻辑比较复杂比如要同时返回哪里不合法那就封装成函数。返回NULL表示通过返回字符串表示具体原因CREATE OR REPLACE FUNCTION f_check_num_format ( p_val IN VARCHAR2, p_int_len IN NUMBER, p_dec_len IN NUMBER, p_leading0 IN VARCHAR2 DEFAULT N ) RETURN VARCHAR2 IS l_pat VARCHAR2(200); BEGIN IF p_val IS NULL THEN RETURN EMPTY: 值为空; END IF; -- 长度快速预筛避免明显超长的串走正则 IF LENGTH(p_val) p_int_len p_dec_len 3 THEN RETURN LENGTH: 总长度超出预期; END IF; IF p_leading0 Y THEN l_pat : ^-?(0|[1-9][0-9]{0, || (p_int_len - 1) || }); ELSE l_pat : ^-?[0-9]{1, || p_int_len || }; END IF; IF p_dec_len 0 THEN l_pat : l_pat || (\.[0-9]{1, || p_dec_len || })?; END IF; l_pat : l_pat || $; IF NOT REGEXP_LIKE(p_val, l_pat) THEN RETURN FORMAT: 不匹配 || l_pat; END IF; RETURN NULL; END; /这个函数有几个设计考虑值得说。第一先做NULL判断因为REGEXP_LIKE对NULL返回NULL如果直接用IF NOT REGEXP_LIKE(...)判断NULL会走到NOT NULL还是NULL的逻辑里结果不符合直觉不如先拦掉。第二先做长度判断再走正则长度判断是 O(1) 级别的操作能在大量超长脏数据上省下不少正则引擎的开销。第三错误码带前缀EMPTY、LENGTH、FORMAT后面做批量清洗时按前缀分组统计一眼就能看出是哪类问题占多数。用的时候SELECT id, raw_val, f_check_num_format(raw_val, 10, 2, Y) AS err FROM t_num_check;要提醒一点这个函数在大表批量校验时性能不如纯 SQL 正则。原因是 PL/SQL 函数在 SQL 里逐行调用会有上下文切换开销一百万行能差出好几倍。所以函数适合做小批量或单条校验比如前端提交过来的一行数据大表批量校验还是老老实实用WHERE NOT REGEXP_LIKE(...)让优化器在 SQL 层跑完。这个分工我在好几个项目里都是这么用的。4. 正则之外性能和替代路径怎么选4.1 大表上正则的代价到底在哪正则不是免费的。REGEXP_LIKE每次调用都要启动正则引擎做编译和匹配虽然 Oracle 对同一个 pattern 有缓存优化但在千万行级别的表上做全表校验开销还是很可观。更麻烦的是正则表达式不能走普通索引WHERE REGEXP_LIKE(col, ...)必然是全表扫描这一点跟LIKE还不一样——LIKE abc%在某些情况下是能用索引的REGEXP_LIKE则完全没有这个待遇。所以性能优化的方向不是把正则写得更快而是**让正则跑的次数变少**。我常用的做法有三层。第一层是缩短输入。在正则之前先用便宜的判断筛一遍比如LENGTH(col) 14、col IS NOT NULL把明显不可能的数据先剔除。这类判断不需要启动正则引擎成本可以忽略。第二层是减少执行频率。校验放在 ETL 的入口而不是出口数据进仓时就卡住后面所有环节都不用再校验。如果数据是分批进来的那就每批校验一次别等到全量入库完再扫一遍全表。第三层是用函数索引或虚拟列把结果持久化。这个下面细说。我的经验是单表百万行以内的校验正则的开销完全可以接受跑一次几秒钟不需要过度优化千万行以上就要考虑持久化方案了否则每次校验都是一次全表扫描跑多了会明显影响业务。这个分界不是绝对的跟机器配置、并发情况都有关系实际还是要看执行计划里的consistent gets。4.2 函数索引、虚拟列与坏数据标记三种做法函数索引的用法是给非法数据建索引。思路很巧妙让合法行返回NULL非法行返回原值因为 Oracle 的 B-Tree 索引不存全NULL的键所以索引里实际上只有非法行体积很小查起来极快。CREATE INDEX idx_amount_bad ON t_amount ( CASE WHEN REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN NULL ELSE amount_str END );建完之后查非法数据就变成SELECT * FROM t_amount WHERE CASE WHEN REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN NULL ELSE amount_str END IS NOT NULL;注意WHERE里必须一字不差地重复索引表达式否则优化器不认还是走全表扫描。这个限制有点烦但没办法函数索引的匹配是基于表达式文本的。这里有个细节要注意建函数索引时正则里不要用[[:alpha:]]、[[:digit:]]这类依赖 NLS 的字符类否则可能因为会话的 NLS 设置不同导致索引失效或校验结果不一致。用[0-9]就没这个问题这也是我在 2.1 里推荐[0-9]的原因之一不只是可移植性。虚拟列是 12c 之后更清爽的做法把校验结果变成一个真实的列可以索引、可以查询、可以统计ALTER TABLE t_amount ADD ( fmt_flag NUMBER GENERATED ALWAYS AS ( CASE WHEN REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN 1 ELSE 0 END ) VIRTUAL ); CREATE INDEX idx_amount_fmt ON t_amount (fmt_flag);之后查非法数据就是WHERE fmt_flag 0简单直观也方便做非法率统计。虚拟列不占存储值在查询时实时计算代价转移到读的时候写的时候只多一点点 CPU。坏数据标记是最土但也最灵活的做法加一个普通列is_valid在 ETL 的最后一步用一条UPDATE打标。UPDATE t_amount SET is_valid CASE WHEN REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN 1 ELSE 0 END;缺点是这条UPDATE本身很重还得单独提交。优点是打完标之后所有查询都变成了普通条件过滤索引效果最好而且校验结果可以只算一次、反复使用。如果这张表要长期反复查非法数据我倾向用这个方案如果只是偶尔查一次函数索引更省事。三种方案对比方案存储成本查询性能维护成本适用场景实时正则不建索引无全表扫描慢最低一次性校验、小表函数索引CASE 返回 NULL只存非法行极小走索引快中WHERE 要重复表达式偶尔查、非法率低虚拟列 索引列不占存储索引占走索引快中需要 12c要统计、要长期看物理标记列占一列最快最高要跑 UPDATE反复查询、大表4.3 TRANSLATE 预筛和 TO_NUMBER 兜底的边界REGEXP_LIKE不是唯一手段。有些场景用TRANSLATE更快因为它只是字符映射没有正则引擎的开销。TRANSLATE的机制是把from_str里的字符逐个映射成to_str里对应位置的字符to_str比from_str短时多出来的字符直接删除。利用这个特性可以快速判断字符串是不是只含数字和点SELECT raw_val, CASE WHEN TRANSLATE(raw_val, 0123456789.-, ) IS NULL THEN ONLY_NUMERIC_CHARS ELSE HAS_OTHER END AS char_check FROM t_num_check;不过这个写法只能判断字符集合判断不了位数和位置--..这种乱序组合它会放过。所以TRANSLATE的定位是预筛先用它把含字母、含逗号、含空格的脏数据快速剔掉剩下的再交给正则做精细校验。在脏数据比例很高的场景下这个两步走能省不少时间。另一种思路是用TO_NUMBER兜底SELECT raw_val, CASE WHEN REGEXP_LIKE(raw_val, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN FORMAT_OK WHEN raw_val IS NULL THEN NULL ELSE FORMAT_BAD END AS fmt, -- 注意只在格式通过时才尝试转换避免 ORA-01722 CASE WHEN REGEXP_LIKE(raw_val, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN TO_NUMBER(raw_val) END AS num_val FROM t_num_check;这个组合的意图是正则管格式、转换管精度两道关分开。千万不要把TO_NUMBER放在正则前面因为 Oracle 的CASE虽然短路求值但WHERE子句的求值顺序不保证一旦优化器把TO_NUMBER提前执行碰上abc直接ORA-01722整个查询挂掉。还有一个反直觉的事实TO_NUMBER(1,234)在某些NLS_NUMERIC_CHARACTERS设置下是能成功的因为逗号被当成小数点。这意味着用TO_NUMBER做格式校验是完全不可靠的同一个值在不同会话里结果不同。所以格式校验必须是显式的正则或字符级判断不能依赖隐式转换。这一点我在踩过一次坑之后记了很久。最后说一个纯粹从性能出发的对比。做纯整数校验不含小数其实可以完全不用正则-- 方案A正则 WHERE REGEXP_LIKE(v, ^[0-9]{1,10}$) -- 方案BTRANSLATE LENGTH WHERE v IS NOT NULL AND LENGTH(v) BETWEEN 1 AND 10 AND TRANSLATE(v, 0123456789, ) IS NULL方案 B 在两个条件都满足时等价于方案 A但因为没有正则引擎参与在大表上能快不少。我测过的场景里纯整数校验用方案 B 大概能省下可观的 CPU 时间具体倍数跟数据分布和版本有关但方向是确定的。所以我的原则是格式简单纯整数、固定字符集就用TRANSLATE格式复杂可选小数、符号位、分组才上正则。5. 排查实录那些以为拦住了却没拦住的数据5.1 五个最容易漏的输入形态第一个是尾随空格。123 在[0-9]{1,10}$面前是过不了的因为$要求数字后面直接结束。但如果数据从固定宽度文件来解析时补了空格就会全军覆没。这时候别急着改正则先确认数据源是不是需要TRIM。我通常会在校验前统一TRIM因为业务上123 和123就是一个值。第二个是末尾换行符。这个坑我在 3.2 提过123||CHR(10)有可能通过^[0-9]$。原因是$在 POSIX 语义下可以匹配最后一个换行符之前。防御方式是加LENGTH校验或者提前REPLACE掉CHR(10)和CHR(13)。这类脏数据在从文件导入或者从接口报文里截取字段时特别常见而且肉眼看不出来SELECT出来显示得跟正常值一模一样。第三个是全角数字。看着是数字实际上每个字符占两个字节在AL32UTF8下占三字节[0-9]拦得住但如果用了[[:digit:]]就不一定了——某些 NLS 环境下全角数字会被算作 digit。这个差异很难在开发环境复现因为开发机上的 NLS 设置经常和生产的容器不一样。所以再强调一遍字符类用[0-9]。第四个是负零和正号。-0、0、123正则-?只放过了负号号过不去这是符合我预期的。但-0是能过的如果业务上-0和0是同一个值后面做DISTINCT或者GROUP BY就会分成两组。这种事只能靠业务层再兜一道正则层面没必要为了它把逻辑写复杂。第五个是**和NULL的混淆**。在 Oracle 里就是NULL不存在空字符串这个东西。所以任何关于空值该怎么处理的讨论在 Oracle 里其实只有一个问题NULL该不该被拦。CHECK约束默认放过NULLREGEXP_LIKE对NULL返回NULL这两个行为组合起来就是空值永远等于合法。如果业务要求字段必填必须显式加NOT NULL约束不能指望正则。这一点我在新建表时写约束的模板里都固定带上ALTER TABLE t_amount ADD CONSTRAINT ck_amount_notnull CHECK (amount_str IS NOT NULL);5.2 常见问题速查表现象可能原因排查 SQL处理方式123校验不通过字符串里混了不可见字符SELECT DUMP(raw_val) FROM t看DUMP输出的字节TRIM或REPLACE加CHECK约束报ORA-02293存量数据不合规先跑WHERE NOT REGEXP_LIKE(...)统计清洗后再加或改用ENABLE NOVALIDATE约束加了但脏数据还在约束用了NOVALIDATE只对新数据生效查USER_CONSTRAINTS.STATUS确认状态必要时VALIDATE空值全被放过了NULL在CHECK里视为通过SELECT COUNT(*) WHERE col IS NULL单独加NOT NULL1e5竟然通过了正则漏了锚点或用了而不是位数限制单独跑REGEXP_LIKE(1e5, 你的正则)补^$和{1,N}大表校验跑了很久正则无法走索引必然全表扫看执行计划TABLE ACCESS FULL用函数索引或标记列TO_NUMBER(1,234)成功NLS_NUMERIC_CHARACTERS影响SELECT * FROM NLS_SESSION_PARAMETERS不要依赖隐式转换做校验小数被静默截断列是NUMBER(p,s)入库时四舍五入对比原串和落库值用正则前置拦截或改用VARCHAR2整数位超了才报错正则按p写而不是p-s检查正则里整数位的上限改成p - s全角数字漏过用了[[:digit:]]SELECT DUMP(raw_val)改用[0-9]表格里小数被静默截断这一条我想再展开一句因为它是这整件事的核心动机。INSERT INTO t VALUES (1.235)到NUMBER(12,2)的列Oracle 不报错存成1.24。同一行数据用VARCHAR2存就是1.235用NUMBER存就是1.24差异是静默产生的。如果这个值参与结算三分钱的差异会随着行数放大。这就是为什么在对接外部系统时我宁可多一层正则校验也不愿意把格式正确这件事交给类型去猜。5.3 几条踩出来的经验第一条校验和存储分离。我的习惯是保留一份原始的VARCHAR2字符串列再加一份NUMBER类型的解析列。原始列带CHECK约束卡格式解析列供业务计算。这样做的好处是出问题时能追到源头1.235和1.24的差异一眼可见不用去翻日志。第二条CHECK约束里的正则不要写太长。Oracle 对约束表达式的长度有上限虽然一般够用但如果你把一大堆CASE WHEN塞进去很容易超。长逻辑应该拆成多个约束或者封装成函数但函数在约束里要注意确定性REGEXP_LIKE是确定性的可以放心用。第三条正则的 pattern 最好定义成一个常量集中管理。同一条正则散落在CHECK约束、ETL 脚本、应用代码里只要有一处改了其他地方就悄悄失配。我的做法是建一张配置表存 pattern或者用包里的常量所有地方从同一处取。改精度的时候只改一行风险可控。第四条先在测试数据上跑一遍反例集。我会专门维护一个反例清单把历史踩过的坑都记进去每次改正则都拿它跑一遍。这个清单比正例更重要因为正例通常一跑就过反例才是暴露问题的地方。清单里现在有三十多条包括1.2.3、--5、-5、0x1F、1_000这些看起来离谱但真的从各种系统里冒出来的形态。第五条正则匹配的边界要跟业务确认。0.00算不算合法金额-0.01这种小额负数在退款场景是不是正常000.00在前导零严格版下被拒但某些老系统导出的就是这种格式。这些都不是技术问题是业务口径问题技术层面只能提供选项拍板得业务来。我一般会在文档里把正则对应的合法集合写成例子列出来让业务方确认这些形态都能接受避免上线后扯皮。这些原则在业务系统里能成为硬约束在报表系统里也许就只需要在 ETL 里做一次具体怎么取舍还得看这份数据下游被谁用、用错一次的代价有多大。 
长河君 · 2026/11/3 4:46:10 · 阅读 1.2k Oracle正则校验数字格式：整数位与小数位限制实践
外部系统或者前端页面过来的数字看着像数字落到库里就露馅有的带千分位逗号有的写成 1.5E3有的小数位悄悄多了两位还有的整数部分直接超过列定义。这时候Oracle正则判断数据就派上用场了——用REGEXP_LIKE在入库前把格式卡死明确限制整数位数、限制小数位数比事后再去捞脏数据省力得多。这篇内容想聊的就是这件事怎么把一句整数最多几位、小数最多几位翻译成一条能落地的正则怎么把它挂到CHECK约束上以及为什么有些时候我压根不写正则而是靠列类型解决。基础到能照着抄深到能解释每个符号为什么这么写。适合做数据迁移、接口对接、ETL 清洗以及被ORA-01722和静默四舍五入折磨过的朋友。1. 位数限制这件事先想清楚再写正则1.1 三类需求纯整数、定点小数、必须带几位小数很多人一上来就问数字的正则怎么写这个问题没法回答因为数字在业务里有好几种形态各自的约束完全不同。我一般先把需求归到三类里归类清楚了正则基本就出来了。第一类是纯整数只有数字和可选的符号位不允许小数点。典型场景是数量、序号、年份、手机号里的某一段。它的正则是三类里最简单的^[0-9]$。如果要限制位数就是^[0-9]{1,10}$。第二类是定点小数小数位最多几位。也就是123、123.4、123.45都合法123.456不合法。这是金额、重量、单价这类字段最常见的形态。注意最多这个词它意味着小数点和小数部分整体是可选的正则里必须用(\.\d{1,2})?这种可选分组不能写死。第三类是定点小数小数位正好几位。也就是必须带小数点且小数位固定123不合法必须写123.00。做财务对账、导出固定格式文件时经常有这种要求因为它能保证每行的字符宽度一致省掉大量格式化代码。这三类的正则骨架差别就在小数段那一个分组上但语义差别很大。我遇到过需求方说小数两位结果开发理解成最多两位上线后发现100.5也能存报表口径全乱了。所以写正则之前先跟需求方把最多和正好这两个词确认清楚这是最容易返工的地方。1.2 为什么我第一眼看的是列类型而不是正则这一点是我想重点说的因为它决定了很多工作是不是白做。如果目标列本身是NUMBER(12,2)那你其实已经用类型表达了整数最多 10 位、小数正好 2 位这个约束NUMBER会在入库时自动做两件事多余的位数四舍五入超出的整数位报错。问题就出在前半句——它是静默的。你往NUMBER(12,2)里塞1.235Oracle 不报错直接给你存成1.24。塞1.2399999存成1.24。整个过程没有任何提示等到月底对账差了三分钱回头查已经很难定位了。所以我的判断顺序是这样的如果业务允许四舍五入那用NUMBER(p,s)就够了不需要正则如果业务不允许静默舍入必须原样拒绝那正则才有存在意义。金融、计费、库存这类场景基本都属于后者。还有一种情况是列必须用VARCHAR2存。原因很多比如这个字段要保持原始输入的字符形态前导零有意义像工号000123或者它是从外部文件原样落库、后面还要原样抛出。这种时候类型帮不了你CHECK约束加正则就是唯一防线。我自己的习惯是能用类型解决的绝不上正则因为类型约束是原生的性能零损耗还自带索引友好只有在类型表达不了比如禁止前导零、禁止科学计数法、禁止正号或者不能容忍静默舍入时才补一条正则。把这个判断放在动手之前能省掉一大半无效工作。2. 把一条正则拆到不能再拆2.1 锚点、符号位与整数段的三处取舍拿一个最典型的场景举例金额字段整数部分最多 10 位小数部分最多 2 位允许负数。基础版正则是这样^[-]?[0-9]{1,10}(\.[0-9]{1,2})?$这条正则一共四个部分每个部分都有取舍逐个说。开头的^和结尾的$是必须的这是最容易漏的一步。不写锚点REGEXP_LIKE(abc123def, [0-9]{1,10})会返回 TRUE因为它只要在字符串里找到一段连续数字就算匹配。做格式校验必须两头锁死否则等于没校验。符号位[-]?里的问号表示可有可无。这里有个业务决策要不要允许号我一般不允许因为123和123在业务上是同一个值允许只会让后续的比较、去重、拼接多出一堆分支。所以实际我写的是-?。另外-0这种形态正则层面是放过的要不要拦看业务图省事可以后面单独加一条col -0。整数段[0-9]{1,10}用的是{1,10}而不是。为什么要写上限因为不加限制的话123456789012345也能通过落库时才在NUMBER那边炸掉错误信息是ORA-01438跟格式问题混在一起排查时很烦。在正则里一次性卡死错误定位更准。为什么用[0-9]而不是\dOracle 的REGEXP_LIKE确实认\d但它是 Perl 风格的扩展不属于 POSIX 标准字符类。跨数据库迁移脚本、或者别人用别的工具读你的正则时\d可能不被识别。[0-9]是硬编码字符集任何引擎都认而且不依赖 NLS 设置这是它相对[[:digit:]]的另一个优势——[[:digit:]]在某些语言环境下会把全角数字也算进去而全角数字恰恰是我想拦的对象。所以[0-9]是我的默认选择。再补一个严格版如果你还要禁止前导零007、0123这类不合法^-?(0|[1-9][0-9]{0,9})(\.[0-9]{1,2})?$这里的(0|[1-9][0-9]{0,9})是个小巧思要么整个整数部分就是一个0要么第一位是1-9、后面跟 0 到 9 位。这样既保证了0和0.5合法又堵死了00和0123。代价是长度变成两段理解成本稍高所以我一般只在明确有禁止前导零需求时才用。2.2 小数段的两种写法最多 N 位与正好 N 位小数段是区分最多和正好的地方写错了整个约束的语义就变了。最多 N 位(\.[0-9]{1,N})?注意这个分组外面那个?它让整个小数部分变成可选所以123能过。里面的{1,N}表示小数点后至少有 1 位、最多 N 位所以123.点后没数字不匹配.5点前没数字也不匹配——因为整数段要求至少一位。正好 N 位\.[0-9]{N}这里既没有?也没有{1,}意思是小数点必须存在、后面必须正好 N 位。注意不能加问号加了就退化成最多 N 位了。把两种需求分别代进去需求描述正则说明整数 1-10 位小数最多 2 位可负^-?[0-9]{1,10}(\.[0-9]{1,2})?$最常用123/0.5/-1.23都过整数 1-10 位小数正好 2 位可负^-?[0-9]{1,10}\.[0-9]{2}$123不过必须写123.00纯整数1-10 位可负^-?[0-9]{1,10}$不许有小数点正整数1-6 位^[1-9][0-9]{0,5}$排除 0 和前导零禁止前导零整数 1-10 位小数最多 2 位^-?(0|[1-9][0-9]{0,9})(\.[0-9]{1,2})?$严格版金额8 位整数 固定 2 位小数^-?(0|[1-9][0-9]{0,7})\.[0-9]{2}$财务导出常用表格里第六行那个正则我在做对账文件生成时用得最多。它同时管住了前导零、位数、小数位固定三件事一条正则顶三条校验。还有个小边界值得单独提如果要允许0.5但不允许00.5上面的严格版已经处理了。如果要允许小数点前为空.5那整数段要改成([0-9]{1,10})?但我不建议这么放开因为.5这种写法在后续TO_NUMBER转换、字符串比较、导出 CSV 时都会各平台表现不一致规范成0.5更省事。真遇到这种脏数据宁可在 ETL 阶段先规范一遍再校验。2.3 NUMBER(p,s) 到底对应几位整数一张换算表NUMBER(p,s)的p是总有效位数s是小数位数p和s的差值才是整数位。这个换算我见过太多人算错直接把p当整数位用结果列定义偏小数据一多就开始ORA-01438。公式很简单整数位上限 p - s举几个实际的列定义整数位上限小数位对应正则最多形态NUMBER(10,2)82^-?[0-9]{1,8}(\.[0-9]{1,2})?$NUMBER(12,2)102^-?[0-9]{1,10}(\.[0-9]{1,2})?$NUMBER(18,4)144^-?[0-9]{1,14}(\.[0-9]{1,4})?$NUMBER(5)50^-?[0-9]{1,5}$NUMBER(1,0)10^[0-9]$1 位整数NUMBER(38,10)2810^-?[0-9]{1,28}(\.[0-9]{1,10})?$这里有个坑NUMBER(10,2)能存的最大值是99999999.99不是9999999999.99。如果你按p写正则写成{1,10}正则放过了9999999999落库时 Oracle 会报ORA-01438因为整数部分超了。所以正则的整数位必须等于p - s不能等于p。反过来也有个陷阱NUMBER(10,2)存-99999999.99是合法的负号不占位数。所以正则里符号位和位数限制要分开-?写在外面{1,8}只管数字部分。有人把符号也算进长度里结果负数全被拒这种低级错误我见过不止一次。如果你的位数是配置项不同客户精度要求不一样可以把正则拼出来。Oracle 里拼正则没什么特别的就是字符串连接CREATE OR REPLACE FUNCTION f_num_pattern ( p_int_len IN NUMBER, p_dec_len IN NUMBER, p_allow_sign IN VARCHAR2 DEFAULT Y ) RETURN VARCHAR2 IS l_pat VARCHAR2(200); BEGIN l_pat : ^; IF p_allow_sign Y THEN l_pat : l_pat || -?; END IF; l_pat : l_pat || [0-9]{1, || p_int_len || }; IF p_dec_len 0 THEN l_pat : l_pat || (\.[0-9]{1, || p_dec_len || })?; END IF; RETURN l_pat || $; END; /调用f_num_pattern(10, 2)返回^-?[0-9]{1,10}(\.[0-9]{1,2})?$。这种函数化写法在配置化的校验框架里特别有用不用每次改精度都去翻代码改字符串常量。不过要注意如果拼接的参数来自用户输入得做类型校验p_int_len声明成NUMBER本身就是一层保护——传非数字进去在参数绑定阶段就报错了。3. 从建表到约束完整跑一遍3.1 测试表和覆盖边界的样本数据光写正则不够得跑一遍才知道有没有漏。我习惯先建一张临时表把所有能想到的边界样本都塞进去跑一遍看结果对不对再决定要不要挂到正式表上。CREATE TABLE t_num_check ( id NUMBER GENERATED ALWAYS AS IDENTITY, raw_val VARCHAR2(100), memo VARCHAR2(200) ); INSERT ALL INTO t_num_check (raw_val, memo) VALUES (123, 普通整数) INTO t_num_check (raw_val, memo) VALUES (1234567890, 整数刚好10位) INTO t_num_check (raw_val, memo) VALUES (12345678901, 整数11位应拒) INTO t_num_check (raw_val, memo) VALUES (-123, 负数) INTO t_num_check (raw_val, memo) VALUES (123, 正号开头) INTO t_num_check (raw_val, memo) VALUES (0, 零) INTO t_num_check (raw_val, memo) VALUES (0.00, 零带小数) INTO t_num_check (raw_val, memo) VALUES (1.5, 一位小数) INTO t_num_check (raw_val, memo) VALUES (1.23, 两位小数) INTO t_num_check (raw_val, memo) VALUES (1.234, 三位小数应拒) INTO t_num_check (raw_val, memo) VALUES (1., 点后无数字应拒) INTO t_num_check (raw_val, memo) VALUES (.5, 点前无数字应拒) INTO t_num_check (raw_val, memo) VALUES (00, 前导零) INTO t_num_check (raw_val, memo) VALUES (0123, 前导零) INTO t_num_check (raw_val, memo) VALUES (1e5, 科学计数法应拒) INTO t_num_check (raw_val, memo) VALUES (1.5E3, 科学计数法应拒) INTO t_num_check (raw_val, memo) VALUES (1,234, 千分位应拒) INTO t_num_check (raw_val, memo) VALUES ( 123, 前导空格应拒) INTO t_num_check (raw_val, memo) VALUES (123 , 尾随空格应拒) INTO t_num_check (raw_val, memo) VALUES (, 全角数字应拒) INTO t_num_check (raw_val, memo) VALUES (, 空串即NULL) INTO t_num_check (raw_val, memo) VALUES (NULL, NULL) SELECT 1 FROM dual;样本设计有几个原则。一是边界值要卡在临界点上所以123456789010 位和1234567890111 位都放进去只有这样才能验证{1,10}是不是真的按预期工作。二是每种看起来像数字但不是的形态都要有样本科学计数法、千分位、空格、全角数字这四个是最容易漏的。三是空串和 NULL 都要有因为在 Oracle 里就是NULL两者的行为需要实测确认。3.2 逐条跑正则对照预期结果基础版的校验跑起来很简单SELECT id, memo, raw_val, CASE WHEN REGEXP_LIKE(raw_val, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN PASS ELSE REJECT END AS basic_result, CASE WHEN REGEXP_LIKE(raw_val, ^-?(0|[1-9][0-9]{0,9})(\.[0-9]{1,2})?$) THEN PASS ELSE REJECT END AS strict_result FROM t_num_check ORDER BY id;跑完之后逐条对我按实际行为整理成表重点看三个意外样本基础版严格版说明123PASSPASS正常1234567890PASSPASS临界10 位12345678901REJECTREJECT临界11 位符合预期-123PASSPASS负号不占位123REJECTREJECT我用的-?正号被拒0/0.00PASSPASS正常1.5PASSPASS一位小数最多两位允许1.234REJECTREJECT三位小数被拒1.REJECTREJECT点后无数字.5REJECTREJECT点前无数字00/0123PASSREJECT基础版放过前导零这是两个版本的唯一实质差异1e5/1.5E3REJECTREJECT科学计数法被拒1,234REJECTREJECT千分位被拒123/123REJECTREJECT空格被拒REJECTREJECT全角数字被拒/NULLNULLNULL结果为 NULL不是 TRUE 也不是 FALSE两个NULL结果要特别说明。REGEXP_LIKE(NULL, ...)返回NULL在WHERE子句里NULL不算通过所以查非法数据时会被漏掉但在CHECK约束里NULL被视为不违反约束所以能通过。这个不对称是 Oracle 三值逻辑的基本行为但很多人第一次遇到会懵。再看前导零那一行这是基础版和严格版的唯一区别。要不要禁止前导零取决于字段语义如果这个字段是金额0123和123是同一个值禁止更好如果这个字段是编号前导零有意义那就必须允许。我一般默认用基础版只在明确需要时才切严格版——因为基础版误杀的概率更低CHECK约束误杀存量数据的代价是ALTER TABLE直接失败。还有个额外的保险我通常会加上长度校验SELECT id, memo, raw_val, CASE WHEN REGEXP_LIKE(raw_val, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) AND LENGTH(raw_val) 14 THEN PASS ELSE REJECT END AS result FROM t_num_check;LENGTH上限怎么算最长的合法形态是- 10 位整数 . 2 位小数 14 个字符。加上这道校验能挡住一种特殊情况字符串末尾带换行符CHR(10)时$在某些情况下可能匹配到换行符之前的位置导致123||CHR(10)意外通过。这个行为在不同版本、不同客户端下表现不一定一致我不想去赌直接用长度兜底更踏实。更彻底的做法是在校验前先清洗REGEXP_LIKE(REPLACE(REPLACE(raw_val, CHR(10)), CHR(13)), ^-?[0-9]{1,10}(\.[0-9]{1,2})?$)清洗和长度校验二选一即可两个都上也不亏成本很低。3.3 落成 CHECK 约束与通用校验函数验证通过之后就可以挂到正式表上了。最直接的方式是CHECK约束ALTER TABLE t_amount ADD CONSTRAINT ck_amount_format CHECK (REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$));加之前必须做一件事先查出存量非法数据否则ALTER TABLE会直接抛ORA-02293无法验证约束在几千万行的表上这个报错能让你等半天再白等一场。SELECT COUNT(*) FROM t_amount WHERE NOT REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) OR amount_str IS NULL;注意这里的写法NOT REGEXP_LIKE(...)对NULL返回NULLWHERE里不成立所以NULL行不会被选出来。想要连NULL一起查必须显式加OR ... IS NULL。这个细节决定了你查出来的非法数据到底完不完整。还有一个选择是ENABLE NOVALIDATE让约束只对新增数据生效、跳过存量校验ALTER TABLE t_amount ADD CONSTRAINT ck_amount_format CHECK (REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$)) ENABLE NOVALIDATE;这个做法在上线维度的取舍很明显好处是立刻能加、不用等全表扫描坏处是存量脏数据依然在后面清洗时还要单独处理。我的经验是迁移场景用NOVALIDATE先止血业务系统新建表直接VALIDATE因为新表没存量没理由留口子。如果校验逻辑比较复杂比如要同时返回哪里不合法那就封装成函数。返回NULL表示通过返回字符串表示具体原因CREATE OR REPLACE FUNCTION f_check_num_format ( p_val IN VARCHAR2, p_int_len IN NUMBER, p_dec_len IN NUMBER, p_leading0 IN VARCHAR2 DEFAULT N ) RETURN VARCHAR2 IS l_pat VARCHAR2(200); BEGIN IF p_val IS NULL THEN RETURN EMPTY: 值为空; END IF; -- 长度快速预筛避免明显超长的串走正则 IF LENGTH(p_val) p_int_len p_dec_len 3 THEN RETURN LENGTH: 总长度超出预期; END IF; IF p_leading0 Y THEN l_pat : ^-?(0|[1-9][0-9]{0, || (p_int_len - 1) || }); ELSE l_pat : ^-?[0-9]{1, || p_int_len || }; END IF; IF p_dec_len 0 THEN l_pat : l_pat || (\.[0-9]{1, || p_dec_len || })?; END IF; l_pat : l_pat || $; IF NOT REGEXP_LIKE(p_val, l_pat) THEN RETURN FORMAT: 不匹配 || l_pat; END IF; RETURN NULL; END; /这个函数有几个设计考虑值得说。第一先做NULL判断因为REGEXP_LIKE对NULL返回NULL如果直接用IF NOT REGEXP_LIKE(...)判断NULL会走到NOT NULL还是NULL的逻辑里结果不符合直觉不如先拦掉。第二先做长度判断再走正则长度判断是 O(1) 级别的操作能在大量超长脏数据上省下不少正则引擎的开销。第三错误码带前缀EMPTY、LENGTH、FORMAT后面做批量清洗时按前缀分组统计一眼就能看出是哪类问题占多数。用的时候SELECT id, raw_val, f_check_num_format(raw_val, 10, 2, Y) AS err FROM t_num_check;要提醒一点这个函数在大表批量校验时性能不如纯 SQL 正则。原因是 PL/SQL 函数在 SQL 里逐行调用会有上下文切换开销一百万行能差出好几倍。所以函数适合做小批量或单条校验比如前端提交过来的一行数据大表批量校验还是老老实实用WHERE NOT REGEXP_LIKE(...)让优化器在 SQL 层跑完。这个分工我在好几个项目里都是这么用的。4. 正则之外性能和替代路径怎么选4.1 大表上正则的代价到底在哪正则不是免费的。REGEXP_LIKE每次调用都要启动正则引擎做编译和匹配虽然 Oracle 对同一个 pattern 有缓存优化但在千万行级别的表上做全表校验开销还是很可观。更麻烦的是正则表达式不能走普通索引WHERE REGEXP_LIKE(col, ...)必然是全表扫描这一点跟LIKE还不一样——LIKE abc%在某些情况下是能用索引的REGEXP_LIKE则完全没有这个待遇。所以性能优化的方向不是把正则写得更快而是**让正则跑的次数变少**。我常用的做法有三层。第一层是缩短输入。在正则之前先用便宜的判断筛一遍比如LENGTH(col) 14、col IS NOT NULL把明显不可能的数据先剔除。这类判断不需要启动正则引擎成本可以忽略。第二层是减少执行频率。校验放在 ETL 的入口而不是出口数据进仓时就卡住后面所有环节都不用再校验。如果数据是分批进来的那就每批校验一次别等到全量入库完再扫一遍全表。第三层是用函数索引或虚拟列把结果持久化。这个下面细说。我的经验是单表百万行以内的校验正则的开销完全可以接受跑一次几秒钟不需要过度优化千万行以上就要考虑持久化方案了否则每次校验都是一次全表扫描跑多了会明显影响业务。这个分界不是绝对的跟机器配置、并发情况都有关系实际还是要看执行计划里的consistent gets。4.2 函数索引、虚拟列与坏数据标记三种做法函数索引的用法是给非法数据建索引。思路很巧妙让合法行返回NULL非法行返回原值因为 Oracle 的 B-Tree 索引不存全NULL的键所以索引里实际上只有非法行体积很小查起来极快。CREATE INDEX idx_amount_bad ON t_amount ( CASE WHEN REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN NULL ELSE amount_str END );建完之后查非法数据就变成SELECT * FROM t_amount WHERE CASE WHEN REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN NULL ELSE amount_str END IS NOT NULL;注意WHERE里必须一字不差地重复索引表达式否则优化器不认还是走全表扫描。这个限制有点烦但没办法函数索引的匹配是基于表达式文本的。这里有个细节要注意建函数索引时正则里不要用[[:alpha:]]、[[:digit:]]这类依赖 NLS 的字符类否则可能因为会话的 NLS 设置不同导致索引失效或校验结果不一致。用[0-9]就没这个问题这也是我在 2.1 里推荐[0-9]的原因之一不只是可移植性。虚拟列是 12c 之后更清爽的做法把校验结果变成一个真实的列可以索引、可以查询、可以统计ALTER TABLE t_amount ADD ( fmt_flag NUMBER GENERATED ALWAYS AS ( CASE WHEN REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN 1 ELSE 0 END ) VIRTUAL ); CREATE INDEX idx_amount_fmt ON t_amount (fmt_flag);之后查非法数据就是WHERE fmt_flag 0简单直观也方便做非法率统计。虚拟列不占存储值在查询时实时计算代价转移到读的时候写的时候只多一点点 CPU。坏数据标记是最土但也最灵活的做法加一个普通列is_valid在 ETL 的最后一步用一条UPDATE打标。UPDATE t_amount SET is_valid CASE WHEN REGEXP_LIKE(amount_str, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN 1 ELSE 0 END;缺点是这条UPDATE本身很重还得单独提交。优点是打完标之后所有查询都变成了普通条件过滤索引效果最好而且校验结果可以只算一次、反复使用。如果这张表要长期反复查非法数据我倾向用这个方案如果只是偶尔查一次函数索引更省事。三种方案对比方案存储成本查询性能维护成本适用场景实时正则不建索引无全表扫描慢最低一次性校验、小表函数索引CASE 返回 NULL只存非法行极小走索引快中WHERE 要重复表达式偶尔查、非法率低虚拟列 索引列不占存储索引占走索引快中需要 12c要统计、要长期看物理标记列占一列最快最高要跑 UPDATE反复查询、大表4.3 TRANSLATE 预筛和 TO_NUMBER 兜底的边界REGEXP_LIKE不是唯一手段。有些场景用TRANSLATE更快因为它只是字符映射没有正则引擎的开销。TRANSLATE的机制是把from_str里的字符逐个映射成to_str里对应位置的字符to_str比from_str短时多出来的字符直接删除。利用这个特性可以快速判断字符串是不是只含数字和点SELECT raw_val, CASE WHEN TRANSLATE(raw_val, 0123456789.-, ) IS NULL THEN ONLY_NUMERIC_CHARS ELSE HAS_OTHER END AS char_check FROM t_num_check;不过这个写法只能判断字符集合判断不了位数和位置--..这种乱序组合它会放过。所以TRANSLATE的定位是预筛先用它把含字母、含逗号、含空格的脏数据快速剔掉剩下的再交给正则做精细校验。在脏数据比例很高的场景下这个两步走能省不少时间。另一种思路是用TO_NUMBER兜底SELECT raw_val, CASE WHEN REGEXP_LIKE(raw_val, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN FORMAT_OK WHEN raw_val IS NULL THEN NULL ELSE FORMAT_BAD END AS fmt, -- 注意只在格式通过时才尝试转换避免 ORA-01722 CASE WHEN REGEXP_LIKE(raw_val, ^-?[0-9]{1,10}(\.[0-9]{1,2})?$) THEN TO_NUMBER(raw_val) END AS num_val FROM t_num_check;这个组合的意图是正则管格式、转换管精度两道关分开。千万不要把TO_NUMBER放在正则前面因为 Oracle 的CASE虽然短路求值但WHERE子句的求值顺序不保证一旦优化器把TO_NUMBER提前执行碰上abc直接ORA-01722整个查询挂掉。还有一个反直觉的事实TO_NUMBER(1,234)在某些NLS_NUMERIC_CHARACTERS设置下是能成功的因为逗号被当成小数点。这意味着用TO_NUMBER做格式校验是完全不可靠的同一个值在不同会话里结果不同。所以格式校验必须是显式的正则或字符级判断不能依赖隐式转换。这一点我在踩过一次坑之后记了很久。最后说一个纯粹从性能出发的对比。做纯整数校验不含小数其实可以完全不用正则-- 方案A正则 WHERE REGEXP_LIKE(v, ^[0-9]{1,10}$) -- 方案BTRANSLATE LENGTH WHERE v IS NOT NULL AND LENGTH(v) BETWEEN 1 AND 10 AND TRANSLATE(v, 0123456789, ) IS NULL方案 B 在两个条件都满足时等价于方案 A但因为没有正则引擎参与在大表上能快不少。我测过的场景里纯整数校验用方案 B 大概能省下可观的 CPU 时间具体倍数跟数据分布和版本有关但方向是确定的。所以我的原则是格式简单纯整数、固定字符集就用TRANSLATE格式复杂可选小数、符号位、分组才上正则。5. 排查实录那些以为拦住了却没拦住的数据5.1 五个最容易漏的输入形态第一个是尾随空格。123 在[0-9]{1,10}$面前是过不了的因为$要求数字后面直接结束。但如果数据从固定宽度文件来解析时补了空格就会全军覆没。这时候别急着改正则先确认数据源是不是需要TRIM。我通常会在校验前统一TRIM因为业务上123 和123就是一个值。第二个是末尾换行符。这个坑我在 3.2 提过123||CHR(10)有可能通过^[0-9]$。原因是$在 POSIX 语义下可以匹配最后一个换行符之前。防御方式是加LENGTH校验或者提前REPLACE掉CHR(10)和CHR(13)。这类脏数据在从文件导入或者从接口报文里截取字段时特别常见而且肉眼看不出来SELECT出来显示得跟正常值一模一样。第三个是全角数字。看着是数字实际上每个字符占两个字节在AL32UTF8下占三字节[0-9]拦得住但如果用了[[:digit:]]就不一定了——某些 NLS 环境下全角数字会被算作 digit。这个差异很难在开发环境复现因为开发机上的 NLS 设置经常和生产的容器不一样。所以再强调一遍字符类用[0-9]。第四个是负零和正号。-0、0、123正则-?只放过了负号号过不去这是符合我预期的。但-0是能过的如果业务上-0和0是同一个值后面做DISTINCT或者GROUP BY就会分成两组。这种事只能靠业务层再兜一道正则层面没必要为了它把逻辑写复杂。第五个是**和NULL的混淆**。在 Oracle 里就是NULL不存在空字符串这个东西。所以任何关于空值该怎么处理的讨论在 Oracle 里其实只有一个问题NULL该不该被拦。CHECK约束默认放过NULLREGEXP_LIKE对NULL返回NULL这两个行为组合起来就是空值永远等于合法。如果业务要求字段必填必须显式加NOT NULL约束不能指望正则。这一点我在新建表时写约束的模板里都固定带上ALTER TABLE t_amount ADD CONSTRAINT ck_amount_notnull CHECK (amount_str IS NOT NULL);5.2 常见问题速查表现象可能原因排查 SQL处理方式123校验不通过字符串里混了不可见字符SELECT DUMP(raw_val) FROM t看DUMP输出的字节TRIM或REPLACE加CHECK约束报ORA-02293存量数据不合规先跑WHERE NOT REGEXP_LIKE(...)统计清洗后再加或改用ENABLE NOVALIDATE约束加了但脏数据还在约束用了NOVALIDATE只对新数据生效查USER_CONSTRAINTS.STATUS确认状态必要时VALIDATE空值全被放过了NULL在CHECK里视为通过SELECT COUNT(*) WHERE col IS NULL单独加NOT NULL1e5竟然通过了正则漏了锚点或用了而不是位数限制单独跑REGEXP_LIKE(1e5, 你的正则)补^$和{1,N}大表校验跑了很久正则无法走索引必然全表扫看执行计划TABLE ACCESS FULL用函数索引或标记列TO_NUMBER(1,234)成功NLS_NUMERIC_CHARACTERS影响SELECT * FROM NLS_SESSION_PARAMETERS不要依赖隐式转换做校验小数被静默截断列是NUMBER(p,s)入库时四舍五入对比原串和落库值用正则前置拦截或改用VARCHAR2整数位超了才报错正则按p写而不是p-s检查正则里整数位的上限改成p - s全角数字漏过用了[[:digit:]]SELECT DUMP(raw_val)改用[0-9]表格里小数被静默截断这一条我想再展开一句因为它是这整件事的核心动机。INSERT INTO t VALUES (1.235)到NUMBER(12,2)的列Oracle 不报错存成1.24。同一行数据用VARCHAR2存就是1.235用NUMBER存就是1.24差异是静默产生的。如果这个值参与结算三分钱的差异会随着行数放大。这就是为什么在对接外部系统时我宁可多一层正则校验也不愿意把格式正确这件事交给类型去猜。5.3 几条踩出来的经验第一条校验和存储分离。我的习惯是保留一份原始的VARCHAR2字符串列再加一份NUMBER类型的解析列。原始列带CHECK约束卡格式解析列供业务计算。这样做的好处是出问题时能追到源头1.235和1.24的差异一眼可见不用去翻日志。第二条CHECK约束里的正则不要写太长。Oracle 对约束表达式的长度有上限虽然一般够用但如果你把一大堆CASE WHEN塞进去很容易超。长逻辑应该拆成多个约束或者封装成函数但函数在约束里要注意确定性REGEXP_LIKE是确定性的可以放心用。第三条正则的 pattern 最好定义成一个常量集中管理。同一条正则散落在CHECK约束、ETL 脚本、应用代码里只要有一处改了其他地方就悄悄失配。我的做法是建一张配置表存 pattern或者用包里的常量所有地方从同一处取。改精度的时候只改一行风险可控。第四条先在测试数据上跑一遍反例集。我会专门维护一个反例清单把历史踩过的坑都记进去每次改正则都拿它跑一遍。这个清单比正例更重要因为正例通常一跑就过反例才是暴露问题的地方。清单里现在有三十多条包括1.2.3、--5、-5、0x1F、1_000这些看起来离谱但真的从各种系统里冒出来的形态。第五条正则匹配的边界要跟业务确认。0.00算不算合法金额-0.01这种小额负数在退款场景是不是正常000.00在前导零严格版下被拒但某些老系统导出的就是这种格式。这些都不是技术问题是业务口径问题技术层面只能提供选项拍板得业务来。我一般会在文档里把正则对应的合法集合写成例子列出来让业务方确认这些形态都能接受避免上线后扯皮。这些原则在业务系统里能成为硬约束在报表系统里也许就只需要在 ETL 里做一次具体怎么取舍还得看这份数据下游被谁用、用错一次的代价有多大。