🪴 Obsidian Publish

MySQL 字段类型

13分钟阅读

补充 JavaGuide 没有的：

  • utf8mb4 下，英文占用 1 个字节，一般汉字占 3 个字节，emoji 表情占 4 个字节。
  • UTF-8：一个汉字＝ 3 个字节；GBK：一个汉字＝ 2 个字节
  • Int(20) 或 BigInt(20) 后面括号中只是显示的长度，并不是能存放的长度。
  • 字符长度函数问题
    • length: 一个汉字是算三个字符 (utf-8)，一个数字或字母算一个字符。
    • char_length: 不管汉字还是数字或者是字母都算是一个字符。
  • MySql 中 UTF8 和 GBK 编码中文字符长度问题_risingsun001 的博客-CSDN 博客_mysql 中文字符长度
  • MySQL 使用 varchar 的时候，字符串是不区分大小写的，需要在创建表时对字段进行设置。比如：WORD VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL

MySQL 字段类型可以简单分为三大类：

  • 数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
  • 字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
  • 日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

下面这张图不是我画的，忘记是从哪里保存下来的了，总结的还蛮不错的。

MySQL 常见字段类型总结

MySQL 字段类型比较多，我这里会挑选一些日常开发使用很频繁且面试常问的字段类型，以面试问题的形式来详细介绍。如无特殊说明，针对的都是 InnoDB 存储引擎。

另外，推荐阅读一下《高性能 MySQL(第三版)》的第四章，有详细介绍 MySQL 字段类型优化。

整数类型的 UNSIGNED 属性有什么用？

MySQL 中的整数类型可以使用可选的 UNSIGNED 属性来表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限提高一倍，因为它不需要存储负数值。

例如， TINYINT UNSIGNED 类型的取值范围是 0 ~ 255，而普通的 TINYINT 类型的值范围是 -128 ~ 127。INT UNSIGNED 类型的取值范围是 0 ~ 4,294,967,295，而普通的 INT 类型的值范围是 -2,147,483,648 ~ 2,147,483,647。

对于从 0 开始递增的 ID 列，使用 UNSIGNED 属性可以非常适合，因为不允许负值并且可以拥有更大的上限范围，提供了更多的 ID 值可用。

CHAR 和 VARCHAR 的区别是什么？

CHAR 和 VARCHAR 是最常用到的字符串类型，两者的主要区别在于：CHAR 是定长字符串，VARCHAR 是变长字符串。

CHAR（0-255 字节）和 VARCHAR（0-65,535 字节）

CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；VARCHAR 在存储时需要使用 1 或 2 个额外字节记录字符串的长度，检索时不需要处理。

CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。

CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。

VARCHAR(100)和 VARCHAR(10)的区别是什么？

VARCHAR(100)和 VARCHAR(10)都是变长类型，表示能存储最多 100 个字符和 10 个字符。因此，VARCHAR (100) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR(10)存储超过 10 个字符时，就需要修改表结构才可以。

虽说 VARCHAR(100)和 VARCHAR(10)能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的，这也是很多人容易误解的一点。

不过，VARCHAR(100) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(100)是按照 100 这个长度来进行的，也就会消耗更多内存。

DECIMAL 和 FLOAT/DOUBLE 的区别是什么？

DECIMAL 和 FLOAT 的区别是：DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

DECIMAL 用于存储具有精度要求的小数，例如与货币相关的数据，可以避免浮点数带来的精度损失。

在 Java 中，MySQL 的 DECIMAL 类型对应的是 Java 类 java.math.BigDecimal。

为什么不推荐使用 TEXT 和 BLOB？

TEXT 类型类似于 CHAR（0-255 字节）和 VARCHAR（0-65,535 字节），但可以存储更长的字符串，即长文本数据，例如博客内容。

类型可存储大小用途
TINYTEXT0-255 字节一般文本字符串
TEXT0-65,535 字节长文本字符串
MEDIUMTEXT0-16,772,150 字节较大文本数据
LONGTEXT0-4,294,967,295 字节极大文本数据

BLOB 类型主要用于存储二进制大对象，例如图片、音视频等文件。

类型可存储大小用途
TINYBLOB0-255 字节短文本二进制字符串
BLOB0-65KB二进制字符串
MEDIUMBLOB0-16MB二进制形式的长文本数据
LONGBLOB0-4GB二进制形式的极大文本数据

在日常开发中，很少使用 TEXT 类型，但偶尔会用到，而 BLOB 类型则基本不常用。如果预期长度范围可以通过 VARCHAR 来满足，建议避免使用 TEXT。

数据库规范通常不推荐使用 BLOB 和 TEXT 类型，这两种类型具有一些缺点和限制，例如：

  • 不能有默认值。
  • 在使用临时表时无法使用内存临时表，只能在磁盘上创建临时表（《高性能 MySQL》书中有提到）。
  • 检索效率较低。
  • 不能直接创建索引，需要指定前缀长度。
  • 可能会消耗大量的网络和 IO 带宽。
  • 可能导致表上的 DML 操作变慢。
  • ……
  • 查询性能问题
    • 全表扫描：TEXT 和 BLOB 类型存储的数据通常比较大，数据库在处理包含这些类型字段的查询时，可能需要进行全表扫描，这会导致查询性能下降。特别是在数据量较大的情况下，查询效率会显著降低。
    • 索引效果有限：虽然可以对 TEXT 和 BLOB 字段创建索引，但由于这些类型的数据长度不确定且可能非常大，索引的效果往往不如对固定长度字段创建的索引。在进行范围查询、排序等操作时，性能可能会受到影响。
  • 存储和内存占用
    • 空间占用：TEXT 和 BLOB 类型会占用大量的存储空间，尤其是当存储的内容较长或数据量较大时。这不仅会增加数据库的存储成本，还可能导致磁盘空间不足的问题。
    • 内存消耗：在查询或处理 TEXT 和 BLOB 数据时，数据库需要将数据加载到内存中进行操作。由于这些数据可能很大，会占用较多的内存空间，可能导致内存不足，影响数据库的整体性能，甚至导致系统崩溃。
  • 数据一致性和维护
    • 事务处理：在事务中处理 TEXT 和 BLOB 数据时，由于数据量较大，可能会导致事务日志变得非常大，增加了事务处理的复杂性和恢复时间。如果事务出现故障，回滚操作也会比较耗时。
    • 数据更新和删除：对 TEXT 和 BLOB 字段进行更新或删除操作时，可能会导致数据库的碎片增加，影响数据库的性能和存储空间利用率。定期进行碎片整理可以解决这个问题，但这也增加了数据库维护的工作量。
  • 兼容性和可移植性
    • 不同数据库差异：不同的数据库管理系统对 TEXT 和 BLOB 类型的支持和实现方式可能存在差异。例如，在某些数据库中，TEXT 类型的最大长度限制可能与其他数据库不同。这可能导致在将数据库从一个系统迁移到另一个系统时出现兼容性问题。
    • 应用程序适配：在应用程序中处理 TEXT 和 BLOB 数据时，需要使用特定的方法和接口。不同的编程语言和数据库驱动程序对这些类型的支持也可能有所不同，这增加了应用程序开发和维护的复杂性，降低了代码的可移植性。

DATETIME 和 TIMESTAMP 的区别是什么？

DATETIME 类型没有时区信息，TIMESTAMP 和时区有关。

TIMESTAMP 只需要使用 4 个字节的存储空间，但是 DATETIME 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小。

  • DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
  • Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

关于两者的详细对比， MySQL 时间的存储方法。

NULL 和 ” 的区别是什么？

NULL 跟 ''(空字符串)是两个完全不一样的值，区别如下：

  • NULL 代表一个不确定的值,就算是两个 NULL,它俩也不一定相等。例如，SELECT NULL=NULL的结果为 false，但是在我们使用DISTINCT,GROUP BY,ORDER BY时,NULL又被认为是相等的。
  • ''的长度是 0，是不占用空间的，而NULL 是需要占用空间的。
  • NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。
  • 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。

看了上面的介绍之后，相信你对另外一个高频面试题：“为什么 MySQL 不建议使用 NULL 作为列默认值？”也有了答案。

Boolean 类型如何表示？

MySQL 中没有专门的布尔类型，而是用 TINYINT(1) 类型来表示布尔值。TINYINT(1) 类型可以存储 0 或 1，分别对应 false 或 true。

反向链接