19.10. variables

19.10. variables
上一頁	第 19 章 MySQL Server	下一頁

show variables;
show global variables;

19.10.1. time_zone

		
SELECT @@global.time_zone, @@session.time_zone;

19.10.2. sql_mode

19.10.2.1. 設置 sql_mode

SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

19.10.2.2. 查看 sql_mode

SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;

19.10.2.3. 兼容早起 MySQL 版本

導入資料庫遇到這樣的問題

[root@netkiller]/tmp# cat cms.sql| mysql -uroot -p cms

ERROR 1067 (42000) at line 2194: Invalid default value for 'created_date'

將下面代碼加入到 cms.sql 頭部可以解決

set @@global.sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

19.10.2.4. 5.7.16

			
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.16    |
+-----------+
1 row in set (0.00 sec)

mysql> SELECT @@global.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

19.10.3. wait_timeout

		
mysql> show global variables like 'wait_timeout';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| wait_timeout               | 10    |
+----------------------------+-------+

		
mysql> use mysql;
Database changed

mysql> set wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables  like '%wait_timeout%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
| table_lock_wait_timeout  | 50    |
| wait_timeout             | 10    |
+--------------------------+-------+
3 rows in set (0.00 sec)

19.10.4. table_lock_wait_timeout

		
mysql> set GLOBAL table_lock_wait_timeout=10;
Query OK, 0 rows affected (0.00 sec)

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

19.10.5. low_priority_updates

		
mysql> use mysql
Database changed

mysql> SET LOW_PRIORITY_UPDATES=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables  like '%priority%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| low_priority_updates     | ON    |
| sql_low_priority_updates | ON    |
+--------------------------+-------+
2 rows in set (0.00 sec)

19.10.6. collation_server

		
mysql> SHOW VARIABLES LIKE 'collation_server';
+------------------+-------------------+
| Variable_name    | Value             |
+------------------+-------------------+
| collation_server | latin1_swedish_ci |
+------------------+-------------------+
1 row in set (0.01 sec)

mysql>

19.10.7. character_set

		
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

連結 MySQL 指定字符集

		
mysql -uroot -h 192.168.0.10 -p --default-character-set=latin1

19.10.8. datadir

 SHOW VARIABLES LIKE 'datadir';

		
mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| datadir       | /var/lib/mysql/         |
+---------------+-------------------------+
1 row in set (0.00 sec)

19.10.9. plugin_dir

show variables like '%plugin_dir%';

		
mysql> show variables like '%plugin_dir%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+
1 row in set (0.00 sec)

19.10.10. storage_engine

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

19.10.11. timeout

show variables like "%timeout%";

19.10.12. max_connections

show variables like "max_connections";

set global max_connections = 200;

上一頁	上一級	下一頁
19.9. MySQL Router	起始頁	19.11. SHOW COMMAND