mysql判断纯数字_MYSQL中判断字符串是否为纯数字

今天下午产品的需求中要求要把一个数据库字段类型是text类型，但是字段里面的字段值是纯数字的记录找出来做统计使用，开始觉得不能够实现，后面网上查了一下，最后按照产品的要求做出来了，但是实现的过程中出现一些小坑，并且发现网上的说法中有的是错误的，做一个纠正。

最开始的时候通过网上查资料搜到的方法是想通过正则匹配。(之前有个错误的认识,觉得正则匹配会严重影响效率)。不是特别愿意使用正则匹配，想通过其他方式实现。正好看到一个人提了一种方法。

"select * from tt where length(0+name)=length(name);"

我按照这个方法写了一下，跑了一下貌似可以，但是这个字段中会有存放'-'和'+'的情况，这种情况下的也被筛选了出来。然后再加个筛选条件把这两种情况过滤出去。这样表面上看是可以了。

但是得需要把这个写法的原理解释清楚，就接着找了一下mysql中length()的作用，网上查出来的作用是：mysql的一个用来来获取字符串长度的内置函数方法。按照这个作用的思路来的话，是解释不通的。但是为什么能查出来结果呢！？

然后就把关键部分单独拿出来，在mysql中执行

sql语句

运行结果

select LENGTH('test') as test1;

4

select LENGTH(0+'test') as test2;

1

select 0+'test' as test3;

0

select LENGTH('30') as test4;

2

select LENGTH('30'+0) as test5;

2

select '30'+0 as test6;

30

从上面的结果中，这就能说明问题了：字段类型是char，字段值是字符串的话，把字段的值加上0得到的结果是0。但字段值是要是数字的话，它加0的时候是把它当成数字在加的。那么这种情况下，数字在加0前后的长度是不变化的，确实能筛选出来；字符串的话在加了0之后长度就变成1了，就不等了就被过滤了出去(这里面有一个十分严重的坑，就是我测试的时候，数据库里面的字符串没有长度为1的字符串。加0操作后长度就变化了。)其实'-'和'+'也被筛选出来，就是因为它们的长度是1.但是这两个符号特殊，让我产生了误解，朝着把它们理解成是正号和负号的方向去考虑了。这点上也说明了想测试用例的时候，覆盖面要广是多么的重要！！！

接下来就确定下来使用正则规则去匹配。刚开始写出来的sql：

SELECT * FROM TEST WHERE (RESULT REGEXP '[^0-9.]') = 0;

{String} REGEXP '[^0-9.]'的作用就是如果String中含有不是0-9之间的数字或者是小数点时,返回true(1) ，反之则返回false(0)。返回值是0的时候正好符合我们的要求。

对正则熟悉的人就能马上看出问题来。

这样的话，是能把含有数字的字段过滤出来，但是出现类似'1.2.2.2.2'这种规则的数字的话也能被筛选出来，但是这在数学上是错误的，不能用来进行计算的。所以需要进一步修改，又得到如下写法：

SELECT * FROM TEST WHERE RESULT REGEXP '(^[0-9].[0-9]$)|(^[0-9]$)'

这种写法是只能限定死得到的小数类型在整数部分只能有一位。再经过查询资料最后得到写法

SELECT * FROM TEST WHERE RESULT REGEXP '(^[0-9]+.[0-9]+$)|(^[0-9]$)'

过程中查询到有价值的信息：

过程中，思路来源于参考了以下博文：

最后看一下刚开始担心的效率问题：

一共9125条数据，

SELECT * FROM TEST 用时：0.015

SELECT * FROM TEST WHERE RESULT REGEXP '(^[0-9]+.[0-9]+$)|(^[0-9]$)' 用时：0.028

SELECT * FROM TEST WHERE RESULT REGEXP '(^[0-9].[0-9]$)|(^[0-9]$)' 用时：0.018

可以看出要是规则复杂的正则表达式的话，效率上确实是会有明显的影响的。