如何检查MySQL中的值是否为整数?

I see that within MySQL there are Cast() and Convert() functions to create integers from values, but is there any way to check to see if a value is an integer? Something like is_int() in PHP is what I am looking for.

我看到在MySQL中有Cast()和Convert()函数来从值创建整数，但是有没有办法检查值是否是整数?我正在寻找的是PHP中的is_int()。

11 个解决方案

#1

176

I'll assume you want to check a string value. One nice way is the REGEXP operator, matching the string to a regular expression. Simply do

我假设你想检查一个字符串值。一种很好的方法是REGEXP操作符，将字符串匹配到正则表达式。简单的做

select field from table where field REGEXP '^-?[0-9]+$';

this is reasonably fast. If your field is numeric, just test for

这是相当快。如果你的字段是数字的，那就测试一下

ceil(field) = field

instead.

代替。

#2

Match it against a regular expression.

将它与正则表达式匹配。

c.f. http://forums.mysql.com/read.php?60,1907,38488#msg-38488 as quoted below:

多严峻http://forums.mysql.com/read.php?60、1907、38488 #味精- 38488如下报价:

Re: IsNumeric() clause in MySQL??
Posted by: kevinclark ()
Date: August 08, 2005 01:01PM

是MySQL中的IsNumeric()条款吗?发布日期:2005年8月8日下午1点

I agree. Here is a function I created for MySQL 5:

我同意。下面是我为MySQL 5创建的一个函数:

CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint
RETURN sIn REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

This allows for an optional plus/minus sign at the beginning, one optional decimal point, and the rest numeric digits.

这允许在开始处有一个可选的加减号、一个可选的小数点和其余的数字。

#3

Suppose we have column with alphanumeric field having entries like

假设我们有一个包含字母和数字字段的列，它有类似的条目

a41q
1458
xwe8
1475
asde
9582
.
.
.
.
.
qe84

and you want highest numeric value from this db column (in this case it is 9582) then this query will help you

您想要这个db列的最高数值(在本例中是9582)，那么这个查询将帮助您

SELECT Max(column_name) from table_name where column_name REGEXP '^[0-9]+$'

#4

Here is the simple solution for it assuming the data type is varchar

这里有一个简单的解决方案，假设数据类型为varchar

select * from calender where year > 0

It will return true if the year is numeric else false

如果年份为数字else false，则返回true

#5

This also works:

这同样适用:

CAST( coulmn_value AS UNSIGNED ) // will return 0 if not numeric string.

for example

例如

SELECT CAST('a123' AS UNSIGNED) // returns 0
SELECT CAST('123' AS UNSIGNED) // returns 123 i.e. > 0

#6

To check if a value is Int in Mysql, we can use the following query. This query will give the rows with Int values

要检查Mysql中的值是否为Int，可以使用以下查询。该查询将给出带有Int值的行。

SELECT col1 FROM table WHERE concat('',col * 1) = col;

#7

What about:

是什么:

WHERE table.field = "0" or CAST(table.field as SIGNED) != 0

to test for numeric and the corrolary:

测试数值和腐蚀性:

WHERE table.field != "0" and CAST(table.field as SIGNED) = 0

#8

I have tried using the regular expressions listed above, but they do not work for the following:

我尝试过使用上面列出的正则表达式，但是它们不能用于以下方面:

SELECT '12 INCHES' REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$' FROM ...

The above will return 1 (TRUE), meaning the test of the string '12 INCHES' against the regular expression above, returns TRUE. It looks like a number based on the regular expression used above. In this case, because the 12 is at the beginning of the string, the regular expression interprets it as a number.

上面的将返回1 (TRUE)，这意味着对上面的正则表达式的字符串“12英寸”的测试返回TRUE。它看起来像一个基于上面使用的正则表达式的数字。在这种情况下，由于12在字符串的开头，正则表达式将它解释为一个数字。

The following will return the right value (i.e. 0) because the string starts with characters instead of digits

下面将返回正确的值(例如0)，因为字符串以字符而不是数字开头

SELECT 'TOP 10' REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$' FROM ...

The above will return 0 (FALSE) because the beginning of the string is text and not numeric.

上面的字符串将返回0 (FALSE)，因为字符串的开头是文本而不是数字。

However, if you are dealing with strings that have a mix of numbers and letters that begin with a number, you will not get the results you want. REGEXP will interpret the string as a valid number when in fact it is not.

但是，如果您处理的字符串中包含以数字开头的数字和字母，那么您将不会得到您想要的结果。REGEXP将把字符串解释为有效数字，而实际上不是。

#9

The best i could think of a variable is a int Is a combination with MySQL's functions CAST() and LENGTH().
This method will work on strings, integers, doubles/floats datatypes.

我能想到的最好的变量是int，它结合了MySQL的函数CAST()和LENGTH()。此方法将用于字符串、整数、双精度/浮点数据类型。

SELECT (LENGTH(CAST([data] AS UNSIGNED))) = (LENGTH([data])) AS is_int

see demo http://sqlfiddle.com/#!9/ff40cd/44

看到演示http://sqlfiddle.com/ ! 9 / ff40cd / 44

#10

This works well for VARCHAR where it begins with a number or not..

这对VARCHAR很有效，它从一个数字开始。

WHERE concat('',fieldname * 1) != fieldname

may have restrictions when you get to the larger NNNNE+- numbers

当你得到较大的NNNNE+- number时，可能会有限制

#11

for me the only thing that works is:

对我来说，唯一起作用的是:

CREATE FUNCTION IsNumeric (SIN VARCHAR(1024)) RETURNS TINYINT
RETURN SIN REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

from kevinclark all other return useless stuff for me in case of 234jk456 or 12 inches

在234jk456或12英寸的情况下，所有其他的东西都从凯夫林克拉克返回给我

#1

176