MySQL修改默认引擎和字符集详情

吾爱主题阅读：215 2024-04-02 08:06:06 评论：0

一、数据库引擎

1.1 查看数据库引擎

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> show engines; + --------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | + --------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | DEFAULT | Supports transactions, row- level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/ null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | + --------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)

可以看到默认引擎是 InnoDB。

1.2 修改默认数据库引擎

1.打开配置文件

1	`[root@VM_0_15_centos ~]# vim /etc/my.cnf`

2.在最下面编辑如下内容：

1	`default` `-storage-engine=InnoDB`

3.重启服务

1	`[root@VM_0_15_centos ~]# systemctl restart mysqld`

二、数据库字符集

2.1 查看字符集

查看MYSQL数据库服务器和数据库字符集

1 2 3 4 5 6 7 8 9 10 11 12 13 14 mysql> show variables like '%character%' ; + --------------------------+----------------------------+ | Variable_name | Value | + --------------------------+----------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | + --------------------------+----------------------------+ 8 rows in set (0.01 sec)

字符集	作用
character_set_client	用来设置客户端使用的字符集。
character_set_connection	用来设置连接数据库时的字符集
character_set_database	用来设置默认创建数据库的编码格式
character_set_filesystem	文件系统的编码格式，把操作系统上的文件名转化成此字符集，默认binary是不做任何转换的
character_set_results	查询结果字符集
character_set_server	服务器安装时指定的默认编码格式
character_set_system	系统元数据(字段名等)字符集
character_sets_dir	字符集安装的目录

查看 MYSQL 所支持的字符集

1	`show charset;`

查看库的字符集

1	`show` `database` `status` `from` `库名` `like` `表名;`

查看表的字符集

1	`show` `table` `status` `from` `库名` `like` `表名;`

查看表中所有列的字符集

1	`show` `full` `columns` `from` `表名;`

2.2 修改字符集

1.打开配置文件

1	`[root@VM_0_15_centos ~]# vim /etc/my.cnf`

2.在最下面编辑如下内容：

1 2 3 character - set -server=utf8 [client] default - character - set =utf8

3.重启服务并验证

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 [root@VM_0_15_centos ~]# systemctl restart mysqld [root@VM_0_15_centos ~]# mysql -uroot -p Enter password : Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.27 MySQL Community Server (GPL) Copyright (c) 2000, 2019, Oracle and / or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and / or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 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)