如何在超大表list的holder_name字段中查询全数值记录？

针对大数据量表list的holder_name字段（字母数字型），要筛选出全为数值、不含任何字母的记录，以下是不同数据库的高效实现方式：

为什么LIKE语句没生效？ #

如果之前用的是LIKE '%[0-9]%'或LIKE '[0-9]%'这类写法，只能匹配「包含数字」或「开头是数字」的记录，无法确保每一位都是数字。如果要靠LIKE实现全匹配，需要写LIKE '[0-9]%' AND NOT LIKE '%[^0-9]%'（部分数据库支持），但这种写法繁琐且效率极低，完全不适合大数据量场景。

MySQL/MariaDB #

推荐用正则表达式直接匹配整个字符串：

SELECT * FROM list WHERE holder_name REGEXP '^[0-9]+$';
-- 等价写法：排除所有含非数字的记录
SELECT * FROM list WHERE holder_name NOT REGEXP '[^0-9]';

如果要进一步优化性能，可考虑创建表达式索引（MySQL 8.0+支持）：

CREATE INDEX idx_holder_num ON list ((holder_name REGEXP '^[0-9]+$'));

Oracle #

两种高效方式可选：

1. 正则匹配：

SELECT * FROM list WHERE REGEXP_LIKE(holder_name, '^[0-9]+$');

2. TRANSLATE函数（性能优于正则，更适合大数据量）：

SELECT * FROM list WHERE TRANSLATE(holder_name, '0123456789', '') IS NULL;

原理：把所有数字替换为空，若结果为空则说明原字段全是数字。

SQL Server #

用PATINDEX匹配非数字字符，若不存在则说明全是数字：

SELECT * FROM list WHERE PATINDEX('%[^0-9]%', holder_name) = 0;

也可尝试类型转换（注意数值范围，超长数字需用BIGINT/DECIMAL）：

SELECT * FROM list WHERE TRY_CAST(holder_name AS BIGINT) IS NOT NULL;

注意事项 #

• 若字段存在空值或空格，需额外过滤：比如加AND holder_name IS NOT NULL AND holder_name != ''
• 大数据量下，优先选择内置函数（如TRANSLATE、PATINDEX）而非正则，性能更优
• 频繁查询的话，建议基于筛选条件创建函数索引，大幅提升查询速度

内容的提问来源于stack exchange，提问作者Zehr_rheZ