SET statement is used for assigning the values for different types of variables which affects the operation of your client or the server. In this section we are describing you the use of SET statement to assign the values for system variables or user vari
SET statement is used for assigning the values for different types of variables which affects the operation of your client or the server. In this section we are describing you the use of SET statement to assign the values for system variables or user variThe general syntax of SET statement is :
SET variable_assignment [,
variable_assignment] ...
variable_assignment : user_var_name = expr | [GLOBAL | SESSION] system_var_name = expr | [@@global. | @@session. | @@]system_var_name = expr
SET statement is used for assigning the values for different types of variables which affects the operation of your client or the server. In this section we are describing you the use of SET statement to assign the values for system variables or user variables.
Some variants of SET syntax are used in other contexts:
mysql> SET PASSWORD FOR chand=PASSWORD('chand2'); Query OK, 0 rows affected (0.00 sec) |
mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL -> REPEATABLE READ; Query OK, 0 rows affected (0.00 sec) |
mysql> delimiter // mysql> CREATE PROCEDURE Setdemo(OUT a VARCHAR(30)) -> SET a='Demo of Set Syntax'; -> // Query OK, 0 rows affected (0.20 sec) mysql> delimiter ; mysql> CALL Setdemo(@a); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @a; +--------------------+ | @a | +--------------------+ | Demo of Set Syntax | +--------------------+ 1 row in set (0.01 sec) |
A user variable can written as @variable_name and it
can set as follows :
mysql> SET @b='Rahul'; Query OK, 0 rows affected (0.03 sec) mysql> SELECT @b; +-------+ | @b | +-------+ | Rahul | +-------+ 1 row in set (0.00 sec) |
Many system variables are dynamic and while the server running it can be change by using of SET statement. For changing a system variable by SET statement refer it as variable_name optionally it can be preceded by a modifier.
We can get the list of system variable names and values by the SHOW VARIABLES statement.
The following list describes you the options which have non standard syntax and these option are not displayed by SHOW variables, you can obtain their values by SELECT.
The lettercase of these options does not matter.
mysql> SELECT @@AUTOCOMMIT; +--------------+ | @@AUTOCOMMIT | +--------------+ | 1 | +--------------+ 1 row in set (0.01 sec) mysql> SET AUTOCOMMIT=0; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@AUTOCOMMIT; +--------------+ | @@AUTOCOMMIT | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec) |
mysql> SELECT @@BIG_TABLES; +--------------+ | @@BIG_TABLES | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec) |
mysql> SELECT @@FOREIGN_KEY_CHECKS; +----------------------+ | @@FOREIGN_KEY_CHECKS | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec) |
mysql> SELECT @@IDENTITY; +------------+ | @@IDENTITY | +------------+ | 0 | +------------+ 1 row in set (0.01 sec) |
mysql> DESCRIBE Emp; +-------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+----------------+ | Eid | int(10) unsigned | NO | PRI | | auto_increment | | Ename | varchar(22) | NO | | | | | City | varchar(15) | YES | MUL | | | | Designation | varchar(20) | YES | | | | | Salary | int(11) | YES | | | | | Perks | int(11) | YES | | | | +-------------+------------------+------+-----+---------+----------------+ 6 rows in set (0.11 sec) mysql> SELECT * FROM EMP; +-----+---------+-----------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+-----------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 11255 | 879 | | 2 | Suman | Mumbai | Designer | 20600 | 865 | | 3 | Chandan | Delhi | G Manager | 25750 | 986 | | 4 | Amar | Hyderabad | Developer | 15000 | 899 | +-----+---------+-----------+-------------+--------+-------+ 4 rows in set (0.01 sec) mysql> SET INSERT_ID=7; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO Emp (Ename,City,Designation,Salary,Perks) -> VALUES ('Ravi','Hyderabad','Programmer',18000,899); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM EMP; +-----+---------+-----------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+-----------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 11255 | 879 | | 2 | Suman | Mumbai | Designer | 20600 | 865 | | 3 | Chandan | Delhi | G Manager | 25750 | 986 | | 4 | Amar | Hyderabad | Developer | 15000 | 899 | | 7 | Ravi | Hyderabad | Programmer | 18000 | 899 | +-----+---------+-----------+-------------+--------+-------+ 5 rows in set (0.00 sec) |
mysql> SELECT @@LAST_INSERT_ID; +------------------+ | @@LAST_INSERT_ID | +------------------+ | 7 | +------------------+ 1 row in set (0.00 sec) |
mysql> SET ONE_SHOT max_allowed_packet=2; ERROR 1382 (HY000): The 'SET ONE_SHOT' syntax is reserved for purposes internal to the MySQL server |
But when it is used with allowed variables then it changes the value of variables but only for the next non SET statement.
Example :
mysql> SET ONE_SHOT collation_connection=latin5_turkish_ci; Query OK, 0 rows affected (0.11 sec) mysql> SHOW VARIABLES LIKE '%_connection'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_connection | latin5 | | collation_connection | latin5_turkish_ci | +--------------------------+-------------------+ 2 rows in set (0.00 sec) mysql> SHOW VARIABLES LIKE '%_connection'; +--------------------------+-------------------+ | Variable_name | Value | +--------------------------+-------------------+ | character_set_connection | latin1 | | collation_connection | latin1_swedish_ci | +--------------------------+-------------------+ 2 rows in set (0.00 sec) |
mysql> SET SQL_SAFE_UPDATES=1; Query OK, 0 rows affected (0.01 sec) mysql> DELETE FROM Emp WHERE Perks>900; ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column mysql> SET SQL_SAFE_UPDATES=0; Query OK, 0 rows affected (0.01 sec) mysql> DELETE FROM Emp WHERE Perks>900; Query OK, 1 row affected (0.03 sec) |
mysql> SELECT * FROM Emp; +-----+---------+-----------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+---------+-----------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 11255 | 879 | | 2 | Suman | Mumbai | Designer | 20600 | 865 | | 3 | Chandan | Delhi | G Manager | 25750 | 986 | | 4 | Amar | Hyderabad | Developer | 15000 | 899 | | 7 | Ravi | Hyderabad | Programmer | 18000 | 899 | +-----+---------+-----------+-------------+--------+-------+ 5 rows in set (0.00 sec) mysql> SET SQL_SELECT_LIMIT=2; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM Emp; +-----+-------+--------+-------------+--------+-------+ | Eid | Ename | City | Designation | Salary | Perks | +-----+-------+--------+-------------+--------+-------+ | 1 | Rahul | Delhi | Manager | 11255 | 879 | | 2 | Suman | Mumbai | Designer | 20600 | 865 | +-----+-------+--------+-------------+--------+-------+ 2 rows in set (0.00 sec) |
Ads