MySQL @和@@符号的详细使用说明

1 用户自定义变量

• 用户定义变量语法

SET @var_name = expr [, @var_name = expr]

如：

set @t1 =100;

• 获取用户定义变量值方式，如：

select @t1 from dual;

2 系统变量

• 查看全部系统变量指令

SHOW GLOBAL VARIABLES

• 查看单个系统变量

SHOW GLOBAL VARIABLES LIKE 'wait_timeout'

• 设置系统变量语法

SET GLOBAL 变量名 = 变量值 

如：

SET GLOBAL wait_timeout = 604800;

注：如果修改变量值后没有生效，请退出从新再试下 。

• 获取系统变量值的语法

select @@wait_timeout  from dual;

3 Demo

mysql> set @hey_baby=998;
Query OK, 0 rows affected (0.00 sec)

mysql> select @hey_baby;
+-----------+
| @hey_baby |
+-----------+
|       998 |
+-----------+
1 row in set (0.00 sec)

mysql> select @hey_baby from dual;
+-----------+
| @hey_baby |
+-----------+
|       998 |
+-----------+
1 row in set (0.00 sec)

mysql> show global variables like "%timeout";
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| clone_ddl_timeout            | 300      |
| connect_timeout              | 30       |
| delayed_insert_timeout       | 300      |
| have_statement_timeout       | YES      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 100      |
| innodb_rollback_on_timeout   | OFF      |
| interactive_timeout          | 28800    |
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| replica_net_timeout          | 60       |
| rpl_semi_sync_source_timeout | 10000    |
| rpl_stop_replica_timeout     | 31536000 |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 60       |
| thread_pool_idle_timeout     | 60       |
| wait_timeout                 | 28800    |
+------------------------------+----------+
18 rows in set (0.00 sec)

mysql> set global wait_timeout=27777;
Query OK, 0 rows affected (0.01 sec)

mysql> show global variables like "%timeout";
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| clone_ddl_timeout            | 300      |
| connect_timeout              | 30       |
| delayed_insert_timeout       | 300      |
| have_statement_timeout       | YES      |
| innodb_flush_log_at_timeout  | 1        |
| innodb_lock_wait_timeout     | 100      |
| innodb_rollback_on_timeout   | OFF      |
| interactive_timeout          | 28800    |
| lock_wait_timeout            | 31536000 |
| net_read_timeout             | 30       |
| net_write_timeout            | 60       |
| replica_net_timeout          | 60       |
| rpl_semi_sync_source_timeout | 10000    |
| rpl_stop_replica_timeout     | 31536000 |
| rpl_stop_slave_timeout       | 31536000 |
| slave_net_timeout            | 60       |
| thread_pool_idle_timeout     | 60       |
| wait_timeout                 | 27777    |
+------------------------------+----------+
18 rows in set (0.00 sec)


mysql> select @@wait_timeout from dual;
+----------------+
| @@wait_timeout |
+----------------+
|          28800 |
+----------------+
1 row in set (0.00 sec)

mysql> select @@wait_timeout from dual;
+----------------+
| @@wait_timeout |
+----------------+
|          28800 |
+----------------+
1 row in set (0.00 sec)

mysql> 

relogin

mysql> select @@wait_timeout from dual;
+----------------+
| @@wait_timeout |
+----------------+
|          28800 |
+----------------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'wait_timeout'
    -> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 27777 |
+---------------+-------+
1 row in set (0.00 sec)

mysql>