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

将其与正则表达式匹配。

比照 http://forums.mysql.com/read.php?60,1907,38488#msg-38488，引述如下:

Re: MySQL 中的 IsNumeric ()子句？
Posted by: kevinclark ()作者: kevinclark ()
日期: 2005年8月8日01:01 PM

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

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

这允许在开头处使用可选的加减号、一个可选的小数点和其余的数字位。

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

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

这是相当快的。如果您的字段是数字，只需测试

ceil(field) = field

取而代之。

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

select * from calender where year > 0

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

我试过使用上面列出的正则表达式，但它们不适用于以下情况:

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

上面的代码将返回 1(TRUE) ，这意味着对上面的正则表达式测试字符串“12 INCHES”，返回 TRUE。它看起来像一个基于上面使用的正则表达式的数字。在本例中，因为12位于字符串的开头，所以正则表达式将其解释为一个数字。

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

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

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

但是，如果处理的字符串由数字和以数字开头的字母组成，则不会得到所需的结果。REGEXP 将字符串解释为一个有效的数字，而实际上并非如此。

那么:

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

测试数值和推论:

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

假设我们有一个字母数字字段的列，其中的条目如下

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

如果您希望从这个 db 列获得最高的数值(在本例中是9582) ，那么这个查询将帮助您

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

这也行得通:

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

比如说

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

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

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

这对于 VARCHAR 来说非常有效，因为它以一个数字开头。

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

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

对我来说，唯一有用的就是:

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

从 kevinclark 所有其他返回无用的东西为我的情况下，234jk456或 12 inches

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

SELECT (LENGTH(CAST(<data> AS UNSIGNED))) = (LENGTH(<data>)) AS is_int

参见演示 http://sqlfiddle.com/#!9/ff40cd/44

如果列只有一个字符值，则它将失败 一个值“ A”，然后 Cast (“ A”作为 UNSIGNED)将计算为0，并且 长度(0)将是1。所以长度(铸造(‘ A’作为未签名)) = 长度(0)将 求值为1 = 1 = > 1

真正的 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;

结果

**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      |

见 小样