【宋红康 MySQL数据库 】【高级篇】【02】MySQL字符集、比较规则、大小写规范、sql_mode

最新推荐文章于 2022-12-14 15:06:15 发布
最新推荐文章于 2022-12-14 15:06:15 发布
阅读量1k 收藏 12
点赞数
分类专栏： 数据库 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44018671/article/details/124969805
版权

持续学习&持续更新中…

学习态度：守破离

【宋红康 MySQL数据库 】【高级篇】【02】MySQL字符集、比较规则、大小写规范、sql_mode

默认字符集

  • MySQL8.0版本之前，默认字符集为latin1，utf8字符集指向的是utf8mb3。
  • 在数据库设计的时候往往需要将默认的字符集编码修改为utf8字符集，如果忘记了修改默认的字符集，就会出现乱码问题
  • 从MySQL8.0开始，数据库的默认编码为utf8mb4，从而避免了上述乱码问题。
MySQL8
mysql> show variables like '%character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)

mysql> create database dbtest01;
Query OK, 1 row affected (0.01 sec)

mysql> use dbtest01;
Database changed

mysql> create table emp01(id int, name varchar(15));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into emp01(id, name) values(10, 'Tom');
Query OK, 1 row affected (0.03 sec)

mysql> insert into emp01(id, name) values(12, '数据库');
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp01;
+------+-----------+
| id   | name      |
+------+-----------+
|   10 | Tom       |
|   12 | 数据库    |
+------+-----------+
2 rows in set (0.00 sec)

mysql> show create table emp01;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp01 | CREATE TABLE `emp01` (
  `id` int DEFAULT NULL,
  `name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database dbtest01;
+----------+------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                    |
+----------+------------------------------------------------------------------------------------------------------------------------------------+
| dbtest01 | CREATE DATABASE `dbtest01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL5

MySQL5默认的客户端和服务器都使用latin1，不支持中文。

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)

mysql> create database dbtest01;
Query OK, 1 row affected (0.00 sec)

mysql> use dbtest01;
Database changed

mysql> create table emp01(id int, name varchar(15));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into emp01(id, name) values(10, 'Tom');
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp01(id, name) values(12, '数据库');
ERROR 1366 (HY000): Incorrect string value: '\xE6\x95\xB0\xE6\x8D\xAE...' for column 'name' at row 1

mysql> show create table emp01;
+-------+------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------+
| emp01 | CREATE TABLE `emp01` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create database dbtest01;
+----------+---------------------------------------------------------------------+
| Database | Create Database                                                     |
+----------+---------------------------------------------------------------------+
| dbtest01 | CREATE DATABASE `dbtest01` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

修改字符集

修改MySQL5的默认字符集
vi /etc/my.cnf

在[mysqld]下添加：

character_set_server=utf8

重启MySQL服务：

systemctl restart mysqld

现在MySQL5的字符集就是：

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)

之后再创建新的数据库、新的表，在不指定字符集的情况下，就会使用默认的utf8编码

修改已有的数据库、表的字符集

虽然修改了MySQL5的默认字符集，但是，之前创建的已有的数据库、表的字符集是不会改变的。

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.01 sec)

mysql> use dbtest01;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show create database dbtest01;
+----------+---------------------------------------------------------------------+
| Database | Create Database                                                     |
+----------+---------------------------------------------------------------------+
| dbtest01 | CREATE DATABASE `dbtest01` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table emp01;
+-------+------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                 |
+-------+------------------------------------------------------------------------------------------------------------------------------+
| emp01 | CREATE TABLE `emp01` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

使用如下命令修改数据库字符集：

alter database 数据库名 character set 'utf8';

mysql> use dbtest01;
Database changed

mysql> alter database dbtest01 character set 'utf8';
Query OK, 1 row affected (0.01 sec)

mysql> show create database dbtest01;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| dbtest01 | CREATE DATABASE `dbtest01` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

修改表字符集：

alter table 表名 convert to character set 'utf8';

mysql> alter table emp01 convert to character set 'utf8';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table emp01;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| emp01 | CREATE TABLE `emp01` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into emp01(id, name) values(33, '数据库');
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp01;
+------+-----------+
| id   | name      |
+------+-----------+
|   10 | Tom       |
|   33 | 数据库    |
+------+-----------+
2 rows in set (0.00 sec)

字符集和比较规则

MySQL有4个级别的字符集和比较规则，分别是：

  • 服务器级别（MySQL server 级别）
  • 数据库级别
  • 表级别
  • 列级别

执行：show variables like 'character%';

mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8mb3                        |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.01 sec)

在这里插入图片描述

执行：show character set; 以查看MySQL支持的字符集：

mysql> show character set;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| binary   | Binary pseudo charset           | binary              |      1 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.00 sec)
utf8与utf8mb4：

在这里插入图片描述

比较规则

在这里插入图片描述

常用操作

在这里插入图片描述

在这里插入图片描述

utf8、utf8mb4一般使用以_general_ci、_unicode_ci为后缀的比较规则：

在这里插入图片描述

服务器级别

在这里插入图片描述

数据库级别

在这里插入图片描述

如果创建和修改数据库时没有指定字符集和比较规则，那么将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。

表级别

在这里插入图片描述

列级别

在这里插入图片描述

小结
  • 如果创建或修改数据库时没有显示的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则
  • 如果创建或修改表时没有显示的指定字符集和比较规则，则该表默认用所在数据库的字符集和比较规则
  • 如果创建或修改列（字段）时没有显示的指定字符集和比较规则，则该列默认用所在表的字符集和比较规则

请求到响应过程中字符集的变化

在这里插入图片描述

不同操作系统的默认值可能不同：

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

SQL大小写规范

默认规范

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述

Linux下大小写规则修改

在这里插入图片描述

SQL编写建议

在这里插入图片描述

sql_mode的合理设置

介绍

在这里插入图片描述

宽松模式、严格模式

在这里插入图片描述

在这里插入图片描述

查看sql_mode

在这里插入图片描述

举例：

mysql> select @@global.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
设置sql_mode

sql_mode可设置多个

在这里插入图片描述

举例：

设置sql_mode为宽松模式：

SET SESSION sql_mode = '';
sql_mode解释

在这里插入图片描述

参考

尚硅谷宋红康: MySQL数据库（入门到高级，菜鸟到大牛）.

本文完，感谢您的关注支持！

lpruoyu
关注
  • 0
    点赞
  • 踩
  • 12
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
mysql高级参考课件（供实操参考）.zip
01-18
mysql进阶资料
尚硅谷MySQL全套教程包含MySql核心技术篇和MySQL高级篇
08-10
MYSQL教程 MYSQL教程MYSQL教程 MYSQL教程尚硅谷MySQL全套教程包含MySql核心技术篇和MySQL高级篇。数据库视频教程，全套视频教程。
mysql markdon 笔记
04-20
mysql markdon , 详细
mysql数据库课件
07-15
mysql数据库入门课件，简单易懂，适合初学者，下吧
【宋红康 MySQL数据库 】【高级篇】【07】MySQL的存储引擎
Keep Reading Keep Writing Keep Thinking
06-21 1511
MySQL的存储引擎
2.1（Mysql）康师傅课件
weixin_46401545的博客
11-28 2332
大家好，感谢收看这篇文章。 这篇文章我本来是想通过尚硅谷的康师傅最新发布的Mysql初级进行参考的。 但是当我下载完宋老师的笔记之后，我就觉得我好像没有必要再继续写下去了。内容太细致了。绝了。nice 视频地址：【宋红康】MySQL数据库（入门到高级，菜鸟到大牛）_哔哩哔哩_bilibili 笔记地址： 链接：https://pan.baidu.com/s/1E6MZcZCSOI7FvmGgMV-3fg 提取码：6666 ...
【宋红康 MySQL数据库 】【高级篇】【12】性能分析工具的使用
a1k2l45k的博客
12-07 471
mysql数据库-性能分析工具
【宋红康 MySQL数据库 】【高级篇】【16】事务基础知识
a1k2l45k的博客
12-14 588
事务基础知识
【宋红康 MySQL数据库 】【高级篇】【10】索引的创建与删除_MySQL8.0的索引新特性
Keep Reading Keep Writing Keep Thinking
08-16 1034
索引的创建与删除_MySQL8.0的索引新特性
MySQL入门到高级-宋红康版.zip
最新发布
08-15
MySQL入门到高级-宋红康版 ----- 毕业设计，课程设计，项目源码均经过助教老师测试，运行无误，欢迎下载交流 ----- 下载后请首先打开README.md文件（如有），某些链接可能需要魔法打开。 ----- 毕业设计，课程设计，项目源码均经过助教老师测试，运行无误，欢迎下载交流 ----- 下载后请首先打开README.md文件（如有），某些链接可能需要魔法打开。
通俗易懂的MySQL高级教程（含配套资料）
06-14
本教程为授权出品 MySQL是目前流行的关系型数据库管理系统，在WEB应用方面 MySQL 也是目前好的 RDBMS 应用软件之一。随着淘宝去IOE(去除IBM小型机、Oracle数据库及EMC存储设备)化的推进，MySQL 数据库在当前的互联网应用中变得越来越重要，本教程主要讲授针对 Java 开发所需的 MySQL 高级知识，课程中会让大家快速掌握索引，如何避免索引失效，索引的优化策略，了解innodb和myisam存储引擎，熟悉MySQL锁机制，能熟练配置MySQL主从复制，熟练掌握explain、show profile、慢查询日志等日常SQL诊断和性能分析策略。
详解MySQL查询时区分字符串中字母大小写的方法
12-15
就是mysql默认的字段值不区分大小写？这点是比较令人头痛的事。直接使用客户端用sql查询数据库。 发现的确是大小不敏感 。 通过查询资料发现需要设置collate（校对） 。 collate规则： *_bin: 表示的是binary case...
mysql 查询时没有对字符串区分大小写处理方法
12-14
mysql 查询时没有对字符串区分大小写的原因主要在于创建表时没有指定其校对规则，或者校对规则设置成了不区分大小写。 区分大小写的设置通常是： utf8****_bin ，以 “_bin” 结尾的表示区分大小写,它表示的是：...
MySQL数据库管理高级篇(二)
06-14
MySQL所使用的SQL语言是用于访问数据库的最常用标准化语言。MySQL采用了双授权策略，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源代码这一特点。 本课程作为MySQL的基础课程，主要讲解...
mysql字符集和校对规则(Mysql校对集)
12-15
命名约定：以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二元）结束 校对规则一般分为两类： binary collation，二元法，直接比较字符的编码，可以认为是区分...
Mysql数据库名和表名在不同系统下的大小写敏感问题
12-15
这就意味着数据库和表名在 Windows 中是大小写不敏感的，而在大多数类型的 Unix 系统中是大小写敏感的。一个特例是 Mac OS X，当缺省的 HFS+ 文件系统使用时。然而 Mac OS X 还支持 UFS 卷，那些在 Mac OS X 是大小...
MySQL数据库大小写敏感的问题
09-09
今天小编就为大家分享一篇关于MySQL数据库大小写敏感的问题，小编觉得内容挺不错的，现在分享给大家，具有很好的参考价值，需要的朋友一起跟随小编来看看吧
MySQL选择数据库、修改数据库(修改数据库字符集、存储字符符格式)、删除数据库
12-14
MySQL选择数据库、修改数据库(修改数据库字符集、存储字符符格式)、删除数据库 选择数据库为什么要选择数据库？ 因为数据是存储到数据表，表存储在数据库下。如果要操作数据，那么必须要进入到对应的数据库才行。 ...
宋红康 mysql数据库
08-15
您好！对于MySQL数据库，我可以提供一些基本的信息。MySQL是一个开源的关系型数据库管理系统，广泛应用于各种网站和应用程序中。它具有高性能、稳定可靠以及丰富的功能。 MySQL支持多种操作系统，并提供了多种编程语言的接口，如Java、Python、PHP等。它使用标准的SQL语言进行数据管理和查询，可以处理大规模的数据集。 MySQL的特点包括： 1. 可扩展性：可以在单个服务器上运行多个数据库实例，支持主从复制和分布式数据库架构。 2. 安全性：提供了各种安全特性，如用户认证、数据加密和访问控制等。 3. 高性能：通过索引、查询优化和缓存等技术，提升数据库的查询和写入性能。 4. 数据完整性：支持事务处理和数据约束，保证数据的一致性和完整性。 5. 可靠性：提供了数据备份、恢复和故障转移等机制，确保数据的可靠性和可用性。 除了基本的数据库操作外，MySQL还支持存储过程、触发器、视图等高级功能，可以实现复杂的业务逻辑和数据处理需求。 希望这些信息对您有所帮助！如果您有更具体的问题，可以继续提问。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值