文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ MySQL语法迁移/ 函数和表达式
更新时间：2024-10-26 GMT+08:00
查看PDF
分享

函数和表达式

概述

由于MySQL中的函数与表达式，在GaussDB(DWS)中不存在或者存在一定的差异，DSC工具会根据GaussDB(DWS)的支持情况做相应迁移。（兼容ADB for MySQL的语法支持）

类型对照

表1 类型对照表

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;
父主题： MySQL语法迁移