在数据库处理中数字与字符串之间比较的坑（hive VS mysql ）_hive 字符串和数字比较

背景

在工作中遇到了同样的SQL在hive和MySQL执行后所得总数对不上，但是抽样执行都是一致的。最后发现问题出在hive和MySQL在数字和字符串比较时是不一样的。

MySQL

在MySQL中，字符串和数字比较是自动转化为数字，如果开头没有数字就转化为0：

1. 字符串（数值）与数值比较 ，与数值比较没有差别：

mysql> select '5'>4;
+-------+
| '5'>4 |
+-------+
|     1 |
+-------+
1 row in set

mysql> select '5'>04;
+--------+
| '5'>04 |
+--------+
|      1 |
+--------+
1 row in set
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

2. 字符串（数值+字符串）与数值比较 ，从左到右转换为数值进行比较，转换不了即为0：

mysql> select 'abc'=0;
+---------+
| 'abc'=0 |
+---------+
|       1 |
+---------+
1 row in set

mysql> select 'abc1'>0;
+----------+
| 'abc1'>0 |
+----------+
|        0 |
+----------+
1 row in set

mysql> select '1abc'>0;
+----------+
| '1abc'>0 |
+----------+
|        1 |
+----------+
1 row in set

mysql> select '01abc'>0;
+-----------+
| '01abc'>0 |
+-----------+
|         1 |
+-----------+
1 row in set
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

3. 字符串（数值）与字符串（数值）比较 ，从左到右按位比较：

mysql> select '05'>4;
+---------+
| '05'>4 |
+---------+
|       1 |
+---------+
1 row in set

mysql> select '05'>'4';
+----------+
| '05'>'4' |
+----------+
|        0 |
+----------+
1 row in set

mysql> select 'a05'>'4';
+----------+
| 'a05'>'4' |
+----------+
|        1 |
+----------+
1 row in set

mysql> select '05a'>'4';
+-----------+
| '05a'>'4' |
+-----------+
|         0 |
+-----------+
1 row in set
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

具体可参考：https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html

hive

因工作需要在hive中大家往往会将数值转换为string，在hive中，字符串和数字比较是自动转化为数字，但如果是“数字+字符”和数值比较的话，hive是无法转换的，会为空（null）,注意不是0：

其他规则跟MySQL一样。

除此之外，对于日期型的比较，相当于字符比字符。

mysql> select '2019-10-02abc'>'2019-10-01';

+------------------------------+
| '2019-10-02abc'>'2019-10-01' |
+------------------------------+
|                            1 |
+------------------------------+
1 row in set

mysql> select '2019-10-01
abc'>'2019-10-02';
+------------------------------+
| '2019-10-01abc'>'2019-10-02' |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set
mysql> select 'abc
2019-10-01'>'2019-10-02';
+------------------------------+
| 'abc2019-10-01'>'2019-10-02' |
+------------------------------+
|                            1 |
+----------------
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

在进行字符串（数值型字符串） 和数值比较时一定要慎重，不同的数据库语法的支持是不一样的。