查看数据库的默认字符集：
show variables like '%character%';show variables like '%collation%';
注意：
character_set_database、character_set_server
collation_database、collation_server
注释：此时数据库的默认字符集为 utf8mb4_general_ci
############
直接新建一个库，不指定其字符集：
mysql> create database data1;
Query OK, 1 row affected (0.00 sec)
mysql> show create database data1;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| data1 | CREATE DATABASE `data1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
注释：库data1的字符集为 utf8mb4_general_ci
在库data1里新建一个表：
use data1;
create table t_1(id int);
show create table t_1;
############
############
创建多个不同字符集的库，再查看各个字符集的显示效果：
库db1的字符集为 utf8_general_ci ：
mysql> create database db1 character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.05 sec)
mysql> show create database db1;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
注释：utf8_general_ci不区分英文字母大小写；ci是case insensitive的缩写，即大小写不敏感。
############
库db2的字符集为 utf8mb4_general_ci ：
mysql> create database db2 character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db2;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
############
库db3的字符集为 utf8_unicode_ci ：
mysql> create database db3 character set utf8 collate utf8_unicode_ci;
Query OK, 1 row affected (0.07 sec)
mysql> show create database db3;
+----------+--------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------------------------+
| db3 | CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */ |
+----------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
############
库db4的字符集为 utf8mb4_unicode_ci ：
mysql> create database db4 character set utf8mb4 collate utf8mb4_unicode_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database db4;
+----------+--------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------------------------------+
| db4 | CREATE DATABASE `db4` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
############
库db5的字符集为 utf8_bin ：
mysql> create database db5 character set utf8 collate utf8_bin;
Query OK, 1 row affected (0.06 sec)
mysql> show create database db5;
+----------+-------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------+
| db5 | CREATE DATABASE `db5` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
注释：utf8_bin区分英文字母大小写。
############
############
测试utf8_general_ci、utf8_bin的区别：
mysql> use db1;
Database changed
mysql>
mysql> create table table1(id tinyint,name varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into table1(id,name) values(1,'Zhuohua');
Query OK, 1 row affected (0.00 sec)
mysql> select * from table1 where name = 'Zhuohua';
+------+---------+
| id | name |
+------+---------+
| 1 | Zhuohua |
+------+---------+
1 row in set (0.01 sec)
mysql>
mysql> select * from table1 where name = 'zhuohua';
+------+---------+
| id | name |
+------+---------+
| 1 | Zhuohua |
+------+---------+
1 row in set (0.00 sec)
mysql>
结论：使用utf8_general_ci时，关键字不区分英文字母大小写。
############
mysql> use db5;
Database changed
mysql>
mysql> create table table5(id tinyint,name varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into table5(id,name) values(1,'Zhuohua');
Query OK, 1 row affected (0.00 sec)
mysql> select * from table5 where name = 'zhuohua';
Empty set (0.00 sec)
mysql>
mysql> select * from table5 where name = 'Zhuohua';
+------+---------+
| id | name |
+------+---------+
| 1 | Zhuohua |
+------+---------+
1 row in set (0.00 sec)
mysql>
结论：使用utf8_bin时，关键字区分英文字母大小写。
############
############
MySQL的主配置文件：（初始状态）
[root@Zabbix_server_01 ~]# cat /etc/my.cnf |grep -v "^$"
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
datadir = /usr/local/mysql/var
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 8M
tmp_table_size = 16M
#skip-networking
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
default_storage_engine = InnoDB
innodb_data_home_dir = /usr/local/mysql/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[root@Zabbix_server_01 ~]#
############
下面，修改数据库的默认字符集为 utf8_general_ci
修改MySQL的主配置文件：
[root@Zabbix_server_01 ~]# vi /etc/my.cnf
在[mysqld]下添加：
character-set-server = utf8
collation-server = utf8_general_ci
完成的效果如下：
[root@Zabbix_server_01 ~]# cat /etc/my.cnf |grep -v "^$"
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
[mysqld]
character-set-server = utf8
collation-server = utf8_general_ci
port = 3306
socket = /tmp/mysql.sock
datadir = /usr/local/mysql/var
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 8M
tmp_table_size = 16M
#skip-networking
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
default_storage_engine = InnoDB
innodb_data_home_dir = /usr/local/mysql/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
[root@Zabbix_server_01 ~]#
重启MySQL：
[root@Zabbix_server_01 ~]# service mysql restart
Shutting down MySQL... SUCCESS!
Starting MySQL... SUCCESS!
修改数据库的默认字符集成功：
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "show variables like '%character%';show variables like '%collation%';"
注意：
character_set_database、character_set_server
collation_database、collation_server
注释：此时数据库的默认字符集为 utf8_general_ci
备注：此方法适用于MySQL5.5、MySQL5.6、MySQL5.7、MySQL8.0
############
修改了数据库的默认字符集后，新建的库才会默认使用 utf8_general_ci :
再直接新建一个库，不指定其字符集：
mysql> create database data2;
Query OK, 1 row affected (0.00 sec)
mysql> show create database data2;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| data2 | CREATE DATABASE `data2` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
注释：此时库data2的字符集为 utf8_general_ci
在库data2里新建一个表：
use data2;
create table t_2(id int);
show create table t_2;
注释：表的字符集默认会跟随所属的库的字符集。
############
修改某个已经创建好的库的字符集：
mysql> alter database data2 default character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show create database data2;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| data2 | CREATE DATABASE `data2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
注释：此时库data2的字符集为 utf8mb4_general_ci
############
修改某个已经创建好的表的字符集：
mysql> alter table data2.t_2 convert to character set utf8mb4;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
show create table data2.t_2;
############
下面，修改数据库的默认字符集为 utf8mb4_general_ci
修改MySQL的主配置文件：
[root@Zabbix_server_01 ~]# vi /etc/my.cnf
在[mysqld]下添加或修改：
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
重启MySQL：
[root@Zabbix_server_01 ~]# service mysql restart
Shutting down MySQL... SUCCESS!
Starting MySQL... SUCCESS!
修改数据库的默认字符集成功：
[root@Zabbix_server_01 ~]# mysql -u"root" -p"888" -e "show variables like '%character%';show variables like '%collation%';"
注意：
character_set_database、character_set_server
collation_database、collation_server
注释：此时数据库的默认字符集为 utf8mb4_general_ci
备注：此方法适用于MySQL5.5、MySQL5.6、MySQL5.7、MySQL8.0
笺注：
utf8mb4是utf8的超集，兼容utf8，能存储4字节的unicode。
utf8_general_ci和utf8_unicode_ci对中、英文来说没有实质性的差别；如果应用程序中有德语、法语、俄语等等，就一定要使用utf8_unicode_ci。
假如字符集不匹配，会出现错误的，所以数据库的字符集的选择，要问该应用程序的开发人员。
相关文章：
CentOS8编译安装Zabbix4.4.5
CentOS8_LAMP_编译安装Zabbix5.0.12
CentOS8_lnmp1.7_LAMP_安装Cacti
CentOS8使用Python3脚本管理MariaDB
Windows2008R2_安装MySQL5.5
MySQL的SQL语句
MySQL的表的存储引擎
Linux下MySQL的备份和还原
使用Navicat远程管理MySQL
MariaDB10.3修改默认字符集，
可参考：CentOS8安装LNMP+phpMyAdmin
MySQL8.0修改默认字符集，
可参考：CentOS8_Yum安装MySQL8.0
#################################
#################################
亲，学习研究也要劳逸结合哦，来我微店逛逛，买点东西好好犒劳犒劳自己和家人吧^_^^_^
苏泊尔多功能电热锅韩式电火锅8-10人家用电炒锅不粘锅电锅电烤锅
苏泊尔电火锅多功能家用电热锅不沾锅一体电煮锅宿舍4-6人
图片附件: guo.png (2020-5-19 14:18, 470.68 KB) / 下载次数 76
图片附件: huoguo.png (2020-5-19 14:18, 444 KB) / 下载次数 68
图片附件: 图片1.png (2022-4-12 08:42, 46.65 KB) / 下载次数 83
图片附件: 图片2.png (2022-4-12 08:42, 87.06 KB) / 下载次数 85
图片附件: 图片3.png (2022-4-12 08:43, 19.1 KB) / 下载次数 73
图片附件: 图片4.png (2022-4-12 08:54, 74.4 KB) / 下载次数 81
图片附件: 图片5.png (2022-4-12 08:56, 18.91 KB) / 下载次数 75
图片附件: 图片6.png (2022-4-12 08:58, 12.46 KB) / 下载次数 57
图片附件: 图片7.png (2022-4-12 08:58, 78.85 KB) / 下载次数 54
| 欢迎光临 blog.zhuohua.store (http://blog.zhuohua.store/) | Powered by Discuz! 7.2 |