SET Syntax

The 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: 

• SET PASSWORD statement is used to assign the account passwords. Example :
           

  mysql> SET PASSWORD FOR chand=PASSWORD('chand2'); Query OK, 0 rows affected (0.00 sec)

          
• SET TRANSACTION ISOLATION LEVEL is used to set the isolation level for transaction process. Example :
        

  mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL -> REPEATABLE READ; Query OK, 0 rows affected (0.00 sec)

      
• SET is used store procedure or function for assigning the values to local variables. Example :
       

  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.

• For specifying explicitly a variable is a global variable we can precede it by GLOBAL or @@global… But for this you must have have super privilege.
• For specifying explicitly a variable is a session variable we can precede it by SESSION or @@session or @@. For this you don’t require any special privilege but a client can change its own session variable only. LOCAL and @@local. are synonyms for SESSION and @@session..
• If no modifier is present, SET changes the session variable. 

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. 

• AUTOCOMMIT = {0 | 1} 
  It is used to set the autocommit mode. If it is set to 1 then all changes to a table take effect immediately. But if it is set to 0 then we have to use COMMIT to for accepting the transaction or ROLLBACK for canceling it. By default it is set to 1. Example :
       

  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)

         
• BIG_TABLES = {0 | 1} 
  If this variable is set to 1 then all temporary tables are stored on dist not in the memory. But its default value is 0. Generally you don’t need to set this variable. Example :
        

  mysql> SELECT @@BIG_TABLES; +--------------+ | @@BIG_TABLES | +--------------+ | 0 | +--------------+ 1 row in set (0.00 sec)

         
• FOREIGN_KEY_CHECKS = {0 | 1} 
  Its default value is 1 means foreign key constraints are checked to InnoDB tables but if it is 0 then they are ignored and it can be useful to reload the InnoDB tables in an order different from that required by their parent/child relationships. Example :
       

  mysql> SELECT @@FOREIGN_KEY_CHECKS; +----------------------+ | @@FOREIGN_KEY_CHECKS | +----------------------+ | 1 | +----------------------+ 1 row in set (0.00 sec)

       
• IDENTITY = value 
  It is a synonym to the LAST_INSERT_ID variable. It exists for compatibility with other database system and we can value of this variable by SELECT @@IDENTITY. Example :
         

  mysql> SELECT @@IDENTITY; +------------+ | @@IDENTITY | +------------+ | 0 | +------------+ 1 row in set (0.01 sec)

       
• INSERT_ID = value 
  This variable is used to set the value that is used by the subsequent INSERT or ALTER TABLE statement when inserting an AUTO_INCREMENT values. Example :
       

  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)

        
• LAST_INSERT_ID = value 
  This variable is used to set the values to returned from LAST_INSERT_ID() and it is stored in binary log when you use LAST_INSERT_ID() in a statement that updates a table. Example :
       

  mysql> SELECT @@LAST_INSERT_ID; +------------------+ | @@LAST_INSERT_ID | +------------------+ | 7 | +------------------+ 1 row in set (0.00 sec)

       
• ONE_SHOT 
  It is not a variable infect it is a modifier and it is used to influence the effect of variables which set the character set, the collation and the time zone. It is used to modify the temporarily values of character set, collation and time zone variables. It is only for internal use. We cannot use it other than allowed set ot variables but if you try then you get the error. Example :
       

  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)

         
• SQL_BIG_SELECTS = {0 | 1} 
  Its default value is 1 that allows all SELECT statements. But if it is 0 then MySQL aborts the SELECT statements that can take a very long time to execute (means the optimizer estimates the number of examined rows for a statement that exceeds the value of max_join_size). 
        
• SQL_BUFFER_RESULT = {0 | 1} 
  Its default value is 0 but if it is 1 then it forces the results from SELECT statement to be put into temporary tables. It can helpful to MySQL to free the table locks and also in that cases where it takes a long time for sending the results to client.
        
• SQL_LOG_BIN = {0 | 1} 
  Its default value is 1 but if it is set to 1 then no logging is done for the binary log for the client and for setting this option the client required the SUPER privilege. 
          
• SQL_LOG_OFF = {0 | 1} 
  Its default value is 0 but if it set to 1 then no logging is done for the general query log to this client and for setting this option the client required the SUPER privilege. 
          
• SQL_QUOTE_SHOW_CREATE = {0 | 1} 
  Its default value is 1 means the server quotes identifiers for SHOW CREATE DATABASE and SHOW CREATE TABLE statements but if it is 0 the quoting is disabled. By default it is enabled that’s why the replication works for identifiers which require quoting. 
           
• SQL_SAFE_UPDATES = {0 | 1} 
  Its default value is 0 but if it is 1 then MySQL aborts the DELETE and UPDATE statements which don’t use a key in the WHERE or a LIMIT clause. It is helpful to catch DELETE and UPDATE statements where keys are not used properly and which would probably change. Example :
       

  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)

      
• SQL_SELECT_LIMIT = {value | DEFAULT} 
  This variable is used to return the maximum number of rows from SELECT statements. Its default value is “unlimited”. But if you changed the limit then SELECT statement returns the rows equals to the value. But if SELECT statement with a LIMIT clause then the LIMIT clause takes the precedence over the value of SQL_SELECT_LIMIT. This variable does not apply to SELECT statement that executed in the stored procedures or functions. Example :
        

  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)

       
• TIMESTAMP = {timestamp_value | DEFAULT} 
  This variable is used to set the time for the client. It is used for getting the original timestamp if you are using the binary log to restore the rows. Its value is a Unix epoch timestamp rather than a MySQL timestamp. SET TIMESTAMP can affects the value that’s returned by NOW() rather than by SYSDATE(). 
           
• UNIQUE_CHECKS = {0 | 1} 
  Its default value is 1, in InnoDB tables performs uniqueness checks for secondary indexes. If it is 0 then storage engines allowed to assume that in input data duplicate keys are not available.