Help Center/ GaussDB/ Service Overview/ Compatibility with MySQL Databases/ Centralized Instances/ MySQL Compatibility in M-Compatible Mode/ System Functions/ String Functions
Updated on 2024-11-11 GMT+08:00
View PDF
Share

String Functions

Table 1 String functions

No.

MySQL

GaussDB

Difference

1

ASCII()

Supported.

-

2

BIT_LENGTH()

Supported.

-

3

CHAR_LENGTH()

Supported, with differences.

In GaussDB, if the character set is SQL_ASCII, CHAR_LENGTH() returns the number of bytes instead of characters.

4

CHARACTER_LENGTH()

Supported, with differences.

In GaussDB, if the character set is SQL_ASCII, CHARACTER_LENGTH() returns the number of bytes instead of characters.

5

CONCAT()

Supported.

If the return value type in MySQL is binary string (such as BINARY, VARBINARY, or BLOB), the return value type in GaussDB is BLOB. If the return value type in MySQL is non-binary string (such as CHAR, VARCHAR, or TEXT), the return value type in GaussDB is TEXT.

6

CONCAT_WS()

Supported.

If the return value type in MySQL is binary string (such as BINARY, VARBINARY, or BLOB), the return value type in GaussDB is BLOB. If the return value type in MySQL is non-binary string (such as CHAR, VARCHAR, or TEXT), the return value type in GaussDB is TEXT.

7

HEX()

Supported.

-

8

LENGTH()

Supported.

-

9

LPAD()

Supported, with differences.
  • The default maximum padding length in MySQL is 1398101, and that in GaussDB is 1048576. The maximum padding length depends on the character set. For example, if the character set is GBK, the default maximum padding length in GaussDB is 2097152.
  • If the database character set is SQL_ASCII, unexpected results may occur.
  • If the return value type in MySQL is binary string (such as BINARY, VARBINARY, or BLOB), the return value type in GaussDB is BLOB. If the return value type in MySQL is non-binary string (such as CHAR, VARCHAR, or TEXT), the return value type in GaussDB is TEXT.

10

MD5()

Supported, with differences.

When the length of the inserted string of the BINARY type is less than the target length, the padding characters in GaussDB are different from those in MySQL. Therefore, when the input parameter is of the BINARY type, the function result in GaussDB is different from that in MySQL.

11

RANDOM_BYTES()

Supported.

Both GaussDB and MySQL use OpenSSL to generate random character strings. GaussDB uses OpenSSL 3.x.x to generate random character strings. Compared with MySQL using OpenSSL 1.x.x, the performance in GaussDB may deteriorate.

12

REPEAT()

Supported.

If the return value type in MySQL is binary string (such as BINARY, VARBINARY, or BLOB), the return value type in GaussDB is BLOB. If the return value type in MySQL is non-binary string (such as CHAR, VARCHAR, or TEXT), the return value type in GaussDB is TEXT.

13

REPLACE()

Supported.
  • If the return value type in MySQL is binary string (such as BINARY, VARBINARY, or BLOB), the return value type in GaussDB is BLOB. If the return value type in MySQL is non-binary string (such as CHAR, VARCHAR, or TEXT), the return value type in GaussDB is TEXT.
  • If the third input parameter is null and the string length of the second input parameter is not 0, GaussDB returns NULL and MySQL may return the characters of the first parameter.

14

RPAD()

Supported, with differences.
  • The default maximum padding length in MySQL is 1398101, and that in GaussDB is 1048576. The maximum padding length depends on the character set. For example, if the character set is GBK, the default maximum padding length in GaussDB is 2097152.
  • If the database character set is SQL_ASCII, unexpected results may occur.
  • If the return value type in MySQL is binary string (such as BINARY, VARBINARY, or BLOB), the return value type in GaussDB is BLOB. If the return value type in MySQL is non-binary string (such as CHAR, VARCHAR, or TEXT), the return value type in GaussDB is TEXT.

15

SHA()/SHA1()

Supported.

-

16

SHA2()

Supported.

-

17

SPACE()

Supported.

-

18

STRCMP()

Supported, with differences.

If the database character set is SQL_ASCII, unexpected results may occur.

19

FIND_IN_SET()

Supported, with differences.

When characters are specified to be encoded in SQL_ASCII for the database, the server parses byte values 0 to 127 according to the ASCII standard, and byte values 128 to 255 cannot be parsed. If the input and output of the function contain any non-ASCII characters, the database cannot help you convert or verify them.

If the return value type in MySQL is binary string (such as BINARY, VARBINARY, or BLOB), the return value type in GaussDB is BLOB. If the return value type in MySQL is non-binary string (such as CHAR, VARCHAR, or TEXT), the return value type in GaussDB is TEXT.

The SUBSTRING function is different from that in MySQL when the first input parameter is nested.

  • When the collation returned by the first input parameter node is BINARY, MySQL may still use different collation logic (depending on the nested function), but GaussDB processes functions based on BINARY collation. As a result, the length of truncated bytes is different.

The differences in SUBSTRING_INDEX function are as follows:

  • When the third input parameter is a negative number, the comparison logic of MySQL is different from that of GaussDB, which may lead to different results.
  • When the third input parameter is a positive number, wraparound may occur because MySQL 5.7 stores data in int32 format, leading to an incorrect result. In MySQL 8.0, int64 is used for storage, which rectifies the problem. Therefore, GaussDB follows the setting of MySQL 8.0. However, when the input parameter value exceeds 2^63 – 1, wraparound also occurs. As a result, the obtained value of the third parameter may be a negative number, and the results are different.

20

LCASE()

21

LEFT()

22

LOWER()

23

LTRIM()

24

REVERSE()

25

RIGHT()

26

RTRIM()

27

SUBSTR()

28

SUBSTRING()

29

SUBSTRING_INDEX()

30

TRIM()

31

UCASE()

32

UPPER()

33

UNHEX()

Supported.

The return value type in MySQL is BINARY, VARBINARY, BLOB, MEDIUMBLOB, or LONGBLOB, while the return value type in GaussDB is fixed to LONGBLOB.

34

FIELD()

Supported.

-

35

COMPRESS()

Supported, with differences.

In MySQL, the return value type is VARBINARY, BLOB, or LONGBLOB. In GaussDB, the return value type is LONGBLOB.

36

UNCOMPRESS()

Supported.

-

37

UNCOMPRESS_LENGTH()

Supported.

-

38

EXPORT_SET()

Supported.

-

39

POSITION()

Supported.

-

40

LOCATE()

Supported.

-

41

CHAR()

Supported, with differences.
  • When the CHAR function is used to specify a character set, if the transcoding fails, GaussDB reports an error, and MySQL reports a WARNING and returns NULL.
  • In MySQL, if the parameter value is the 0th to 31st or 127th code in the ASCII table, the returned result is invisible. GaussDB returns the value in hexadecimal format, such as \x01 and \x02.
  • In MySQL, the number of input parameters of the CHAR function is not limited. In GaussDB, the number of input parameters of the function cannot exceed 8192.

42

ELT()

Supported, with differences.

In MySQL, the number of input parameters of the ELT function is not limited. In GaussDB, the number of input parameters of the function cannot exceed 8192.

43

FORMAT()

Supported.

-

44

BIN()

Supported.

-

45

MAKE_SET()

Supported.

In MySQL 5.7, if the first parameter selected by the MAKE_SET function is of the integer, floating-point, or fixed-point type and the returned result contains non-ASCII characters, garbled characters may be displayed. In GaussDB, the displayed result is normal, which is the same as that in MySQL 8.0.

46

TO_BASE64()

Supported.

-

47

FROM_BASE64()

Supported.

-

48

ORD()

Supported.

-

49

MID()

Supported.

-

50

QUOTE()

Supported, with differences.
  1. In M-compatible mode, enable MySQL escape. 
    SET m_format_behavior_compat_options=enable_escape_string;
  2. An input parameter string contains "\0" cannot be entered, because it is not supported by the character set in GaussDB. It is the escape character instead of the function itself that makes the function different in GaussDB and MySQL.
  3. In GaussDB, a maximum of 1 GB data can be transferred. The maximum length of the str input parameter is 536870908, and the maximum size of the result string returned by the function is 1 GB.
  4. For characters that are not padded, if the input parameter is of the BINARY type with a fixed length, null characters \0 are padded in MySQL and spaces are padded in GaussDB by default.
  5. In MySQL, QUOTE() processes null characters. In GaussDB, QUOTE() cannot process null characters.

51

INSERT()

Supported.

-

52

INSTR()

Supported.

-
Parent topic: System Functions