返回列表 发帖

MySQL的字符集

笺注:这是在 CentOS6.9编译安装Zabbix4.4.5 的基础上进行的。


服务器本地登录MySQL数据库:(使用的是数据库管理员root@localhost,密码888)
mysql -u"root" -p"888"
图片1.png
2022-4-12 08:42



查看数据库的默认字符集:
show variables like '%character%';show variables like '%collation%';
图片2.png
2022-4-12 08:42

注意:
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;

图片3.png
2022-4-12 08:43






############
############

创建多个不同字符集的库,再查看各个字符集的显示效果:

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%';"
图片4.png
2022-4-12 08:54

注意:
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;

图片5.png
2022-4-12 08:56

注释:表的字符集默认会跟随所属的库的字符集。





############

修改某个已经创建好的库的字符集:
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;
图片6.png
2022-4-12 08:58






############

下面,修改数据库的默认字符集为 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%';"
图片7.png
2022-4-12 08:58

注意:
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人家用电炒锅不粘锅电锅电烤锅
guo.png
2020-5-19 14:18


苏泊尔电火锅多功能家用电热锅不沾锅一体电煮锅宿舍4-6人
huoguo.png
2020-5-19 14:18

返回列表