MySQL 检测MySQL中的值是否为数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5064977/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Detect if value is number in MySQL
提问by Urbycoz
Is there a way to detect if a value is a number in a MySQL query? Such as
有没有办法检测一个值是否是 MySQL 查询中的数字？如
SELECT *
FROM myTable
WHERE isANumber(col1) = true
回答by T. Corner
You can use Regular Expression too... it would be like:
您也可以使用正则表达式...就像：
SELECT * FROM myTable WHERE col1 REGEXP '^[0-9]+$';
Reference:http://dev.mysql.com/doc/refman/5.1/en/regexp.html
回答by RichardTheKiwi
This should work in most cases.
这应该在大多数情况下工作。
SELECT * FROM myTable WHERE concat('',col1 * 1) = col1
It doesn't work for non-standard numbers like
它不适用于非标准数字，例如
1e41.2e5123.(trailing decimal)
1e41.2e5123.（尾随小数）
回答by Dmitriy Kozmenko
If your data is 'test', 'test0', 'test1111', '111test', '111'
如果你的数据是 'test', 'test0', 'test1111', '111test', '111'
To select all records where the data is a simple int:
要选择数据为简单整数的所有记录：
SELECT *
FROM myTable
WHERE col1 REGEXP '^[0-9]+$';
Result: '111'
结果：'111'
(In regex, ^ means begin, and $ means end)
（在正则表达式中，^ 表示开始，$ 表示结束）
To select all records where an integer or decimal number exists:
要选择存在整数或十进制数的所有记录：
SELECT *
FROM myTable
WHERE col1 REGEXP '^[0-9]+\.?[0-9]*$'; - for 123.12
Result: '111' (same as last example)
结果：'111'（与上一个示例相同）
Finally,to select all records where number exists, use this:
最后，要选择 number 存在的所有记录，请使用以下命令：
SELECT *
FROM myTable
WHERE col1 REGEXP '[0-9]+';
Result: 'test0' and 'test1111' and '111test' and '111'
结果：'test0' 和 'test1111' 和 '111test' 和 '111'
回答by Paul Spiegel
SELECT * FROM myTable
WHERE col1 REGEXP '^[+-]?[0-9]*([0-9]\.|[0-9]|\.[0-9])[0-9]*(e[+-]?[0-9]+)?$'
Will also match signed decimals (like -1.2, +0.2, 6., 2e9, 1.2e-10).
还将匹配有符号小数（如-1.2, +0.2, 6., 2e9, 1.2e-10）。
Test:
测试：
drop table if exists myTable;
create table myTable (col1 varchar(50));
insert into myTable (col1)
values ('00.00'),('+1'),('.123'),('-.23e4'),('12.e-5'),('3.5e+6'),('a'),('e6'),('+e0');
select
col1,
col1 + 0 as casted,
col1 REGEXP '^[+-]?[0-9]*([0-9]\.|[0-9]|\.[0-9])[0-9]*(e[+-]?[0-9]+)?$' as isNumeric
from myTable;
Result:
结果：
col1 | casted | isNumeric
-------|---------|----------
00.00 | 0 | 1
+1 | 1 | 1
.123 | 0.123 | 1
-.23e4 | -2300 | 1
12.e-5 | 0.00012 | 1
3.5e+6 | 3500000 | 1
a | 0 | 0
e6 | 0 | 0
+e0 | 0 | 0
回答by Bora
Returns numeric rows
返回数字行
I found the solution with following query and works for me:
我通过以下查询找到了解决方案，并且对我有用：
SELECT * FROM myTable WHERE col1 > 0;
This query return rows having only greater than zero number column that col1
此查询返回的行只有大于零的列数 col1
Returns non numeric rows
返回非数字行
if you want to check column not numeric try this one with the trick (!col1 > 0):
如果您想检查列不是数字，请尝试使用技巧 ( !col1 > 0)：
SELECT * FROM myTable WHERE !col1 > 0;
回答by Devpaq
This answer is similar to Dmitry, but it will allow for decimals as well as positive and negative numbers.
这个答案类似于 Dmitry，但它允许小数以及正数和负数。
select * from table where col1 REGEXP '^[[:digit:]]+$'
回答by Hugo R
use a UDF (user defined function).
使用 UDF（用户定义函数）。
CREATE FUNCTION isnumber(inputValue VARCHAR(50))
RETURNS INT
BEGIN
IF (inputValue REGEXP ('^[0-9]+$'))
THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
Then when you query
然后当你查询
select isnumber('383XXXX')
--returns 0
--返回0
select isnumber('38333434')
--returns 1
-- 返回 1
select isnumber(mycol) mycol1, col2, colx from tablex; -- will return 1s and 0s for column mycol1
select isnumber(mycol) mycol1, col2, colx from tablex; -- 将返回 mycol1 列的 1 和 0
--you can enhance the function to take decimals, scientific notation , etc...
--您可以增强取小数、科学记数法等的功能...
The advantage of using a UDF is that you can use it on the left or right side of your "where clause" comparison. this greatly simplifies your SQL before being sent to the database:
使用 UDF 的优点是您可以在“where 子句”比较的左侧或右侧使用它。这在发送到数据库之前大大简化了您的 SQL：
SELECT * from tablex where isnumber(columnX) = isnumber('UnkownUserInput');
hope this helps.
希望这可以帮助。
回答by Stian Hvatum
Another alternative that seems faster than REGEXP on my computer is
在我的计算机上似乎比 REGEXP 更快的另一种选择是
SELECT * FROM myTable WHERE col1*0 != col1;
This will select all rows where col1 starts with a numeric value.
这将选择 col1 以数值开头的所有行。
回答by Jirka Kop?iva
Still missing this simple version:
仍然缺少这个简单的版本：
SELECT * FROM myTable WHERE `col1` + 0 = `col1`
(addition should be faster as multiplication)
（加法应该比乘法快）
Or slowest version for further playing:
或用于进一步播放的最慢版本：
SELECT *,
CASE WHEN `col1` + 0 = `col1` THEN 1 ELSE 0 END AS `IS_NUMERIC`
FROM `myTable`
HAVING `IS_NUMERIC` = 1
回答by Ferhat KO?ER
I recommend: if your search is simple , you can use `
我建议：如果您的搜索很简单，您可以使用 `
column*1 = column
` operator interesting :) is work and faster than on fields varchar/char
` 操作符有趣 :) 比在字段 varchar/char 上工作更快
SELECT * FROM myTable WHERE column*1 = column;
SELECT * FROM myTable WHERE column*1 = column;
ABC*1 => 0 (NOT EQU **ABC**)
AB15*A => 15 (NOT EQU **AB15**)
15AB => 15 (NOT EQU **15AB**)
15 => 15 (EQUALS TRUE **15**)
