最後更新: 2022-08-16

Variable 一共有兩類

• User-Defined (@var_name)
• System Variable

User-Defined Variable(@VAR)

User-defined variables are created locally within a session and
exist only within the context of that session;

System Variable (@@VAR)

configure MySQL operation

Many system variables are dynamic and can be changed at runtime
Setting a global system variable value always requires special privileges.
Setting a session system variable value normally requires no special privileges

Notes

A SET statement that assigns variable values is not written to the binary log,

so in replication scenarios it affects only the host on which you execute it.

 

---

User-Defined Variable

 

SET variable = expr [, variable = expr] ...

i.e.

SET @MyVar = 1 + 3;

SELECT @MyVar

 

---

Variable 的 Default 值是 NULL

 

SELECT @var1, @var2;

-> NULL, NULL

 

---

:= 與 =

 

• :=        Assignment operator must be
• =         Treated as a comparison

Within a SET statement, "=" is treated as an assignment operator

 => that causes the user variable on the left hand side of the operator to take on the value to its right.

e.g.

set @var1 = 1; 

SELECT @var1, @var2 := @var1;

-> 1, 1

 

---

SELECT 的 Result 放入 Variable

 

方式1: INTO

# 因為得一個 variable, 所以一定要 Limit 1 !!

SELECT id  FROM `mailaddr`  WHERE email = '[email protected]'  LIMIT 1 INTO @rid

方式2: ":="

SET @server := (SELECT server FROM gateway ORDER BY RAND() LIMIT 1);

方式3: 一次過 assign 多個 var

SELECT c1, c2, ... INTO @v1, @v2, ... FROM tablename WHERE condition;

 

---

Update 時用到 Variable:

 

UPDATE t1 SET c1 = 2 WHERE c1 = @var1:= 1;

Remark:

SELECT ... INTO

e.g.

Insert wblist(rid, sid, 'B')
    SELECT @rid = SELECT id FROM `mailaddr` WHERE email = '[email protected]'
    SELECT id FROM users WHERE email = '@datahunter.org';

 

---

stored in variables or written to a file

 

SELECT ... INTO var_list
SELECT ... INTO OUTFILE
# without any formatting
SELECT ... INTO DUMPFILE

 

---

CSV Output:

 

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N

 

---

System Variable

 

global system variable

SET GLOBAL max_connections = 1000;
SET @@GLOBAL.max_connections = 1000;

session system variable

SET SESSION sql_mode = 'TRADITIONAL';
SET LOCAL sql_mode = 'TRADITIONAL';
SET @@SESSION.sql_mode = 'TRADITIONAL';
SET @@LOCAL.sql_mode = 'TRADITIONAL';
SET @@sql_mode = 'TRADITIONAL';
SET sql_mode = 'TRADITIONAL';

Set to default value

SET @@sql_mode = DEFAULT;

 

Checking

SHOW VARIABLES