我看到在 MySQL 中有 Cast() 和 Convert() 函数可以从值创建整数，但是有什么方法可以检查一个值是否为整数？我正在寻找像 PHP 中的 is_int() 这样的东西。
我假设你想检查一个字符串值。一种不错的方法是 REGEXP 运算符，将字符串与正则表达式匹配。简单地做
1
| select field from table where field REGEXP '^-?[0-9]+$'; |
这是相当快的。如果您的字段是数字，只需测试
改为。
假设我们有一个包含字母数字字段的列，其中包含
之类的条目
1
2
3
4
5
6
7
8
9
10
11
12
| a41q
1458
xwe8
1475
asde
9582
.
.
.
.
.
qe84 |
并且您想要此 db 列中的最高数值(在本例中为 9582)，那么此查询将帮助您
1
| SELECT Max(column_name) from table_name where column_name REGEXP '^[0-9]+$' |
将其与正则表达式匹配。
c.f. 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:
1
2
| 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.
这是一个简单的解决方案
假设数据类型是 varchar
1
| select * from calender where year 0 |
如果年份是数字则返回true，否则返回false
这也有效：
1
| CAST( coulmn_value AS UNSIGNED ) // will return 0 if not numeric string. |
例如
1
2
| SELECT CAST('a123' AS UNSIGNED) // returns 0
SELECT CAST('123' AS UNSIGNED) // returns 123 i.e. 0 |
要检查Mysql中的值是否为Int，我们可以使用以下查询。
此查询将给出具有 Int 值的行
1
| SELECT col1 FROM table WHERE concat('',col * 1) = col; |
我能想到的最好的变量是 int 是与 MySQL 的函数 CAST() 和 LENGTH() 的组合。
此方法适用于字符串、整数、双精度/浮点数数据类型。
1
| SELECT (LENGTH(CAST(data AS UNSIGNED))) = (LENGTH(data)) AS is_int |
参见演示 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
True Waqas Malik 完全忘记了测试那个案例。补丁是。
1
| 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; |
结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
| **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 | |
看演示
怎么样：
1
| WHERE table.field ="0" or CAST(table.field as SIGNED) != 0 |
测试数字和推论：
1
| WHERE table.field !="0" and CAST(table.field as SIGNED) = 0 |
这适用于 VARCHAR，它是否以数字开头..
1
| WHERE concat('',fieldname * 1) != fieldname |
当您到达较大的 NNNNE - 数字时可能会有限制
我曾尝试使用上面列出的正则表达式，但它们不适用于以下情况：
1
| SELECT '12 INCHES' REGEXP '^(-|\\\\+){0,1}([0-9]+\\\\.[0-9]*|[0-9]*\\\\.[0-9]+|[0-9]+)$' FROM ... |
上面会返回1(TRUE)，意思是字符串'12 INCHES'对上面的正则表达式的测试，返回TRUE。它看起来像一个基于上面使用的正则表达式的数字。在这种情况下，因为 12 位于字符串的开头，所以正则表达式将其解释为数字。
以下将返回正确的值(即0)，因为字符串以字符而不是数字开头
1
| SELECT 'TOP 10' REGEXP '^(-|\\\\+){0,1}([0-9]+\\\\.[0-9]*|[0-9]*\\\\.[0-9]+|[0-9]+)$' FROM ... |
上面将返回 0 (FALSE) 因为字符串的开头是文本而不是数字。
但是，如果您处理的字符串混合了数字和以数字开头的字母，您将无法获得想要的结果。 REGEXP 会将字符串解释为有效数字，而实际上它不是。
对我来说唯一有效的是：
1
2
| CREATE FUNCTION IsNumeric (sIn varchar(1024)) RETURNS tinyint
RETURN sIn REGEXP '^(-|\\\\+){0,1}([0-9]+\\\\.[0-9]*|[0-9]*\\\\.[0-9]+|[0-9]+)$'; |
在 234jk456 或 12 inches
的情况下，来自 kevinclark 的所有其他东西都会为我返回无用的东西
