MySQL - SET Syntax for Variable Assignment

---

---

MySQL SET Statement

The SET statement in MySQL is used to assign values for variables. Using this you can set values to, user-defined variables, variables in procedures and, system variables.

Syntax

Following is the syntax of the SET statement in MySQL−

SET variable = val1, val2, val3, . . .

variable: {
   user_var_name
   | param_name
   | local_var_name
   | {GLOBAL | @@GLOBAL.} system_var_name
   | {PERSIST | @@PERSIST.} system_var_name
   | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name
   | [SESSION | @@SESSION. | @@] system_var_name
}

Setting values to user defined variables

You can create variables with in the current MySQL session and use them in the queries. While defining a user d-fined variable you need to place '@' before the variable name.

Example

SET @name = 'Krishna';

SET @age = 25;

SET @phone = 9012345678;

You can retrieve the values of these variables using the SELECT statement as −

SELECT @name, @age, @phone;

Output

The above query produces the following output −

@name	@age	@phone
Krishna	25	9012345678

Setting values to local variables

You can define variables with in stored routines and set values to them using the SET statement.

Example

Following is an example of declaring local variables −

DELIMITER //
CREATE PROCEDURE RepeatExample()
BEGIN
   DECLARE val INT;
   DECLARE squares INT;
   DECLARE res VARCHAR(100);
   SET val=1;
   SET squares=1;
   SET res = '';
   REPEAT
      SET squares = val*val;
      SET res = CONCAT(res, squares,',');
      SET val = val + 1;
   UNTIL val >= 10
   END REPEAT;
   SELECT res;
END//
DELIMITER ;

You can call the above procedure as follows −

CALL RepeatExample;

Output

Following is the output of the above query −

res
1,4,9,16,25,36,49,64,81,

Setting values to system variables

MySQL system variables holds global or session level values, these variables are used to configure various operations. You can set values to these variables dynamically using the SET statement

Example

Let us verify whether loading local data is enabled, if not you can observe the local_infile variable value as −

SHOW GLOBAL VARIABLES LIKE 'local_infile';

Output

The above mysql query generates the following output −

Variable_name	Value
local_infile	OFF

Following query enables local_infile option as −

SET GLOBAL local_infile = 'ON';

If you verify the value of the variable local_infile again, you can observe the changed value as −

SHOW GLOBAL VARIABLES LIKE 'local_infile';

Output

Following is the output of the above mysql query −

Variable_name	value
local_infile	ON