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;

Last modified: April 25, 2023

Was this article helpful?