MySQL原理 - 字符集与排序规则(下)

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: MySQL原理 - 字符集与排序规则(下)

库级别


创建数据库的时候,可以指定字符集还有排序规则。


mysql> create database test_db character set utf8mb4 collate utf8mb4_general_ci;
Query OK, 1 row affected (0.01 sec)


不指定的话,就用实例级别的字符集还有排序规则。


查看当前数据库的字符集还有排序规则则是通过use命令指定数据库之后,查看character_set_database变量以及collation_database来实现:


mysql> show variables like 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.07 sec)
mysql> show variables like 'collation_database';
+--------------------+--------------------+
| Variable_name      | Value              |
+--------------------+--------------------+
| collation_database | utf8mb4_general_ci |
+--------------------+--------------------+
1 row in set (0.09 sec)


就算设置这两个变量,也是无效的:


mysql> set character_set_database = 'utf8';
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'character_set_database';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.09 sec)


修改数据库的字符集还有排序规则的方式:


mysql> alter database test_db character set = 'utf8';
Query OK, 1 row affected (0.01 sec)
mysql> show variables like 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.08 sec)


这个更新只会对新建的表如果没指定字符集和排序规则的生效,并不会更新老表的字符集还有排序规则。


表级别


可以在创建时指定字符集合排序规则,不指定的话,用数据库的字符集还有排序规则,也可以修改字符集和排序规则。


mysql> create table test (name varchar(32)) character set utf8mb4 collate utf8mb4_bin; 
Query OK, 0 rows affected (0.04 sec)
mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `name` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)
mysql> alter table test character set = 'utf8';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table test;
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                         |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

可以看出,仅仅是表的字符集还有排序规则变了,对于已有字段,并没有改变编码和排序规则。


列级别


可以在创建表的时候,指定不同的列有不同的字符集和排序规则,也可以修改列的字符集和排序规则:


mysql> create table test (name varchar(32) character set utf8 collate utf8_bin) character set utf8mb4 collate utf8mb4_bin; 
Query OK, 0 rows affected (0.03 sec)
mysql> show create table test;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                          |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)
mysql> alter table test modify column name  varchar(32) COLLATE latin1_bin;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create table test;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                              |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `name` varchar(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.09 sec)


MySQL客户端字符编码问题


有时候,我们会遇到字符编码不一致导致的程序问题。例如我们的 Java 程序,使用 jdbc 链接。读取的数据,打印出来是乱码。或者是,MySQL 无法识别我们客户端发来的命令。这涉及到字符编码问题。我们需要保持 Java 程序的字符编码与 JDBC 链接指定的字符编码一致,这样才不会有乱码的问题。


**指定 Java 程序编码:**通过启动参数:-Dfile.encoding=UTF-8 设置默认的字符编码(java.nio.charset.Charset.defaultCharset();)是utf-8(对应 MySQL 的utf8还有utf8mb4)。


指定 JDBC 链接编码:

jdbc:mysql://127.0.0.1:3306/test?characterEncoding=utf8


mysql客户端命令行指定字符集


mysql -h 127.0.0.1 -P 3306 -u root  --default-character-set=utf8mb4  -p

之后查看有关编码的环境变量,都是和设置的这个字符集一样。


mysql> SHOW VARIABLES LIKE 'character_set_client';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| character_set_client | utf8mb4 |
+----------------------+---------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character_set_connection';
+--------------------------+---------+
| Variable_name            | Value   |
+--------------------------+---------+
| character_set_connection | utf8mb4 |
+--------------------------+---------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW VARIABLES LIKE 'character_set_results';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| character_set_results | utf8mb4 |
+-----------------------+---------+
1 row in set, 1 warning (0.00 sec)


其中:

  • character_set_client: 服务器解码请求时使用的字符集
  • character_set_connection:服务器处理请求时将字符集转换成这个字符集处理。操作具体列时,在转换为具体列的编码。
  • character_set_results:服务器向客户端返回数据时使用的字符集

MySQL 设计这三个编码的时候,出于以下考虑:

  • 一个 MySQL,可能有多种不同语言和操作系统或者国家的客户端,所以通过设置character_set_client还有character_set_results进行兼容。
  • 由于操作具体列数据的时候需要编码转换,如果character_set_connection和字段一致的话,就不用转换了,所以设置character_set_connection可以让 MySQL 用一种编码理解命令统一处理,同时设置character_set_connection为最常用的可以减少转换。


一般情况下,保持这三个一致就好。我们就设置好连接使用的字符集就行了。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
21天前
|
存储 关系型数据库 MySQL
深入理解MySQL索引:从原理到最佳实践
深入理解MySQL索引:从原理到最佳实践
44 0
|
24天前
|
安全 关系型数据库 MySQL
总结MySQL 的一些知识点:MySQL 排序(上)
总结MySQL 的一些知识点:MySQL 排序
|
4天前
|
存储 SQL 关系型数据库
MySQL事务底层原理和MVCC机制
MySQL事务底层原理和MVCC机制
14 1
|
8天前
|
SQL 存储 关系型数据库
MySQL索引原理以及SQL优化
MySQL索引原理以及SQL优化
43 0
|
21天前
|
存储 SQL 关系型数据库
MySQL - 深入理解 MySQL 的 MVCC 及实现原理
MySQL - 深入理解 MySQL 的 MVCC 及实现原理
|
24天前
|
SQL JavaScript 关系型数据库
|
25天前
|
存储 关系型数据库 MySQL
从零开始学Mysql - 字符集和编码(下)
从零开始学Mysql - 字符集和编码(下)
23 0
|
25天前
|
存储 SQL 关系型数据库
从零开始学Mysql - 字符集和编码(上)
从零开始学Mysql - 字符集和编码(上)
58 0
|
25天前
|
存储 关系型数据库 MySQL
面试题15: Mysql 数据存储原理
面试题15: Mysql 数据存储原理
|
30天前
|
存储 缓存 算法
作者推荐 | 【深入浅出MySQL】「底层原理」探秘缓冲池的核心奥秘,揭示终极洞察
MySQL作为一个存储系统,有着一个关键的优化机制——缓冲池(buffer pool),它极大地提高了数据的访问效率,避免了频繁的磁盘IO操作。通过将常用的数据存储在内存中,MySQL可以快速响应查询请求,减少耗时的磁盘访问。这一优化机制在提升数据库性能方面起到了重要的作用。
40 7
作者推荐 | 【深入浅出MySQL】「底层原理」探秘缓冲池的核心奥秘,揭示终极洞察