存储之道：MySQL 中的字符串数据类型之 CHAR 与 VARCHAR

文章目录

• 参考
• 描述
• CHAR 与 VARCHAR
• • • 常见的字符串数据类型
    • 权衡
• CHAR 中尾随的空白字符
• • • 固定字符长度
    • PAD_CHAR_TO_FULL_LENGTH
    • 开启 PAD_CHAR_TO_FULL_LENGTH 模式
    • • • 配置文件
        • • Windows 下 MySQL 常见版本的配置文件所在的路径
          • Linux 下 MySQL 常见版本的配置文件所在的路径
        • SET 关键字
    • 尾随空白字符的显现
• CHAR 字段值的检索
• • • 尾部空格的自动删除
    • 如何避免尾部空格的自动删除？
• 输入字符超出最大字符长度
• • • 默认处理方式
    • 严格模式
• 比较
• • • VARCHAR 的额外数据
    • VARCHAE 的数据存储方式

参考

项目	描述
Mysql 官方文档	The CHAR and VARCHAR Types
mysql 官方文档	Server SQL Modes
搜索引擎	Google 、Bing

描述

项目	描述
DataGrip	2023.1.1
Mysql	8.0.33 MySQL CommUnity Server - GPL

CHAR 与 VARCHAR

常见的字符串数据类型

当涉及到存储字符串数据时，MySQL 提供了两种常见的数据类型：CHAR 和 VARCHAR。

1. CHAR
CHAR 是用于存储固定长度的字符串的数据类型。它需要指定字符串的最大字符长度（可以使用 CHAR(M) 来指定最大字符长度，其中的 M 即为最大字符长度），取值范围为 0 ~ 255（当最大字符长度为 0 时，MySQL 允许你向该字段中插入值，但该操作并不会成功）。如果存储的字符串长度小于指定长度，MySQL 会在字符串末尾使用 空格 字符进行填充，以使其达到指定长度。这意味着 CHAR 类型的字段 始终占用指定长度的存储空间，无论实际存储的字符串长度是多少。

2. VARCHAR
VARCHAR 是用于存储可变长度的字符串的数据类型。它也需要指定字符串的最大长度，最大可为 65,535 个字符（可以使用 VARCHAR(M) 来指定最大字符长度，其中的 M 即为最大字符长度）。与 CHAR 不同，VARCHAR 只会 占用实际存储的字符长度（单个字符所占据的存储空间由其使用的字符集决定）加上一定的额外字节（一或两个字节），而不会固定占用指定长度的存储空间。

数据类型	最大长度	描述
CHAR	255 字符	存储 固定长度 的 字符串，末尾会使用 空格 进行填充以满足指定长度要求
VARCHAR	65,535 字符	存储 可变长度 的 字符串，只占用实际存储的字符长度（单个字符所占据的存储空间由其使用的字符集决定）以及一或两字节的额外存储空间

权衡

选择使用 CHAR 还是 VARCHAR 取决于具体的需求。如果存储的字符串长度是固定的或接近固定的，且占用存储空间的一致性很重要，可以选择 CHAR。如果存储的字符串长度变化较大或不确定，并且希望节省存储空间，可以选择 VARCHAR。

CHAR 中尾随的空白字符

固定字符长度

对于 CHAR 数据类型，在存放的数据未达到指定的大小时，MySQL 会在字符串的末尾填充 空格，使其达到固定的字符长度。

需要注意的是，当从数据库中检索数据时，MySQL 会 自动去除 CHAR 类型字段值 末尾的空格，这样可以使得获取到的字符串长度与实际存储的数据长度一致。

举个栗子

# 创建一个数据库用于测试CREATE DATABASE db_test;# 使用 db_test 数据库USE db_test;# 创建一张表CREATE TABLE tb(    a VARCHAR(10),    b CHAR(10),    c CHAR(10));# 向表中插入数据INSERT INTO tbVALUES ('a', 'b', 'c');# 查询表中的数据SELECT concat('(', a, ')'), concat('(', b, ')'), concat('(', c, ')')FROM tb;

查询语句的输出结果

VARCHAR 类型的字段值在不满最大长度限制时并不会在实际字段值的尾部添加空格。CHAR 字段在实际字段值未达到指定的字符个数（10）时将在尾部添加适当的空格并将其存储与 MySQL 中。在检索时，CHAR 字段尾部的空白字符将被剔除。因此，无论是 VARCHAR 字段，还是 CHAR 字段，其保存的值都紧邻两侧的括号。

+---------------------+---------------------+---------------------+| concat('(', a, ')') | concat('(', b, ')') | concat('(', c, ')') |+---------------------+---------------------+---------------------+| (a)                 | (b)                 | (c)                 |+---------------------+---------------------+---------------------+1 row in set (0.00 sec)

PAD_CHAR_TO_FULL_LENGTH

PAD_CHAR_TO_FULL_LENGTH 是 MySQL 的一个 SQL mode，用于控制在检索 CHAR 类型列时是否保留尾随空格。

当 PAD_CHAR_TO_FULL_LENGTH 模式启用时，MySQL 在检索 CHAR 类型的列值时不会去除末尾的空格。这意味着，无论存储的数据是否占满了列的长度，检索到的 CHAR 值都将以完整长度形式呈现。

注：

从 MySQL 8.0.13 开始， PAD_CHAR_TO_FULL_LENGTH 已弃用。预计它会在 MySQL 的未来版本中被 删除。

开启 PAD_CHAR_TO_FULL_LENGTH 模式

配置文件

1. 打开 MySQL 配置文件。根据你的操作系统和 MySQL 版本，配置文件的位置可能有所不同。在大多数情况下，MySQL 的配置文件名为 my.cnf（类 Unix 系统） 或 my.ini（windows 系统）。

2. 在配置文件中找到 [mysqld] 部分。如果该部分不存在，则可以自行添加。

3. 若 sql-mode 配置选项不存在于 [mysqld] 部分，则在该部分内容中添加如下语句

   sql-mode="PAD_CHAR_TO_FULL_LENGTH"

   若 sql-mode 配置选项已存在，则在该选项中添加值 PAD_CHAR_TO_FULL_LENGTH 。在添加过程中注意将添加值与原有值用英式逗号进行分隔。

4. 保存并关闭配置文件。

5. 重启 MySQL 服务，以使配置更改生效。

在重新启动后，MySQL 将启用 PAD_CHAR_TO_FULL_LENGTH 模式，对于 CHAR 类型的列，在检索时将保留尾随空格并填充到完整的长度。

Windows 下 MySQL 常见版本的配置文件所在的路径

在 Windows 下，各版本 MySQL 的配置文件所处的路径可能会有所不同。以下是一些常见的 MySQL 版本和对应的配置文件路径：

1. MySQL 5.6 及更早版本：

   • 安装目录下的 my.ini 文件。

2. MySQL 5.7：

   • 安装目录下的 my.ini 文件。
   • 或者在 %PROGRAMDATA%\MySQL\MySQL Server 5.7\ 目录下的 my.ini 文件。

3. MySQL 8.0：

   • 安装目录下的 my.ini 文件。
   • 或者在 %ProgramData%\MySQL\MySQL Server 8.0\ 目录下的 my.ini 文件。

注：

%PROGRAMDATA% 是一个 Windows 环境变量，表示系统数据文件夹路径，一般为 C:\ProgramData。而 %ProgramData%\MySQL\MySQL Server X.X\ 中的 X.X 则表示具体的 MySQL 版本号。

如果无法找到特定版本的配置文件，你可以尝试搜索计算机上的 my.ini 文件来确定其位置。

linux 下 MySQL 常见版本的配置文件所在的路径

在 Linux 下，各版本 MySQL 的配置文件所处的路径可能会有所不同。以下是一些常见的 MySQL 版本和对应的配置文件路径：

1. MySQL 5.7 及更早版本：

   • /etc/my.cnf 或 /etc/mysql/my.cnf 文件。

2. MySQL 8.0：

   • /etc/my.cnf 或 /etc/mysql/my.cnf 文件。
   • /etc/mysql/mysql.conf.d/ 目录下的以 .cnf 结尾的文件中。

请注意，具体的配置文件路径可能因 Linux 发行版和安装方式而有所差异。以上路径仅为一般情况下的默认配置文件位置。

如果你无法找到特定版本的配置文件，可以尝试使用以下命令来确定其位置：

sudo find / -name my.cnf

该命令会在整个文件系统中搜索 my.cnf 文件，并显示其路径。

SET 关键字

在 MySQL 中，SET 是一个关键字，用于设置和修改 会话级别 的系统变量的值。通过 SET 关键字，你可以灵活地调整 MySQL 的行为和配置，以满足特定需求和优化性能。

你可以使用如下语句在当前会话下设置 sql-mode 系统变量的值：

set sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';

注：

1. 系统变量 是 MySQL 中控制服务器行为和配置的参数。

2. 使用 SET 关键字设置的系统变量值仅在 当前会话中有效，对其他会话不产生影响。如果你希望修改 全局级别 的系统变量，可以使用 SET GLOBAL 语法或直接修改配置文件并重启 MySQL 服务器。
   会话级别的设置意味着 对于每个连接到 MySQL 服务器的客户端，可以独立地设置和修改系统变量的值，而不会影响其他客户端的设置。

3. 使用 SET 关键字对系统变量的修改是临时的。如果需要永久修改系统变量的值，需要修改 MySQL 的配置文件（如 my.cnf 或 my.ini）并重启 MySQL 服务。

尾随空白字符的显现

在开启了 PAD_CHAR_TO_FULL_LENGTH 模式后，MySQL 在处理 CHAR 类型的字段时，不会删除尾部的空格字符。对此，请参考如下示例：

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb(    a VARCHAR(10),    b CHAR(10),    c CHAR(10));INSERT INTO tbVALUES ('a', 'b', 'Hello');# 通过 SET 关键字开启 PAD_CHAR_TO_FULL_LENGTHSET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';SELECT concat('(', a, ')'), concat('(', b, ')'), concat('(', c, ')')FROM tb;

查询语句的输出结果

开启 PAD_CHAR_TO_FULL_LENGTH 模式后，CHAR 类型的字段将始终保持固定长度。在检索 CHAR 类型的字段时并不会自动删除字段值尾随的空格。

+---------------------+---------------------+---------------------+| concat('(', a, ')') | concat('(', b, ')') | concat('(', c, ')') |+---------------------+---------------------+---------------------+| (a)                 | (b         )        | (Hello     )        |+---------------------+---------------------+---------------------+1 row in set (0.00 sec)

CHAR 字段值的检索

尾部空格的自动删除

在前文我们讲到，MySQL 在检索 CHAR 字段中的值时将剔除其尾部的空格。那么，MySQL 会将我们人为添加而不是其自动添加的空格去除吗？对此，我们将进行如下验证：

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb(    a VARCHAR(10),    b CHAR(10),    c CHAR(10));INSERT INTO tbVALUES ('a   ', 'b   ', 'c   ');SELECT concat('(', a, ')'), concat('(', b, ')'), concat('(', c, ')')FROM tb;

查询语句的输出结果

由输出结果可知，CHAR 类型字段的尾部空格均被删除了。由此，我们可以得出如下结论：

MySQL 检索 CHAR 类型字段时，默认将删除该字段尾部的所有空格。无论这些空格是否是人为添加的。

+---------------------+---------------------+---------------------+| concat('(', a, ')') | concat('(', b, ')') | concat('(', c, ')') |+---------------------+---------------------+---------------------+| (  a  )             | (  b)               | (  c)               |+---------------------+---------------------+---------------------+1 row in set (0.00 sec)

如何避免尾部空格的自动删除？

如果你希望保留 CHAR 字段值中的空格，你可以考虑以下两种方法：

1. 使用 VARCHAR 字段
   VARCHAR 字段是一种可变长度的字符类型，不会自动剔除尾部的空格。相比于CHAR字段，VARCHAR字 段更适合存储长度可变的字符串。

2. 使用特殊字符来表示空格
   如果你必须继续使用 CHAR 字段，并且需要保留空格，可以考虑使用特殊字符来表示空格，例如 Unicode 中的 全角空格（U+3000） 或其他不常用的空格字符。通过使用这些特殊字符来表示空格，可以避免空格被自动剔除。在检索数据时，你可以将特殊字符转换回空格，或者根据需要进行处理。

举个栗子

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb(    a VARCHAR(5),    b CHAR(5),    c CHAR(5));# 使用全角空格代替常用的半角空格以# 避免尾部空格在检索时被自动剔除。INSERT INTO tbVALUES ('　　a　　', '　　b　　', '　　c　　');SELECT concat('(', a, ')'), concat('(', b, ')'), concat('(', c, ')')FROM tb;

查询语句输出结果

在 CHAR 字段值的尾部使用 全角空格 后，后续若对此字段进行检索，尾部添加的全角空格将不会被 MySQL 自动剔除。

+---------------------+---------------------+---------------------+| concat('(', a, ')') | concat('(', b, ')') | concat('(', c, ')') |+---------------------+---------------------+---------------------+| (　　a　　)         | (　　b　　)         | (　　c　　)         |+---------------------+---------------------+---------------------+1 row in set (0.00 sec)

输入字符超出最大字符长度

当插入或更新的字符串长度超过了 VARCHAR 或 CHAR 字段的最大字符长度限制时，MySQL 会根据情况采取不同的处理方式。

默认处理方式

默认情况下，MySQL 会截断超过最大长度的部分，并将截断后的字符串存储在该字段中，而不会引发错误或警告。这意味着超过最大长度的部分会被丢弃，只存储最大长度内的部分。对此，请参考如下示例：

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb(    a VARCHAR(5),    b CHAR(5),    c CHAR(5));INSERT INTO tbVALUES ('Hello World', 'Hello China', 'Hello');SELECT a, b, cFROM tb;

查询语句的输出结果

+-------+-------+-------+| a     | b     | c     |+-------+-------+-------+| Hello | Hello | Hello |+-------+-------+-------+1 row in set (0.00 sec)

严格模式

如果 sql_mode 参数设置为严格模式，当插入或更新的字符串长度超过 CHAR 字段的最大字符长度时，MySQL 会引发错误，并拒绝插入或更新操作。

举个栗子

在 MySQL 中，开启严格模式的方式与开启 PAD_CHAR_TO_FULL_LENGTH 的方法类似，仅需在 MySQL 系统变量 sql-mode 中添加 STRICT_ALL_TABLES 即可。我们将在本示例中通过 SET 关键字在当前会话中临时修改 sql-mode 系统变量以开启严格模式。

CREATE DATABASE db_test;USE db_test;CREATE TABLE tb(    a VARCHAR(5),    b CHAR(5),    c CHAR(5));# 通过 SET 关键字在当前会话中临时# 修改 sql-mode 系统变量。SET sql_mode = 'STRICT_ALL_TABLES';INSERT INTO tbVALUES ('Hello World', 'Hello China', 'Hello');SELECT a, b, cFROM tb;

查询语句的输出结果

MySQL 执行到 INSERT INTO 语句时抛出异常错误信息 [22001][1406] Data truncation: Data too long for column 'a' at row 1。随后执行查询语句得到输出 Empty set (0.00 sec)。

比较

下表展示了使用 CHAR(4) 和 VARCHAR(4) 存储不同字符串值的结果，假设该列使用 单字节字符集（如 latin1）。

值	CHAR(4)	存储所需空间	VARCHAR(4)	存储所需空间
''	' '	4字节	''	1字节
'ab'	'ab '	4字节	'ab'	3字节
'abcd'	'abcd'	4字节	'abcd'	5字节
'abcdefgh'	'abcd'	4字节	'abcd'	5字节

上表中的最后一行所示的存储值仅适用于 未启用严格 SQL 模式的情况；如果启用了严格模式，则超过列长度的值不会被存储，会导致错误发生。

这个表格比较了 CHAR 和 VARCHAR 在存储和占用空间方面的不同。在使用CHAR 列时，数据会被右侧填充空格以达到指定的长度，而在使用VARCHAR 列时，数据会根据实际长度存储，不会填充额外的空格但需要额外的存储空间用于存放一些必要的数据。

VARCHAR 的额外数据

VARCHAR 类型需要存储 字符串的长度信息。这是因为 VARCHAR 类型是一种可变长度的字符类型，其长度可以根据存储的实际值而变化。为了确保能够正确地检索和操作数据，MySQL 需要在存储时记录字符串的长度信息。

VARCHAE 的数据存储方式

在 MySQL中，VARCHAR 字段需要存储的内容由长度前缀以及数据内容。

1. 长度前缀
   VARCHAR 类型存储时使用 一个或两个字节的长度前缀来表示字符串的长度。这个长度前缀指示了 数据内容中的字符数。如果数据内容的长度不超过 255 个字符，则使用 一 个字节来存储长度信息；如果值的长度可能超过 255 个字节，则使用 两 个字节来存储长度信息。

2. 数据内容
   长度前缀之后，紧跟着实际的字符串数据内容。

来源地址：https://blog.csdn.net/qq_44879989/article/details/130772295