| 知乎专栏 ｜ 多维度架构 |
show variables; show global variables;
show variables where Variable_name like 'collation%' or Variable_name like 'character_set_server%';
SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION'; SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';
导入数据库遇到这样的问题
[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';
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)
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)
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)
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)
mysql> SHOW VARIABLES LIKE 'collation_server'; +------------------+-------------------+ | Variable_name | Value | +------------------+-------------------+ | collation_server | latin1_swedish_ci | +------------------+-------------------+ 1 row in set (0.01 sec) mysql>
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
SHOW VARIABLES LIKE 'datadir';
mysql> SHOW VARIABLES LIKE 'datadir'; +---------------+-------------------------+ | Variable_name | Value | +---------------+-------------------------+ | datadir | /var/lib/mysql/ | +---------------+-------------------------+ 1 row in set (0.00 sec)
mysql> show variables like '%plugin_dir%'; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | plugin_dir | /usr/lib/mysql/plugin/ | +---------------+------------------------+ 1 row in set (0.00 sec)
mysql> show variables like '%storage_engine%'; +------------------------+--------+ | Variable_name | Value | +------------------------+--------+ | default_storage_engine | InnoDB | | storage_engine | InnoDB | +------------------------+--------+ 2 rows in set (0.00 sec)