### 模糊搜索系統(tǒng)變量中包含conn的所有變量，精確匹配時(shí)值不包含%
mysql> show variables like '%conn%';
+-----------------------------------------------+-----------------+
| Variable_name                                 | Value           |
+-----------------------------------------------+-----------------+
| character_set_connection                      | utf8            |
| collation_connection                          | utf8_general_ci |
| connect_timeout                               | 10              |
| disconnect_on_expired_password                | ON              |
| init_connect                                  |                 |
| max_connect_errors                            | 100             |
| max_connections                               | 400             |
| max_user_connections                          | 0               |
| performance_schema_session_connect_attrs_size | 512             |
+-----------------------------------------------+-----------------+

[mysqld]
datadir=/usr/local/mysql-5.7.26/data
default-storage-engine=MyISAM
max-connections=10
### 其它配置省略

mysql> show variables like '%datadir%';
+---------------+-------------------------------+
| Variable_name | Value                         |
+---------------+-------------------------------+
| datadir       | /usr/local/mysql-5.7.26/data/ |
+---------------+-------------------------------+
1 row in set (0.00 sec)

mysql> show variables like '%default-storage-engine%';
Empty set (0.01 sec)
#### 注意搜索系統(tǒng)變量名字的區(qū)別?。?！
mysql> show variables like '%default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)

###?root用戶執(zhí)行采用配置--user=root，非root用戶不用
mysqld?--user=root?--max-connections=111

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 111   |
+-----------------+-------+
1 row in set (0.00 sec)

#### 先查詢一次存儲引擎的值
mysql> show variables like '%default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)

mysql> set default_storage_engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%default_storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

### 語法一：默認(rèn)采用session
mysql> set default_storage_engine=InnoDB;
### 語法二：指定作用范圍session
mysql> set session default_storage_engine InnoDB;
### 語法三
mysql> set @@session.default_storage_engine=InnoDB;

### 語法一
mysql> set global default_storage_engine=InnoDB;
### 語法二
mysql> set @@global.default_storage_engine=MyISAM;

### 查詢session當(dāng)前客戶端存儲引擎的系統(tǒng)變量
mysql> show session variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)
### 查詢global所有客戶端存儲引擎的系統(tǒng)變量
mysql> show global  variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.01 sec)

### 改變?nèi)窒到y(tǒng)變量【默認(rèn)存儲引擎】
mysql> set global default_storage_engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)

### 再次查詢?nèi)窒到y(tǒng)變量【默認(rèn)存儲引擎】值，修改成功?。。。。。?！
mysql> show global  variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.00 sec)

### 再次查詢當(dāng)前客戶端【默認(rèn)存儲引擎】值，沒有被修改??！
mysql> show session variables like 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
1 row in set (0.00 sec)

### 初始值默認(rèn)0
mysql> show variables like 'insert_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| insert_id     | 0     |
+---------------+-------+
1 row in set (0.00 sec)
### 設(shè)置初始值為2
mysql> set session insert_id=2;
Query OK, 0 rows affected (0.00 sec)

### 創(chuàng)建一張表，帶有自增長AUTO_INCREMENT列
mysql> CREATE TABLE tb_student(
    -> id INT(4) PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(25) NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb_student(name) values('zhangsan');
Query OK, 1 row affected (0.00 sec)

### 自增長初始值由0變?yōu)?，設(shè)置成功??！
mysql> select * from tb_student;
+----+----------+
| id | name     |
+----+----------+
|  2 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

mysql> set global insert_id=2;
ERROR 1228 (HY000): Variable 'insert_id' is a SESSION variable and can't be used with SET GLOBAL

mysql> set max_connections=123;
ERROR 1229 (HY000): Variable 'max_connections' is a GLOBAL variable and should be set with SET GLOBAL

mysql> show variables like 'version';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.7.26 |
+---------------+--------+
1 row in set (0.01 sec)

### 只讀系統(tǒng)變量修改報(bào)錯
mysql> set version='1.0.0';
ERROR 1238 (HY000): Variable 'version' is a read only variable

### 默認(rèn)查詢session作用范圍的數(shù)據(jù)
mysql> show status like '%threads%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 0     |
| Threads_connected      | 2     |
| Threads_created        | 2     |
| Threads_running        | 1     |
+------------------------+-------+