Operators
GaussDB is compatible with most MySQL operators, but there are some differences. If they are not listed, the operator behavior is the native behavior of GaussDB by default. Currently, there are statements that are not supported by MySQL but supported by GaussDB. You are advised not to use these statements.
Operator Differences
- NULL values in ORDER BY are sorted in different ways. MySQL sorts NULL values first, while GaussDB sorts NULL values last. In GaussDB, nulls first and nulls last can be used to set the sorting sequence of NULL values.
- If ORDER BY is used, the output sequence of GaussDB is the same as that of MySQL. Without ORDER BY, GaussDB does not guarantee that the results are ordered.
- MySQL operators must use parentheses to ensure that an expression is strictly combined in a correct priority. Otherwise, an error is reported. For example, SELECT 1 regexp ('12345' regexp '123').
GaussDB operators support expressions that are not strictly combined by parentheses to be successfully executed.
- NULL values are displayed in different ways. MySQL displays a NULL value as "NULL". GaussDB displays a NULL value as empty.
MySQL output:
mysql> Select NULL; +------+ | NULL | +------+ | NULL | +------+ 1 row in set (0.00 sec)
GaussDB output:m_db=# select NULL; ?column? ---------- (1 row)
- After the operator is executed, the column names are displayed in different ways. MySQL displays a NULL value as "NULL". GaussDB displays a NULL value as empty.
- When character strings are being converted to the double type but there is an invalid one, the alarm is reported differently. MySQL reports an error when there is an invalid constant character string, but does not report an error for an invalid column character string. GaussDB reports an error in either situation.
- The results returned by the comparison operator are different. For MySQL, 1 or 0 is returned. For GaussDB, t or f is returned.
No. |
MySQL |
GaussDB |
Difference |
---|---|---|---|
1 |
<> |
Supported, with differences. |
MySQL supports indexes, but GaussDB does not. |
2 |
<=> |
Supported, with differences. |
MySQL supports indexes, but GaussDB does not support indexes, hash joins, or merge joins. |
3 |
Row expressions |
Not supported. |
MySQL supports <=>, but GaussDB does not. |
4 |
-- |
Supported. |
MySQL indicates that an operand is negated twice and the result is equal to the original operand. GaussDB indicates a comment. |
5 |
!! |
Supported, with differences. |
MySQL: The meaning of !! is the same as that of !, indicating NOT. GaussDB: The exclamation mark (!) indicates NOT. If there is a space between two exclamation marks (! !), it indicates NOT for twice. If there is no space between them (!!), it indicates factorial.
NOTE:
|
6 |
[NOT] REGEXP |
Supported, with differences. |
|
7 |
LIKE |
Supported, with differences. |
MySQL: The left operand of LIKE can only be an expression of a bitwise or arithmetic operation, or expression consisting of parentheses. The right operand of LIKE can only be an expression consisting of unary operators (excluding NOT) or parentheses. GaussDB: The left and right operands of LIKE can be any expression. |
8 |
[NOT] BETWEEN AND |
Supported, with differences. |
MySQL: [NOT] BETWEEN AND is nested from right to left. The first and second operands of [NOT] BETWEEN AND can only be expressions of bitwise or arithmetic operations, or expressions consisting of parentheses. GaussDB: [NOT] BETWEEN AND is nested from left to right. The first and second operands of [NOT] BETWEEN AND can be any expression. |
9 |
IN |
Supported, with differences. |
MySQL: The left operand of IN can only be an expression of a bitwise or arithmetic operation, or expression consisting of parentheses. GaussDB: The left operand of IN can be any expression. |
10 |
! |
Supported, with differences. |
MySQL: The operand of ! can only be an expression consisting of unary operators (excluding NOT) or parentheses. GaussDB: The operand of ! can be any expression. |
11 |
# |
Not supported. |
MySQL supports the comment tag (#), but GaussDB does not. |
Example of Operator Combination |
MySQL |
GaussDB |
Description |
---|---|---|---|
SELECT 1 LIKE 3 & 1; |
Not supported |
Supported |
The right operand of LIKE cannot be an expression consisting of bitwise operators. |
SELECT 1 LIKE 1 +1; |
Not supported |
Supported |
The right operand of LIKE cannot be an expression consisting of arithmetic operators. |
SELECT 1 LIKE NOT 0; |
Not supported |
Supported |
The right operand of LIKE can only be an expression consisting of unary operators (such as +, -, or ! but except NOT) or parentheses. |
SELECT 1 BETWEEN 1 AND 2 BETWEEN 2 AND 3; |
Right-to-left combination |
Left-to-right combination |
It is recommended that parentheses be added to specify the priority. |
SELECT 2 BETWEEN 1=1 AND 3; |
Not supported |
Supported |
The second operand of BETWEEN cannot be an expression consisting of comparison operators. |
SELECT 0 LIKE 0 BETWEEN 1 AND 2; |
Not supported |
Supported |
The first operand of BETWEEN cannot be an expression consisting of pattern matching operators. |
SELECT 1 IN (1) BETWEEN 0 AND 3; |
Not supported |
Supported |
The first operand of BETWEEN cannot be an expression consisting of IN operators. |
SELECT 1 IN (1) IN (1); |
Not supported |
Supported |
The second left operand of the IN expression cannot be an expression consisting of INs. |
SELECT ! NOT 1; |
Not supported |
Supported |
The operand of ! can only be an expression consisting of unary operators (such as +, -, or ! but except NOT) or parentheses. |
![](https://support.huaweicloud.com/intl/en-us/productdesc-gaussdb/public_sys-resources/note_3.0-en-us.png)
Combinations of operators that are supported in Gauss but not supported in MySQL are not recommended. You are advised to combine operators according to the rules in MySQL.
Index Differences
- Currently, GaussDB supports only UB-tree and B-tree indexes.
- For fuzzy match (LIKE operator), the default index created can be used in MySQL, but cannot be used in GaussDB. You need to use the following syntax to specify opclass to, for example, text_pattern_ops, so that LIKE operators can be used as indexes:
CREATE INDEX indexname ON tablename(col [opclass]);
- In the B-tree/UB-tree index scenario, the original logic of the native GaussDB is retained. That is, index scan supports comparison of types in the same operator family, but does not support other index types currently.
- In the operation scenarios involving index column type and constant type, the conditions that indexes of a WHERE clause are supported in GaussDB is different from those in MySQL, as shown in the following table. For example, GaussDB does not support indexes in the following statement:
create table t(_int int); create index idx on t(_int) using BTREE; select * from t where _int > 2.0;
In the operation scenarios involving index column type and constant type in the WHERE clause, you can use the cast function to convert the constant type to the column type for indexing.
select * from t where _int > cast(2.0 as signed);
Table 3 Differences in index support Index Column Type
Constant Type
GaussDB
MySQL
Integer
Integer
Yes
Yes
Floating-point
Floating-point
Yes
Yes
Fixed-point
Fixed-point
Yes
Yes
String
String
Yes
Yes
Binary
Binary
Yes
Yes
Time with date
Time with date
Yes
Yes
TIME
TIME
Yes
Yes
Time with date
Type that can be converted to time type with date (for example, integers such as 20231130)
Yes
Yes
Time with date
TIME
Yes
Yes
TIME
Constants that can be converted to the TIME type (for example, integers such as 203008)
Yes
Yes
Floating-point
Integer
Yes
Yes
Floating-point
Fixed-point
Yes
Yes
Floating-point
String
Yes
Yes
Floating-point
Binary
Yes
Yes
Floating-point
Time with date
Yes
Yes
Floating-point
TIME
Yes
Yes
Fixed-point
Integer
Yes
Yes
String
Time with date
Yes
No
String
TIME
Yes
No
Binary
String
Yes
Yes
Binary
Time with date
Yes
No
Binary
TIME
Yes
No
Integer
Floating-point
No
Yes
Integer
Fixed-point
No
Yes
Integer
String
No
Yes
Integer
Binary
No
Yes
Integer
Time with date
No
Yes
Integer
TIME
No
Yes
Fixed-point
Floating-point
No
Yes
Fixed-point
String
No
Yes
Fixed-point
Binary
No
Yes
Fixed-point
Time with date
No
Yes
Fixed-point
TIME
No
Yes
String
Binary
No
Yes
Time with date
Integer (that cannot be converted to the time type with date)
No
Yes
Time with date
Floating-point (that cannot be converted to the time type with date)
No
Yes
Time with date
Fixed-point (that cannot be converted to the time type with date)
No
Yes
TIME
Integer (that cannot be converted to the TIME type)
No
Yes
TIME
Character string (that cannot be converted to the TIME type)
No
Yes
TIME
Binary (that cannot be converted to the TIME type)
No
Yes
TIME
Time with date
No
Yes
Table 4 Whether index use is supported Index Column Type
Constant Type
Use Index or Not
MySQL
String
Integer
No
No
String
Floating-point
No
No
String
Fixed-point
No
No
Binary
Integer
No
No
Binary
Floating-point
No
No
Binary
Fixed-point
No
No
Time with date
Character string (that cannot be converted to the time type with date)
No
No
Time with date
Binary (that cannot be converted to the time type with date)
No
No
TIME
Floating-point (that cannot be converted to the TIME type)
No
No
TIME
Fixed-point (that cannot be converted to the TIME type)
No
No
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot