函数和表达式
概述
由于MySQL中的函数与表达式，在GaussDB(DWS)中不存在或者存在一定的差异，DSC工具会根据GaussDB(DWS)的支持情况做相应迁移。（兼容ADB for MySQL的语法支持）
类型对照
MySQL/ADB函数类型 |
描述 |
MySQL INPUT |
GaussDB(DWS) OUTPUT |
|---|---|---|---|
CONVERT |
将值转换为指定的数据类型或字符集 |
CONVERT(A, B) |
CAST(A AS B) |
CURDATE |
当前日期,是CURDATE()的同义词 |
CURDATE/CURDATE() |
CURRENT_DATE () |
GET_JSON_OBJECT |
解析json字符串的一个字段 |
GET_JSON_OBJECT(column, '$.obj.arg') GET_JSON_OBJECT(column, '$[i]') |
JSON_EXTRACT(column, 'obj', 'arg') JSON_ARRAY_ELEMENT(column, 0) |
JSON_EXTRACT |
查询json中某个字段的值 |
JSON_EXTRACT(column, '$.obj') |
JSON_EXTRACT(column, 'obj') |
REGEXP |
模糊匹配 |
REGEXP A NOT REGEXP A |
~ A !~ A |
UUID |
生成唯一值(序列号) |
UUID |
SYS_GUID |
SPLIT()[] |
根据delimiter分隔string返回生成的第field个子字符串（从出现第一个delimiter的text为基础） |
SPLIT(string text, delimiter text)[field int] |
SPLIT_PART(string text, delimiter text,field int) |
RAND |
获取0.0到1.0之间的随机数 |
RAND() |
RANDOM() |
SLICE |
字符串切割, 以第一个参数为分隔符，链接第二个以后的所有参数 |
SLICE() |
CONCAT_WS(sep text, str"any" [, str"any" [, ...] ]) |
TRY_CAST |
类型转换函数，将x转换成y指定的类型 |
TRY_CAST(X AS Y) |
CAST(X AS Y) |
CAST |
类型转换函数，将x转换成y指定的类型 (ADB中cast函数第二个参数，强转数据类型可以为string和double，dws中没有对应类型，因此转换为varchar和double precision类型) |
CAST(X AS Y) |
CAST(X AS Y) |
输入示例CONVERT
1
|
SELECT CONVERT (IFNULL (BUSINESS_ID, 0) , DECIMAL(18, 2)) FROM ACCOUNT; |
输出示例
1
|
SELECT cast (ifnull (business_id, 0) AS decimal(18, 2))FROM account; |
输入示例CURDATE
1 2 |
SELECT CURDATE; SELECT CURDATE(); |
输出示例
1 2 |
SELECT CURRENT_DATE(); SELECT CURRENT_DATE(); |
输入示例GET_JSON_OBJECT
1 2 3 |
SELECT GET_JSON_OBJECT(COL_JSON, '$.STORE.BICYCLE.PRICE'); SELECT GET_JSON_OBJECT(COL_JSON, '$.STORE.FRUIT[0]'); |
输出示例
1 2 3 4 |
SELECT JSON_EXTRACT_PATH(COL_JSON, 'STORE', 'BICYCLE', 'PRICE'); SELECT JSON_ARRAY_ELEMENT(JSON_EXTRACT_PATH(COL_JSON, 'STORE', 'FRUIT'), 0); |
输入示例JSON_EXTRACT
1
|
SELECT JSON_EXTRACT(EVENT_ATTR,'$.TOPIC_ID'); |
输出示例
1
|
SELECT JSON_EXTRACT_PATH(EVENT_ATTR, 'TOPIC_ID'); |
输入示例REGEXP
1 2 |
SELECT * FROM USERS WHERE NAME NOT REGEXP '^王'; SELECT * FROM USERS WHERE TEL REGEXP '[^4-5]{11}'; |
输出示例
1 2 |
SELECT * FROM USERS WHERE NAME !~ '^王'; SELECT * FROM USERS WHERE TEL ~ '[^4-5]{11}'; |
输入示例UUID
1 2 |
SELECT CURDATE(str1), UUID(str2, str3) FROM T1; SELECT A FROM B WHERE uuid() > 2; |
输出示例
1 2 |
SELECT current_date (str1),sys_guid (str2, str3) FROM T1; SELECT A FROM B WHERE sys_guid () > 2; |
输入示例SPLIT()[]
1
|
SELECT split('a-b-c-d-e', '-')[4]; |
输出示例
1
|
SELECT split_part('a-b-c-d-e', '-', 4); |
输入示例RAND
1
|
SELECT rand(); |
输出示例
1
|
SELECT random (); |
输入示例SLICE
1
|
SELECT slice(split('2021_08_01','_'),1,3) from dual; |
输出示例
1 2 3 4 5 6 7 8 |
SELECT concat_ws( split_part('2021_08_01', '_', 1), split_part('2021_08_01', '_', 2), split_part('2021_08_01', '_', 3) ) FROM dual; |
输入示例TRY_CAST
1 2 |
select * from ods_pub where try_cast(pay_time AS timestamp) >= 1; select try_cast(pay_time as timestamp) from obs_pub; |
输出示例
1 2 |
SELECT * FROM ods_pub WHERE cast (pay_time AS timestamp) >= 1; SELECT cast (pay_time as timestamp) FROM obs_pub; |
输入示例CAST
1 2 3 4 |
select cast(ifnull(c1, 0) as string) from t1; select cast(ifnull(c1, 0) as varchar) from t1; select cast(ifnull(c1, 0) as double) from t1; select cast(ifnull(c1, 0) as int) from t1; |
输出示例
1 2 3 4 |
SELECT cast (ifnull (c1, 0) as varchar) FROM t1; SELECT cast (ifnull (c1, 0) as varchar) FROM t1; SELECT cast (ifnull (c1, 0) as double precision) FROM t1; SELECT cast (ifnull (c1, 0) as int) FROM t1; |
