南大通用GBase 8a的强制类型转换cast和convert的用法例子

GBase 8a数据库集群支持多种数据类型,从大类看包括CHAR、DATE、DATETIME、DECIMAL、TIME、NUMERIC、INT。对应字符,日期,日期时间,大精度数字,时间,浮点数,整数。本文介绍通过cast或convert函数将数据在不同的类型间强制转换的方法。

由于GBase 8a内部支持自动类型转换,在绝大部分情况下,是不需要强制转换的。一般用于在某些数据类型有限制时,自动评估的结果和预期不符,比如希望是varchar,结果返回的是int, 可以通过这些函数强制将类型转化为char。

函数

CAST(expr AS type) , CONVERT(expr,type)

说明

CAST()和 CONVERT()函数用于将一个类型的数值转换到另一个类型。type 可以是下列值之一:
• CHAR、DATE、DATETIME、DECIMAL、TIME、NUMERIC、INT。
• CAST()和 CONVERT(…USING…)是标准的 SQL 语法。
• CAST(str AS BINARY)等价于 BINARY str。
• CAST(expr AS CHAR)把表达式看作是默认字符集中的字符串。

使用 CAST()函数改变列类型为 DATE,DATETIME 或 TIME,只是标识此列,使其变为一个指定的数据类型,而不是改变列的值。CAST()的最终执行结果将会转化为指定的列类型。

样例

强制转化为字符串

这个一般没有损失。

原始数据

gbase> desc t2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
| pid   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> select * from t2;
+------+-----------+------+
| id   | name      | pid  |
+------+-----------+------+
|    1 | First     |    0 |
|    2 | Fist_1    |    1 |
|    3 | First_2   |    1 |
|    4 | First_1_1 |    2 |
+------+-----------+------+
4 rows in set (Elapsed: 00:00:00.00)

强制转换

gbase> create table t2_1 as select cast(id as char) id, name,pid from t2;
Query OK, 4 rows affected (Elapsed: 00:00:00.15)

gbase> desc t2_1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | varchar(11)  | YES  |     | NULL    |       |
| name  | varchar(100) | YES  |     | NULL    |       |
| pid   | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> select * fro mt2_1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your GBase server version for the right syntax to use near 'fro mt2_1' at line 1
gbase> select * from t2_1;
+------+-----------+------+
| id   | name      | pid  |
+------+-----------+------+
| 1    | First     |    0 |
| 2    | Fist_1    |    1 |
| 3    | First_2   |    1 |
| 4    | First_1_1 |    2 |
+------+-----------+------+
4 rows in set (Elapsed: 00:00:00.01)

转化为数字

这个要看字符能否转换,以及目标数字类型的精度。

完全无法转换

结果全变成了0。

gbase> select name,cast(name as int) from t2;
+-----------+-------------------+
| name      | cast(name as int) |
+-----------+-------------------+
| First     |                 0 |
| Fist_1    |                 0 |
| First_2   |                 0 |
| First_1_1 |                 0 |
+-----------+-------------------+
4 rows in set, 4 warnings (Elapsed: 00:00:00.00)

gbase> show warnings;
+-------+------+---------------------------------------------------------------------+
| Level | Code | Message                                                             |
+-------+------+---------------------------------------------------------------------+
| Note  | 1292 | gbase_rh7_001:5050 - Truncated incorrect INTEGER value: 'First'     |
| Note  | 1292 | gbase_rh7_001:5050 - Truncated incorrect INTEGER value: 'Fist_1'    |
| Note  | 1292 | gbase_rh7_001:5050 - Truncated incorrect INTEGER value: 'First_2'   |
| Note  | 1292 | gbase_rh7_001:5050 - Truncated incorrect INTEGER value: 'First_1_1' |
+-------+------+---------------------------------------------------------------------+
4 rows in set (Elapsed: 00:00:00.00)

部分转换

字符的前面部分能转换为数字,则只准换部分。

gbase> select cast(now() as int) from t2;
+--------------------+
| cast(now() as int) |
+--------------------+
|               2020 |
|               2020 |
|               2020 |
|               2020 |
+--------------------+
4 rows in set, 1 warning (Elapsed: 00:00:00.00)

gbase> select cast('1919-01-02' as int) from t2;
+---------------------------+
| cast('1919-01-02' as int) |
+---------------------------+
|                      1919 |
|                      1919 |
|                      1919 |
|                      1919 |
+---------------------------+
4 rows in set, 1 warning (Elapsed: 00:00:00.00)

gbase> select cast('03-02' as int) from t2;
+----------------------+
| cast('03-02' as int) |
+----------------------+
|                    3 |
|                    3 |
|                    3 |
|                    3 |
+----------------------+
4 rows in set, 1 warning (Elapsed: 00:00:00.00)

转换为日期

正常转换

gbase> select cast('2020-01-02' as date);
+----------------------------+
| cast('2020-01-02' as date) |
+----------------------------+
| 2020-01-02                 |
+----------------------------+
1 row in set (Elapsed: 00:00:00.00)

部分转换

最前面的部分满足格式要求,则转换。

gbase> select cast('2020-01-02 ABCDE' as date);
+----------------------------------+
| cast('2020-01-02 ABCDE' as date) |
+----------------------------------+
| 2020-01-02                       |
+----------------------------------+
1 row in set, 1 warning (Elapsed: 00:00:00.00)

gbase> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 1292 | Truncated incorrect date value: '2020-01-02 ABCDE' |
+---------+------+----------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

不能准换

返回NULL

gbase> select cast('2020' as date);
+----------------------+
| cast('2020' as date) |
+----------------------+
| NULL                 |
+----------------------+
1 row in set, 1 warning (Elapsed: 00:00:00.00)

gbase> select cast('2020-01' as date);
+-------------------------+
| cast('2020-01' as date) |
+-------------------------+
| NULL                    |
+-------------------------+
1 row in set, 1 warning (Elapsed: 00:00:00.00)
gbase> select cast('20200102 ABCDE' as date);
+--------------------------------+
| cast('20200102 ABCDE' as date) |
+--------------------------------+
| NULL                           |
+--------------------------------+
1 row in set, 1 warning (Elapsed: 00:00:00.00)

gbase> show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '20200102 ABCDE' |
+---------+------+--------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)