DCL

Updated on 2024-12-06 GMT+08:00

View PDF

**Table 1** DCL syntax compatibility
No.	Description	Syntax	Difference
1	Set names with COLLATE specified.	SET [ SESSION \| LOCAL ] NAMES {'charset_name' [COLLATE 'collation_name'] \| DEFAULT};	In GaussDB, you cannot specify charset_name to be different from that of the database character set. For details, see "SQL Reference > SQL Syntax > SQL Statements > S > SET" in the M-compatible Developer Guide. If no character set is specified, MySQL reports an error but GaussDB does not.
2	DESCRIBE statements are supported.	{DESCRIBE \| DESC} tbl_name [col_name \| wild]	User permission verification is different from that of MySQL. In GaussDB, you need the USAGE permission on the schema of a specified table and table-level or column-level permissions on the specified table. Only information about columns with the SELECT, INSERT, UPDATE, REFERENCES, and COMMENT permissions is displayed. In MySQL, you need table-level or column-level permissions on a specified table. Only information about columns with the SELECT, INSERT, UPDATE, REFERENCES, and COMMENT permissions is displayed. If character string comparison is involved in fuzzy match, the Field field uses the character set utf8mb4 and collation utf8mb4_general_ci.
3	START TRANSACTION supports consistent read snapshot.	START TRANSACTION [ { ISOLATION LEVEL { READ COMMITTED \| SERIALIZABLE \| REPEATABLE READ } \| { READ WRITE \| READ ONLY } \| WITH CONSISTENT SNAPSHOT } [, ...] ];	In MySQL, a transaction at the repeatable read isolation level starts snapshot read only after the first SELECT statement is executed. In GaussDB, once a transaction is started, not only the first SELECT statement performs snapshot read, but also the first executed DDL, DML, or DCL statement creates a consistent read snapshot of the transaction. In GaussDB, START TRANSACTION allows you to set the isolation level, transaction access mode, and consistent snapshot for multiple times. A new setting overwrites the old one and takes effect.
4	SET sets user variables.	SET @var_name := expr	In MySQL, user-defined variable names can be escaped using escape characters or double quotation marks, but this feature is not supported in GaussDB. Variable names enclosed in single quotation marks cannot contain other single quotation marks. For example, @'', @''', and @'\'' are not supported. During parsing, the single quotation marks (') cannot be matched or an error will be reported. For example: -- An error is reported during parsing. db_mysql=# SET @'''' = 1; ERROR: syntax error at or near "@" LINE 1: SET @'''' = 1; -- The single quotation marks (') cannot be matched during parsing. db_mysql=# SET @'\'' = 1; db_mysql'# Variable names enclosed in double quotation marks cannot contain double quotation marks ("). For example, @"", @"""" and @"\"" are not supported. The double quotation marks cannot be matched or an error will be reported during parsing. For example: -- An error is reported during parsing. db_mysql=# SET @"""" = 1; ERROR: syntax error at or near "@" LINE 1: SET @"""" = 1; -- The double quotation marks (") cannot be matched during parsing. db_mysql=# SET @"\"" = 1; db_mysql"# The variable name enclosed by backquotes cannot contain backquotes. For example, @````, @`````, and @`\`` are not supported. During parsing, the backquotes (`) cannot be matched or an error will be reported. For example: -- An error is reported during parsing. db_mysql=# SET @```` = 1; ERROR: syntax error at or near "@" LINE 1: SET @```` = 1; -- The backquotes (`) cannot be matched during parsing. db_mysql=# SET @`\`` = 1; db_mysql`# For example, set @var_name1 = @var_name2 := @var_name3 = @var_name4 := expr; can be used to assign consecutive values in MySQL, but cannot in GaussDB. db_mysql=# set @a := @b := @c = @d := 1; ERROR: user_defined variables cannot be set, such as @var_name := expr is not supported. expr can be an aggregate function in GaussDB but not in MySQL.
5	SET sets system parameters.	SET [ SESSION \| @@SESSION. \| @@ \| LOCAL \| @@LOCAL.] {config_parameter { TO \| = } { expr \| DEFAULT } \| FROM CURRENT }};	When config_parameter is a system parameter of the BOOLEAN type: The parameter value can be set to '1' or '0' or 'true' or 'false' in the character string format in M-compatible databases but cannot in MySQL. If the parameter value is set to the subquery result, when the result is 'true' or 'false' and the non-integer type is 1 or 0, the setting is successful in M-compatible databases but fails in MySQL. When the query result is NULL, the setting in M-compatible databases fails but is successful in MySQL.