MySQL 8.0+判断含正负号和小数的纯数字应使用col REGEXP '^[+-]?[0-9]+\.?[0-9]*$',需注意双写反斜杠、显式处理NULL、无法走索引,且不匹配空字符串、'.5'、'12.'等非标准格式。

MySQL 8.0+ 用 REGEXP 判断纯数字(含正负号和小数)
直接用 col REGEXP '^[+-]?[0-9]+.?[0-9]*$' 是最常用写法,它能匹配 123、-45.67、+0.0,但拒绝 12.34.56、.5、12.、abc。注意点:
-
.中的反斜杠必须双写为\.(在SQL字符串里最终变成单个点),否则会被当作文本点而非字面小数点 - MySQL 5.7 不支持
+量词,得换成[0-9]{1,} - 空字符串
''和NULL都不匹配该正则,所以要加AND col IS NOT NULL显式控制 - 这个表达式无法走索引,千万级表慎用于高频 WHERE 条件
只接受整数?CAST + 比对更轻量(但有边界)
如果字段业务上只该存整数(比如用户ID、订单号),可以用类型转换反推:col = CAST(CAST(col AS SIGNED) AS CHAR)。
- 原理是:非数字串(如
'123abc')转SIGNED会截断取前缀数字 → 变成123→ 再转回字符串是'123'≠ 原值 - 但
'abc123'转SIGNED得0,再比对变成'abc123' = '0'→ FALSE,也能识别 - 坑点明显:不支持小数(
'12.3'被转成12,比对失败)、溢出时可能返回NULL或最大值、'0'和'00'都变成'0'无法区分
PostgreSQL 用 ~ 操作符,别信 d
PostgreSQL 没有 REGEXP 关键字,要用波浪线:col ~ '^[+-]?[0-9]+.?[0-9]*$'。关键提醒:
-
d在 PostgreSQL 里**不等于[0-9]** —— 它会匹配 Unicode 数字(如阿拉伯数字、天城文数字),导致误判;必须写死[0-9] - 锚点
^和$必须带上,否则子串匹配会让'abc123def'也被当成“纯数字” -
NULL值参与~运算结果是NULL,不会进结果集,需按需补OR col IS NULL
SQL Server 没正则,靠 PATINDEX + 类型转换兜底
SQL Server 2016+ 仍无原生正则,查纯数字只能绕路:
- 先排除含非数字字符:
PATINDEX('%[^0-9]%', col) = 0,注意%[^0-9]%是 T-SQL 的字符类写法,不是正则 - 再处理符号和小数点:单独检查开头是否为
'+'或'-',且小数点最多一个(可用LEN(col) - LEN(REPLACE(col, '.', '')) ) -
PATINDEX对NULL返回NULL,所以条件得写成:(PATINDEX('%[^0-9]%', col) = 0 OR col IS NULL) AND ... - 如果字段是
TEXT类型(已过时),必须先CAST(col AS VARCHAR(MAX))再用PATINDEX
实际用哪条,取决于你数据库版本、字段语义(是否允许小数/符号)、以及能不能接受全表扫描。正则写起来快,但性能黑盒;CAST 比对快,但逻辑边界多。别默认以为 IS_NUMERIC() 存在——它不存在。

















