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

How do I check to see if a value is an integer in MySQL?

提问by Craig Nakamoto

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()函数来创建从值的整数，但有什么办法来检查，看是否有值为整数？is_int()我正在寻找类似PHP 的东西。

回答by Jumpy

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.

反而。

回答by JBB

Match it against a regular expression.

将其与正则表达式匹配。

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

参见http://forums.mysql.com/read.php?60,1907,38488#msg-38488引用如下：

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

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

回复：MySQL 中的 IsNumeric() 子句？
发表者：kevinclark ()
日期：2005 年 8 月 8 日 01:01PM

我同意。这是我为 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.

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

回答by Tarun Sood

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]+$'

回答by Jayjitraj

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

如果年份是数字则返回 true 否则返回 false

回答by Riad

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

回答by minhas23

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;

回答by Raymond Nijland

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

it will fail if the column has a single character value. if column has a value 'A' then Cast('A' as UNSIGNED) will evaluate to 0 and LENGTH(0) will be 1. so LENGTH(Cast('A' as UNSIGNED))=LENGTH(0) will evaluate to 1=1 => 1

如果该列只有一个字符值，它将失败。如果列的值为 'A'，则 Cast('A' as UNSIGNED) 将评估为 0，而 LENGTH(0) 将为 1。所以 LENGTH(Cast('A' as UNSIGNED))=LENGTH(0) 将评估为1=1 => 1

True Waqas Malik totally fogotten to test that case. the patch is.

真正的 Waqas Malik 完全不知道要测试那个案例。补丁是。

SELECT <data>, (LENGTH(CAST(<data> AS UNSIGNED))) = CASE WHEN CAST(<data> AS UNSIGNED) = 0 THEN CAST(<data> AS UNSIGNED) ELSE (LENGTH(<data>)) END AS is_int;

Results

结果

**Query #1**

    SELECT 1, (LENGTH(CAST(1 AS UNSIGNED))) = CASE WHEN CAST(1 AS UNSIGNED) = 0 THEN CAST(1 AS UNSIGNED) ELSE (LENGTH(1)) END AS is_int;

| 1   | is_int |
| --- | ------ |
| 1   | 1      |

---
**Query #2**

    SELECT 1.1, (LENGTH(CAST(1 AS UNSIGNED))) = CASE WHEN CAST(1.1 AS UNSIGNED) = 0 THEN CAST(1.1 AS UNSIGNED) ELSE (LENGTH(1.1)) END AS is_int;

| 1.1 | is_int |
| --- | ------ |
| 1.1 | 0      |

---
**Query #3**

    SELECT "1", (LENGTH(CAST("1" AS UNSIGNED))) = CASE WHEN CAST("1" AS UNSIGNED) = 0 THEN CAST("1" AS UNSIGNED) ELSE (LENGTH("1")) END AS is_int;

| 1   | is_int |
| --- | ------ |
| 1   | 1      |

---
**Query #4**

    SELECT "1.1", (LENGTH(CAST("1.1" AS UNSIGNED))) = CASE WHEN CAST("1.1" AS UNSIGNED) = 0 THEN CAST("1.1" AS UNSIGNED) ELSE (LENGTH("1.1")) END AS is_int;

| 1.1 | is_int |
| --- | ------ |
| 1.1 | 0      |

---
**Query #5**

    SELECT "1a", (LENGTH(CAST("1.1" AS UNSIGNED))) = CASE WHEN CAST("1a" AS UNSIGNED) = 0 THEN CAST("1a" AS UNSIGNED) ELSE (LENGTH("1a")) END AS is_int;

| 1a  | is_int |
| --- | ------ |
| 1a  | 0      |

---
**Query #6**

    SELECT "1.1a", (LENGTH(CAST("1.1a" AS UNSIGNED))) = CASE WHEN CAST("1.1a" AS UNSIGNED) = 0 THEN CAST("1.1a" AS UNSIGNED) ELSE (LENGTH("1.1a")) END AS is_int;

| 1.1a | is_int |
| ---- | ------ |
| 1.1a | 0      |

---
**Query #7**

    SELECT "a1", (LENGTH(CAST("1.1a" AS UNSIGNED))) = CASE WHEN CAST("a1" AS UNSIGNED) = 0 THEN CAST("a1" AS UNSIGNED) ELSE (LENGTH("a1")) END AS is_int;

| a1  | is_int |
| --- | ------ |
| a1  | 0      |

---
**Query #8**

    SELECT "a1.1", (LENGTH(CAST("a1.1" AS UNSIGNED))) = CASE WHEN CAST("a1.1" AS UNSIGNED) = 0 THEN CAST("a1.1" AS UNSIGNED) ELSE (LENGTH("a1.1")) END AS is_int;

| a1.1 | is_int |
| ---- | ------ |
| a1.1 | 0      |

---
**Query #9**

    SELECT "a", (LENGTH(CAST("a" AS UNSIGNED))) = CASE WHEN CAST("a" AS UNSIGNED) = 0 THEN CAST("a" AS UNSIGNED) ELSE (LENGTH("a")) END AS is_int;

| a   | is_int |
| --- | ------ |
| a   | 0      |

see demo

看演示

回答by Tom Auger

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

回答by Bill Kelly

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 INCHES'，返回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 会将字符串解释为有效数字，而实际上并非如此。

回答by PodTech.io

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+- 数字时可能会有限制