String Functions

ASCII()

-

BIT_LENGTH()

-

CHAR_LENGTH()

If the database character set is SQL_ASCII, this function returns the number of bytes instead of the number of characters.

CHARACTER_LENGTH()

CONCAT()

-

CONCAT_WS()

-

HEX()

-

LENGTH()

-

LPAD()

The default maximum padding length in MySQL is 1398101, and that in GaussDB is 1048576. The maximum padding length varies depending on the character set. For example, if the character set is GBK, the default maximum padding length in GaussDB is 2097152.

RPAD()

MD5()

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.

RANDOM_BYTES()

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.

REPEAT()

-

REPLACE()

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. For example:

-- Behavior in GaussDB:
m_db=# select replace('1.23', binary(1.1), null);
 replace 
---------

(1 row)

-- Behavior in MySQL:
mysql> select replace('1.23', binary(1.1), null);
+------------------------------------+
| replace('1.23', binary(1.1), null) |
+------------------------------------+
| 1.23                               |
+------------------------------------+
1 row in set (0.00 sec)

SHA()/SHA1()

-

SHA2()

-

SPACE()

-

STRCMP()

-

FIND_IN_SET()

-

LCASE()

-

LEFT()

-

LOWER()

-

LTRIM()

-

REVERSE()

-

RIGHT()

-

RTRIM()

-

SUBSTR()

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.

SUBSTRING()

SUBSTRING_INDEX()

• 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.

TRIM()

-

UCASE()

-

UPPER()

-

UNHEX()

-

FIELD()

-

COMPRESS()

-

UNCOMPRESS()

-

UNCOMPRESS_LENGTH()

-

EXPORT_SET()

-

POSITION()

-

LOCATE()

-

CHAR()

• 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.

ELT()

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.

FORMAT()

-

BIN()

-

MAKE_SET()

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.

TO_BASE64()

-

FROM_BASE64()

-

ORD()

-

MID()

-

QUOTE()

• 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.
• GaussDB supports a maximum of 1 GB data transfer. The maximum length of the str input parameter is 536870908 bytes, and the maximum size of the result string returned by the function is 1 GB.
• 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.

INSERT()

-

INSTR()

-

OCTET_LENGTH()

-