# 类型转换函数
表1类型转换函数列表 
| 函数名       | 与MySQL的差异                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|:---|:---|
| CAST()    | - 由于函数执行机制不同，在cast函数嵌套其他函数（如greatest、least等）时，内层函数返回小于1的值，结果与MySQL不一致。 ``` --GaussDB: m_db=# SELECT cast(least(1.23, 1.23, 0.23400) AS date); WARNING:  Incorrect datetime value: '0.23400' CONTEXT:  referenced column: cast cast ------ (1 row) --MySQL 5.7: mysql> SELECT cast(least(1.23, 1.23, 0.23400) AS date); +------------------------------------------+ | cast(least(1.23, 1.23, 0.23400) as date) | +------------------------------------------+ | 0000-00-00                               | +------------------------------------------+ 1 row in set (0.00 sec) ```   - GaussDB支持使用CAST(expr AS FLOAT\[(p)\])或CAST(expr AS DOUBLE)将表达式转换为浮点类型，MySQL 5.7版本不支持此转换。  - 对于CAST嵌套子查询场景，如果子查询语句返回的是FLOAT类型，GaussDB返回的是准确的数值，MySQL 5.7版本返回失真数值，BINARY函数使用CAST实现，同理。 ``` --GaussDB m_db=# CREATE TABLE sub_query_table(myfloat float); CREATE TABLE m_db=# INSERT INTO sub_query_table(myfloat) VALUES (1.23); INSERT 0 1 m_db=# SELECT binary(SELECT myfloat FROM sub_query_table) FROM sub_query_table; binary -------- 1.23 (1 row) m_db=# SELECT cast((SELECT myfloat FROM sub_query_table) AS char); cast ------ 1.23 (1 row) --MySQL 5.7 mysql> CREATE TABLE sub_query_table(myfloat float); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO sub_query_table(myfloat) VALUES (1.23); Query OK, 1 row affected (0.00 sec) mysql> SELECT binary(SELECT myfloat FROM sub_query_table) FROM sub_query_table; +-----------------------------------------------+ | binary(SELECT myfloat FROM sub_query_table) | +-----------------------------------------------+ | 1.2300000190734863                            | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT cast((SELECT myfloat FROM sub_query_table) AS char); +------------------------------------------------------+ | cast((SELECT myfloat FROM sub_query_table) AS char) | +------------------------------------------------------+ | 1.2300000190734863                                   | +------------------------------------------------------+ 1 row in set (0.00 sec) ```     - GaussDB在JSON数据类型显式转换后运用于精度计算时，与MySQL 5.7不一致，与MySQL 8.0一致，计算时精度与使用JSON类型表中数据精度保持一致。 示例： ``` --GaussDB test=# SET m_format_behavior_compat_options='enable_precision_decimal'; SET test=# DROP TABLE tt01; DROP TABLE test=# CREATE TABLE tt01 AS SELECT -cast('98.7654321' AS json) AS c1; INSERT 0 1 test=# DESC tt01; Field |  Type  | Null | Key | Default | Extra -------+--------+------+-----+---------+------- c1    | double | YES  |     |         | (1 row) test=# SELECT * FROM tt01; c1 ------------- -98.7654321 (1 row) --MySQL 5.7 mysql> SELECT version(); +------------------+ | version()        | +------------------+ | 5.7.44-debug-log | +------------------+ 1 row in set (0.00 sec) mysql> DROP TABLE tt01; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE tt01 AS SELECT -cast('98.7654321' AS json) AS c1; Query OK, 1 row affected (0.03 sec) Records: 1  Duplicates: 0  Warnings: 0 mysql> DESC tt01; +-------+--------------+------+-----+---------+-------+ | Field | Type         | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | c1    | double(17,0) | YES  |     | NULL    |       | +-------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec) mysql> SELECT * FROM tt01; +------+ | c1   | +------+ |  -99 | +------+ 1 row in set (0.00 sec) --MySQL 8.0 mysql> SELECT version(); +--------------+ | version()    | +--------------+ | 8.0.36-debug | +--------------+ 1 row in set (0.00 sec) mysql> DROP TABLE tt01; Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE tt01 AS SELECT -cast('98.7654321' AS json) AS c1; Query OK, 1 row affected (0.12 sec) Records: 1  Duplicates: 0  Warnings: 0 mysql> DESC tt01; +-------+--------+------+-----+---------+-------+ | Field | Type   | Null | Key | Default | Extra | +-------+--------+------+-----+---------+-------+ | c1    | double | YES  |     | NULL    |       | +-------+--------+------+-----+---------+-------+ 1 row in set (0.01 sec) mysql> SELECT * FROM tt01; +-------------+ | c1          | +-------------+ | -98.7654321 | +-------------+ 1 row in set (0.00 sec) ```    |
| CONVERT() | GaussDB支持使用CONVERT(expr, FLOAT\[(p)\])或CONVERT(expr, DOUBLE)将表达式转换为浮点类型，MySQL 5.7版本不支持此转换。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |