百度360必应搜狗淘宝本站头条
nodejsbufferorg.jsoupsetnocountoncv2.bitwise_nottime.strptime
当前位置：网站首页 > 编程字典 > 正文

第07期:有关 MySQL 字符集的 SQL 语句

toyiye 2024-06-28 10:05 16 浏览 0 评论



本篇为理清字符集的续篇（上一篇：第06期：梳理 MySQL 字符集的相关概念），重点讲述字符集涉及到的 sql 语句用法。


一、character introducer

翻译过来就是字符引导。也就是针对字符串，显式的给定一个字符编码和排序规则，不受系统参数的影响。

语法很简单：

[_charset_name] 'string' [COLLATE collation_name]

示例：

字符串"北京加油?!"

-- 字符集 utf8mb4，排序规则 utf8mb4_bin
select _utf8mb4 "北京加油?!" collate utf8mb4_bin as result;
+------------------+
| result           |
+------------------+
| 北京加油?!       |
+------------------+
1 row in set (0.00 sec)

-- 字符集 utf8mb4,collate 字句缺失，此时对应排序规则为utf8mb4_w0900_ai_ci
select _utf8mb4 "北京加油?!" as result;
+------------------+
| result           |
+------------------+
| 北京加油?!       |
+------------------+
1 row in set (0.00 sec)
-- 字符集缺失，此时字符集按照参数 @@character_set_connection 值来指定。
mysql> select "北京加油?!" collate gb18030_chinese_ci as result;
ERROR 1253 (42000): COLLATION 'gb18030_chinese_ci' is not valid for CHARACTER SET 'utf8mb4'

-- 查看变量 @@character_set_connection，确认其字符集不包含排序规则 gb18030_chinese_ci，所以以上语句报错。
mysql> select @@character_set_connection;
+----------------------------+
| @@character_set_connection |
+----------------------------+
| utf8mb4                    |
+----------------------------+
1 row in set (0.00 sec)
-- 那给下正确的排序规则 utf8mb4_bin，执行正确。
mysql> select "北京加油?!" collate utf8mb4_bin as result;
+------------------+
| result           |
+------------------+
| 北京加油?!       |
+------------------+
1 row in set (0.00 sec)

-- 字符集和排序规则都不指定，此时字符串对应的字符集和排序规则和参数 @@character_set_connection 一致。
select "北京加油?!" as result;
-- 那这条语句其实被 MySQL 解释为
select _utf8mb4 "北京加油?!" collate utf8mb4_0900_ai_ci as result;

总结 Introducer 使用规则：



二、字符集转换函数

1. convert 函数

convert 函数类似于 introducer，不过只能指定字符集。

举个例子，通过 convert 函数转换字符串"北京加油?!"的编码为 utf8mb4。不过前提是转换前后字符集一定要兼容。

-- 正确的转换
mysql> select convert("北京加油?!" using utf8mb4) ;
+-------------------------------------------+
| convert("北京加油?!" using utf8mb4)       |
+-------------------------------------------+
| 北京加油?!                                |
+-------------------------------------------+
1 row in set (0.00 sec)

-- 错误的转换，字符集编码不兼容。

mysql> select convert("北京加油?!" using latin1) ;
+------------------------------------------+
| convert("北京加油?!" using latin1)       |
+------------------------------------------+
| ?????!                                   |
+------------------------------------------+
1 row in set (0.00 sec)

2. charset 函数

检测字符串的字符集。可以检测出当前字符串在当前 session 的字符集。

mysql> set @a="北京加油?!";
Query OK, 0 rows affected (0.00 sec)

mysql> select charset(@a);
+-------------+
| charset(@a) |
+-------------+
| utf8        |
+-------------+
1 row in set (0.00 sec)

3. set names 语句

语法为：

SET NAMES {'charset_name'
[COLLATE 'collation_name'] | DEFAULT}

这条语句最常用，可是也最容易被滥用，比如语句：

set names latin1 collate latin1_bin;

执行后会默认执行一系列语句，也就是把非服务端的相关参数给重新设定了。

set session character_set_results = latin1;

set session character_set_client = latin1;

set session character_set_connection=latin1;

set session collation_connection = latin1_bin;

那现在重新执行确认一下，跟 introducer 一样，没有指定 collate 语句，默认为字符集对应的排序规则。

mysql> set names latin1 ;
Query OK, 0 rows affected (0.00 sec)

-- 那这里看到相关参数值全部被改了。

mysql> select * from performance_schema.session_variables where variable_name in ('character_set_connection','collation_connection','character_set_results','character_set_client');
+--------------------------+-------------------+
| VARIABLE_NAME            | VARIABLE_VALUE    |
+--------------------------+-------------------+
| character_set_client     | latin1            |
| character_set_connection | latin1            |
| character_set_results    | latin1            |
| collation_connection     | latin1_swedish_ci |
+----------------------------------------------+

那如果想改回默认值，简单执行：

mysql> set names default;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from performance_schema.session_variables where variable_name in ('character_set_connection','collation_connection','character_set_results','character_set_client');
+--------------------------+--------------------+
| VARIABLE_NAME            | VARIABLE_VALUE     |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_results    | utf8mb4            |
| collation_connection     | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
4 rows in set (0.00 sec)

不过有一点要注意的是，并不是所有字符集都适用于这条语句，比如定长字符集 utf32，设置就会报错。因为变量 @@character_set_client 不支持这个字符集。

mysql> set names utf32;
ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'utf32'

4. set character set 语句

语法为：

SET {CHARACTER SET | CHARSET}
{'charset_name' | DEFAULT}

类似语句 set names，同样是设置以下三个 session 参数：

  • character_set_results
  • character_set_client
  • character_set_connection

同样是可以恢复默认值，还有同样的限制规则等。

不过有两点不同：

1）参数 character_set_connection 的值不会被设定为指定的字符集，而是继承参数 character_set_database 所设定的字符集。

示例：

mysql> set character set latin1;
Query OK, 0 rows affected (0.01 sec)

-- 检索结果显示，参数 character_set_connection 的值和 character_set_database 的值一致。
mysql> select * from performance_schema.session_variables where variable_name in ('character_set_connection','collation_connection','character_set_database','character_set_results','character_set_client');
+--------------------------+--------------------+
| VARIABLE_NAME            | VARIABLE_VALUE     |
+--------------------------+--------------------+
| character_set_client     | latin1             |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_results    | latin1             |
| collation_connection     | utf8mb4_0900_ai_ci |
+--------------------------+--------------------+
5 rows in set (0.00 sec)

2）只用来设置字符集，不能定义具体的排序规则名称，也就是排序规则名称都是字符集对应的默认排序规则名称。这点从以上例子就可以看出来。

5. collate 子句

collate 语句强制指定排序规则，优先级最高。也就是显式指定 collate 会覆盖已有的排序规则。

这里涉及到单个字符串以及字符串拼接的排序规则问题。

显式的指定排序方式

-- 示例表 c1,
mysql> create table c1 (n char(1));
Query OK, 0 rows affected (0.06 sec)

-- 插入示例数据，英文大小写字母乱序插入
mysql> insert into c1 with recursive a(x,y) as
(
   select 65,97 union all select x+1,y+1 from a where x<90
)
select char(x using ascii) x from a
union all
select char(y using ascii) y from a order by rand();

Query OK, 52 rows affected (0.02 sec)
Records: 52  Duplicates: 0  Warnings: 0

改变 order by 的排序规则。

-- 原有排序结果
mysql> select n from c1 order by n  desc limit 6;
+------+
| n    |
+------+
| Z    |
| z    |
| y    |
| Y    |
| x    |
| X    |
+------+
6 rows in set (0.00 sec)

-- collate 显式指定后，排序结果。
mysql> select n from c1 order by n  collate utf8mb4_0900_bin desc limit 6;
+------+
| n    |
+------+
| z    |
| y    |
| x    |
| w    |
| v    |
| u    |
+------+
6 rows in set (0.00 sec)

用于具体的列别名

mysql> select n collate utf8mb4_bin as n from c1 order by n  desc limit 6;
+------+
| n    |
+------+
| z    |
| y    |
| x    |
| w    |
| v    |
| u    |
+------+
6 rows in set (0.01 sec)

用于聚合函数

mysql> select max(n) n from c1;
+------+
| n    |
+------+
| Z    |
+------+
1 row in set (0.00 sec)

-- 强制collate结果
mysql> select max(n collate utf8mb4_bin) n from c1;
+------+
| n    |
+------+
| z    |
+------+
1 row in set (0.00 sec)

或者用于 where，group by，having 等等。

用于统计排序方式的强制性指标

比如要在 where 条件里过滤字符串，where a = 'a'，那此时是用 a 的排序规则，还是字符 'a' 的排序规则，这里就涉及到一个排序规则的强制性指标。

MySQL 的排序规则强制性指标值 从 0 到 6 一共 7 个。数字指标越小，优先级越高。以下为指标值说明：

同时，为了便于大家理解，MySQL 提供了如何检测这个指标的函数 coercibility，举几个例子看下：

示例：

SET NAMES {'charset_name'[COLLATE 'collation_name'] | DEFAULT}


总结

对于字符集的控制相关 SQL 就介绍的差不多了。主要举例介绍了 MySQL 字符集相关的处理 SQL 语句，比如设置客户端相关编码语句：SET NAMES/SET CHARSET；设置排序规则语句：COLLATE；给 MySQL 信号的 introducer 等。希望对大家有帮助。

关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！


相关推荐

为何越来越多的编程语言使用JSON（为什么编程）

JSON是JavascriptObjectNotation的缩写，意思是Javascript对象表示法，是一种易于人类阅读和对编程友好的文本数据传递方法，是JavaScript语言规范定义的一个子...

何时在数据库中使用 JSON（数据库用json格式存储）

在本文中，您将了解何时应考虑将JSON数据类型添加到表中以及何时应避免使用它们。每天?分享?最新?软件?开发?，Devops，敏捷?，测试?以及?项目?管理?最新?，最热门?的?文章?，每天?花?...

MySQL 从零开始:05 数据类型（mysql数据类型有哪些,并举例）

前面的讲解中已经接触到了表的创建，表的创建是对字段的声明，比如：上述语句声明了字段的名称、类型、所占空间、默认值和是否可以为空等信息。其中的int、varchar、char和decimal都...

JSON对象花样进阶（json格式对象）

一、引言在现代Web开发中，JSON（JavaScriptObjectNotation）已经成为数据交换的标准格式。无论是从前端向后端发送数据，还是从后端接收数据，JSON都是不可或缺的一部分。...

深入理解 JSON 和 Form-data（json和formdata提交区别）

在讨论现代网络开发与API设计的语境下，理解客户端和服务器间如何有效且可靠地交换数据变得尤为关键。这里，特别值得关注的是两种主流数据格式：...

JSON 语法（json 语法 priority）

JSON语法是JavaScript语法的子集。JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔花括号保存对象方括号保存数组JS...

JSON语法详解（json的语法规则）

JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔大括号保存对象中括号保存数组注意：json的key是字符串,且必须是双引号，不能是单引号...

MySQL JSON数据类型操作（mysql的json）

概述mysql自5.7.8版本开始，就支持了json结构的数据存储和查询，这表明了mysql也在不断的学习和增加nosql数据库的有点。但mysql毕竟是关系型数据库，在处理json这种非结构化的数据...

JSON的数据模式（json数据格式示例）

像XML模式一样，JSON数据格式也有Schema，这是一个基于JSON格式的规范。JSON模式也以JSON格式编写。它用于验证JSON数据。JSON模式示例以下代码显示了基本的JSON模式。{"...

前端学习——JSON格式详解（后端json格式）

JSON(JavaScriptObjectNotation)是一种轻量级的数据交换格式。易于人阅读和编写。同时也易于机器解析和生成。它基于JavaScriptProgrammingLa...

什么是 JSON:详解 JSON 及其优势（什么叫json）

现在程序员还有谁不知道JSON吗？无论对于前端还是后端，JSON都是一种常见的数据格式。那么JSON到底是什么呢？JSON的定义...

PostgreSQL JSON 类型:处理结构化数据

PostgreSQL提供JSON类型，以存储结构化数据。JSON是一种开放的数据格式，可用于存储各种类型的值。什么是JSON类型？JSON类型表示JSON(JavaScriptO...

JavaScript:JSON、三种包装类（javascript 包）

JOSN:我们希望可以将一个对象在不同的语言中进行传递，以达到通信的目的，最佳方式就是将一个对象转换为字符串的形式JSON（JavaScriptObjectNotation）-JS的对象表示法...

Python数据分析 只要1分钟 教你玩转JSON 全程干货

Json简介：Json，全名JavaScriptObjectNotation,JSON(JavaScriptObjectNotation(记号、标记))是一种轻量级的数据交换格式。它基于J...

比较一下JSON与XML两种数据格式?（json和xml哪个好）

JSON（JavaScriptObjectNotation）和XML（eXtensibleMarkupLanguage）是在日常开发中比较常用的两种数据格式，它们主要的作用就是用来进行数据的传...

取消回复欢迎 你 发表评论:

请填写验证码
一周热门
最近发表
标签列表