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)