Assigning Expressions to Variables
You can reference system variables, user-defined variables, literals, or any combination of these using built-ins like CONCAT
, and the SELECT
statement as a variant of complex expressions.
Example:
SET sql_mode = CONCAT(@@sql_mode, _utf8', ONLY_FULL_GROUP_BY');
You can connect to a CA, set session variables, and then use SET GLOBAL
or SET AGGREGATOR
referencing these variables. Values are evaluated on the CA and then forwarded to the MA.
You can set system variables using complex expressions inside stored procedures.
Example:
CREATE OR REPLACE PROCEDURE db.fff() AS BEGIN SET wait_timeout = 30001 + (SELECT b FROM db.t WHERE a = 3); ECHO SELECT @@session.wait_timeout; END;
There is a limitation that applies to all DDL statements inside stored procedures. They cannot access the stored procedure’s arguments and local variables. The workaround is to use EXECUTE IMMEDIATE
.
Example:
CREATE OR REPLACE PROCEDURE db.fff(arg int) AS DECLARE q text; BEGIN q = CONCAT('SET wait_timeout = 30001 + ', arg, ' + (SELECT b FROM db.t WHERE a = 3)'); EXECUTE IMMEDIATE q; ECHO SELECT @@session.wait_timeout; END;
Examples Using SET GLOBAL and SET SESSION
Example 1:
SET @new_mode = 'ANSI_QUOTES,'; SET @@GLOBAL.sql_mode = CONCAT(@new_mode, 'ONLY_FULL_GROUP_BY'); SELECT @@GLOBAL.sql_mode;
Example 2:
SET GLOBAL exporter_ssl_capath=`what's "going" on`; SELECT @@GLOBAL.exporter_ssl_capath;
Example 3:
SET SESSION net_read_timeout = (SELECT COUNT(*) FROM db.t) * 400; SELECT @@SESSION.net_read_timeout;
Example 4:
SET SESSION sql_mode=(select concat(@@sql_mode,',ANSI_QUOTES')); SELECT @@sql_mode;
Example 5:
SET SESSION inlist_precision_limit = 2000; SET @cvar = 200; SET GLOBAL inlist_precision_limit = @@inlist_precision_limit + @cvar; SELECT @@inlist_precision_limit; SELECT @@GLOBAL.inlist_precision_limit;