Help Center/ GaussDB(DWS)/ Tool Guide/ DSC/ MySQL Syntax Migrating/ Functions and Expressions
Updated on 2024-12-09 GMT+08:00
View PDF
Share

Functions and Expressions

Overview

The functions and expressions in MySQL do not exist in GaussDB(DWS) or are different from those in GaussDB(DWS). DSC migrates the functions and expressions based on the supported types of GaussDB(DWS). (compatible with ADB for MySQL)

Type Mapping

Table 1 Type mapping

MySQL/ADB Function

Description

MySQL INPUT

GaussDB(DWS) OUTPUT

CONVERT

Converts a value to a specified data type or character set.

CONVERT(A, B)

CAST(A AS B)

CURDATE

Specifies the current date. It is synonymous with CURDATE().

CURDATE/CURDATE()

CURRENT_DATE ()

GET_JSON_OBJECT

Parses a field in a JSON string.

GET_JSON_OBJECT(column, '$.obj.arg')

GET_JSON_OBJECT(column, '$[i]')

JSON_EXTRACT(column, 'obj', 'arg')

JSON_ARRAY_ELEMENT(column, 0)

JSON_EXTRACT

Queries the value of a field in JSON.

JSON_EXTRACT(column, '$.obj')

JSON_EXTRACT(column, 'obj')

REGEXP

Fuzzy match

REGEXP A

NOT REGEXP A

~ A

!~ A

UUID

Generates a unique value (sequence number).

UUID

SYS_GUID

SPLIT()[]

Splits string on delimiter and returns the fieldth column (counting from text of the first appeared delimiter).

SPLIT(string text, delimiter text)[field int]

SPLIT_PART(string text, delimiter text,field int)

RAND

Obtains a random number ranging from 0.0 to 1.0.

RAND()

RANDOM()

SLICE

Concatenates two or more strings, placing a separator between each one. The separator is specified by the first argument.

SLICE()

CONCAT_WS(sep text, str"any" [, str"any" [, ...] ])

TRY_CAST

Converts x into the type specified by y.

TRY_CAST(X AS Y)

CAST(X AS Y)

CAST

Converts x into the type specified by y.

The second argument of the cast function in the ADB can be forcibly converted to the string or double-precision data type. In GaussDB(DWS), it is converted to the varchar or double precision type.

CAST(X AS Y)

CAST(X AS Y)

Input example: CONVERT

1
 
SELECT CONVERT (IFNULL (BUSINESS_ID, 0) , DECIMAL(18, 2)) FROM ACCOUNT;

Output

1
 
SELECT cast (ifnull (business_id, 0) AS decimal(18, 2))FROM account;

Input example: CURDATE

1
2
 
SELECT CURDATE;
SELECT CURDATE();

Output

1
2
 
SELECT CURRENT_DATE();
SELECT CURRENT_DATE();

Input example: GET_JSON_OBJECT

1
2
3
 
SELECT GET_JSON_OBJECT(COL_JSON, '$.STORE.BICYCLE.PRICE');

SELECT GET_JSON_OBJECT(COL_JSON, '$.STORE.FRUIT[0]');

Output

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);

Input example: JSON_EXTRACT

1
 
SELECT JSON_EXTRACT(EVENT_ATTR,'$.TOPIC_ID');

Output

1
 
SELECT JSON_EXTRACT_PATH(EVENT_ATTR, 'TOPIC_ID');

Input example: REGEXP

1
2
 
SELECT * FROM USERS WHERE NAME NOT REGEXP '^ Wang';
SELECT * FROM USERS WHERE TEL REGEXP '[^4-5]{11}';

Output

1
2
 
SELECT * FROM USERS WHERE NAME !~ '^ Wang';
SELECT * FROM USERS WHERE TEL ~ '[^4-5]{11}';

Input example: UUID

1
2
 
SELECT CURDATE(str1), UUID(str2, str3) FROM T1;
SELECT A FROM B WHERE uuid() > 2;

Output

1
2
 
SELECT current_date (str1),sys_guid (str2, str3) FROM T1;
SELECT A FROM  B WHERE sys_guid () > 2;

Input example: SPLIT()[]

1
 
SELECT split('a-b-c-d-e', '-')[4];

Output

1
 
SELECT split_part('a-b-c-d-e', '-', 4);

Input Example RAND

1
 
SELECT rand();

Output

1
 
SELECT random ();

Input Example: SLICE

1
 
SELECT slice(split('2021_08_01','_'),1,3) from dual;

Output

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;

Input example: 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;

Output

1
2
 
SELECT * FROM ods_pub WHERE cast (pay_time AS timestamp) >= 1;
SELECT cast (pay_time as timestamp) FROM obs_pub;

Input Example: 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;

Output

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;
Parent topic: MySQL Syntax Migrating