MySQL的字符集
笺注：这是在 [url=http://blog.zhuohua.store/viewthread.php?tid=331&extra=page%3D1]CentOS6.9编译安装Zabbix4.4.5[/url] 的基础上进行的。服务器本地登录MySQL数据库：（使用的是数据库管理员root@localhost，密码888）
mysql -u"root" -p"[color=DarkRed]888[/color]"
[attach]19415[/attach]
查看数据库的默认字符集：
show variables like '%character%';show variables like '%collation%';
[attach]19416[/attach]
注意：
character_set_database、character_set_server
collation_database、collation_server
注释：此时数据库的默认字符集为 [color=Blue]utf8mb4_general_ci [/color]
############
直接新建一个库，不指定其字符集：
mysql> create database data1;
Query OK, 1 row affected (0.00 sec)
mysql> show create database data1;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| data1 | [color=Purple]CREATE DATABASE `data1` /*!40100 DEFAULT CHARACTER SET utf8mb4 */[/color] |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
注释：库data1的字符集为 [color=Blue]utf8mb4_general_ci [/color]
在库data1里新建一个表：
use data1;
create table t_1(id int);
show create table t_1;
[attach]19417[/attach]
############
############
创建多个不同字符集的库，再查看各个字符集的显示效果：
库[color=Blue]db1[/color]的字符集为 [color=DarkRed]utf8_general_ci [/color]：
mysql> create database [color=Blue]db1[/color] character set [color=DarkRed]utf8[/color] collate [color=DarkRed]utf8_general_ci[/color];
Query OK, 1 row affected (0.05 sec)
mysql> show create database [color=Blue]db1[/color];
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| db1 | [color=Purple]CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */[/color] |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
注释：utf8_general_[color=Blue]ci[/color]不区分英文字母大小写；[color=Blue]ci[/color]是case insensitive的缩写，即大小写不敏感。
############
库[color=Blue]db2[/color]的字符集为 [color=DarkRed]utf8mb4_general_ci[/color] ：
mysql> create database [color=Blue]db2[/color] character set [color=DarkRed]utf8mb4[/color] collate [color=DarkRed]utf8mb4_general_ci[/color];
Query OK, 1 row affected (0.00 sec)
mysql> show create database [color=Blue]db2[/color];
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| db2 | [color=Purple]CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */[/color] |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
############
库[color=Blue]db3[/color]的字符集为 [color=DarkRed]utf8_unicode_ci[/color] ：
mysql> create database [color=Blue]db3[/color] character set [color=DarkRed]utf8[/color] collate [color=DarkRed]utf8_unicode_ci[/color];
Query OK, 1 row affected (0.07 sec)
mysql> show create database [color=Blue]db3[/color];
+----------+--------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------------------------+
| db3 | [color=Purple]CREATE DATABASE `db3` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */[/color] |
+----------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
############
库[color=Blue]db4[/color]的字符集为 [color=DarkRed]utf8mb4_unicode_ci[/color] ：
mysql> create database [color=Blue]db4[/color] character set [color=DarkRed]utf8mb4[/color] collate [color=DarkRed]utf8mb4_unicode_ci[/color];
Query OK, 1 row affected (0.00 sec)
mysql> show create database [color=Blue]db4[/color];
+----------+--------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------------------------------------+
| db4 | [color=Purple]CREATE DATABASE `db4` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */[/color] |
+----------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
############
库[color=Blue]db5[/color]的字符集为 [color=DarkRed]utf8_bin[/color] ：
mysql> create database [color=Blue]db5[/color] character set [color=DarkRed]utf8[/color] collate [color=DarkRed]utf8_bin[/color];
Query OK, 1 row affected (0.06 sec)
mysql> show create database [color=Blue]db5[/color];
+----------+-------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------+
| db5 | [color=Purple]CREATE DATABASE `db5` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */[/color] |
+----------+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
注释：utf8_[color=DarkRed]bin[/color]区分英文字母大小写。
############
############
测试utf8_general_[color=Blue]ci[/color]、utf8_[color=DarkRed]bin[/color]的区别：
mysql> use [color=Blue]db1[/color];
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 = '[color=DarkRed]Zhuohua[/color]';
+------+---------+
| id | name |
+------+---------+
| 1 | Zhuohua |
+------+---------+
1 row in set (0.01 sec)
mysql>
mysql> select * from table1 where name = '[color=DarkRed]zhuohua[/color]';
+------+---------+
| id | name |
+------+---------+
| 1 | Zhuohua |
+------+---------+
1 row in set (0.00 sec)
mysql>
结论：使用utf8_general_[color=Blue]ci[/color]时，关键字不区分英文字母大小写。
############
mysql> use [color=Blue]db5[/color];
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 = '[color=DarkRed]zhuohua[/color]';
[color=Red]Empty set (0.00 sec)[/color]
mysql>
mysql> select * from table5 where name = '[color=DarkRed]Zhuohua[/color]';
+------+---------+
| id | name |
+------+---------+
| 1 | Zhuohua |
+------+---------+
1 row in set (0.00 sec)
mysql>
结论：使用utf8_[color=DarkRed]bin[/color]时，关键字区分英文字母大小写。
############
############
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 ~]#
############
下面，修改数据库的默认字符集为 [color=Blue]utf8_general_ci[/color]
修改MySQL的主配置文件：
[root@Zabbix_server_01 ~]# vi /etc/my.cnf
在[color=DarkRed][mysqld][/color]下添加：
[color=Blue]character-set-server = utf8[/color]
[color=Blue]collation-server = utf8_general_ci[/color]
完成的效果如下：
[root@Zabbix_server_01 ~]# cat /etc/my.cnf |grep -v "^$"
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
[color=DarkRed][mysqld][/color]
[color=Blue]character-set-server = utf8
collation-server = utf8_general_ci[/color]
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%';"
[attach]19418[/attach]
注意：
character_set_database、character_set_server
collation_database、collation_server
注释：此时数据库的默认字符集为 [color=Blue]utf8_general_ci [/color]
备注：此方法适用于MySQL5.5、MySQL5.6、MySQL5.7、MySQL8.0
############
修改了数据库的默认字符集后，新建的库才会默认使用 [color=Blue]utf8_general_ci [/color]:
再直接新建一个库，不指定其字符集：
mysql> create database data2;
Query OK, 1 row affected (0.00 sec)
mysql> show create database data2;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| data2 | [color=Purple]CREATE DATABASE `data2` /*!40100 DEFAULT CHARACTER SET utf8 */[/color] |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
注释：此时库data2的字符集为[color=Blue] utf8_general_ci[/color]
在库data2里新建一个表：
use data2;
create table t_2(id int);
show create table t_2;
[attach]19419[/attach]
注释：表的字符集默认会跟随所属的库的字符集。
############
修改某个已经创建好的库的字符集：
mysql> alter database [color=Blue]data2[/color] default character set [color=DarkRed]utf8mb4[/color] collate [color=DarkRed]utf8mb4_general_ci[/color];
Query OK, 1 row affected (0.00 sec)
mysql> show create database data2;
+----------+-------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------+
| data2 | [color=Purple]CREATE DATABASE `data2` /*!40100 DEFAULT CHARACTER SET utf8mb4 */[/color] |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
注释：此时库data2的字符集为 [color=Blue]utf8mb4_general_ci[/color]
############
修改某个已经创建好的表的字符集：
mysql> alter table [color=Blue]data2.t_2[/color] convert to character set [color=DarkRed]utf8mb4[/color];
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
show create table [color=Blue]data2.t_2[/color];
[attach]19420[/attach]
############
下面，修改数据库的默认字符集为 [color=Blue]utf8mb4_general_ci[/color]
修改MySQL的主配置文件：
[root@Zabbix_server_01 ~]# vi /etc/my.cnf
在[color=DarkRed][mysqld][/color]下添加或修改：
character-set-server = [color=Blue]utf8mb4[/color]
collation-server = [color=Blue]utf8mb4_general_ci[/color]
重启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%';"
[attach]19421[/attach]
注意：
character_set_database、character_set_server
collation_database、collation_server
注释：此时数据库的默认字符集为[color=Blue] utf8mb4_general_ci[/color]
备注：此方法适用于MySQL5.5、MySQL5.6、MySQL5.7、MySQL8.0
笺注：
utf8mb4是utf8的超集，兼容utf8，能存储4字节的unicode。
utf8_[color=Blue]general[/color]_ci和utf8_[color=DarkRed]unicode[/color]_ci对中、英文来说没有实质性的差别；如果应用程序中有德语、法语、俄语等等，就一定要使用utf8_[color=DarkRed]unicode[/color]_ci。
假如字符集不匹配，会出现错误的，所以数据库的字符集的选择，要问该应用程序的开发人员。
相关文章：
[url=http://blog.zhuohua.store/viewthread.php?tid=256&extra=page%3D1]CentOS8编译安装Zabbix4.4.5[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=491&extra=page%3D1]CentOS8_LAMP_编译安装Zabbix5.0.12[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=483&extra=page%3D1]CentOS8_lnmp1.7_LAMP_安装Cacti[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=513&page=1&extra=#pid941]CentOS8使用Python3脚本管理MariaDB[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=90&extra=page%3D1]Windows2008R2_安装MySQL5.5[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=239&extra=page%3D1]MySQL的SQL语句[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=235&extra=page%3D1]MySQL的表的存储引擎[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=238&page=1&extra=#pid241]Linux下MySQL的备份和还原[/url]
[url=http://blog.zhuohua.store/viewthread.php?tid=231&page=1&extra=#pid234]使用Navicat远程管理MySQL[/url]
MariaDB10.3修改默认字符集，
可参考：[url=http://blog.zhuohua.store/viewthread.php?tid=403&page=1&extra=#pid831]CentOS8安装LNMP+phpMyAdmin[/url]
MySQL8.0修改默认字符集，
可参考：[url=http://blog.zhuohua.store/viewthread.php?tid=406&page=1&extra=#pid834]CentOS8_Yum安装MySQL8.0[/url]
#################################
#################################
[url=https://weidian.com/?userid=823531601&wfr=wx&sfr=app&source=shop]亲，学习研究也要劳逸结合哦，来我微店逛逛，买点东西好好犒劳犒劳自己和家人吧^_^^_^[/url]
[url=https://weidian.com/item.html?itemID=905482571143024037958&wfr=wx&sfr=app&source=goods_home]苏泊尔多功能电热锅韩式电火锅8-10人家用电炒锅不粘锅电锅电烤锅[/url]
[url=https://weidian.com/item.html?itemID=905482571143024037958&wfr=wx&sfr=app&source=goods_home][attach]7122[/attach][/url]
[url=https://weidian.com/item.html?itemID=905482571143024040619&wfr=wx&sfr=app&source=goods_home]苏泊尔电火锅多功能家用电热锅不沾锅一体电煮锅宿舍4-6人[/url]
[url=https://weidian.com/item.html?itemID=905482571143024040619&wfr=wx&sfr=app&source=goods_home][attach]7123[/attach][/url]
页:
[1]