1.1 数据类型分类体系#

• 概念解释：数据类型（Data Type） 是数据库管理系统中用于规定字段所能存储数据特征（如大小、格式、行为）的约束规范。合理选择数据类型不仅能保证数据的准确性，还能极大优化磁盘空间和查询性能。

• 笔记：

1. 数值类型：

   • BIT(M)：位类型。M 指定位数，默认值 1，范围 1-64。

   • TINYINT：1 字节。带符号范围 -128127，无符号（UNSIGNED）范围 0255。默认有符号。

   • BOOL：本质上使用 0 和 1 表示真和假（等同于 TINYINT(1)）。

   • SMALLINT：2 字节。带符号范围 -3276832767，无符号范围 065535。

   • MEDIUMINT：3 字节。带符号范围 -83886088388607，无符号 016777215。

   • INT：4 字节。带符号范围 -2^31 ~ 2^31-1，无符号范围 0 ~ 2^32-1。

   • BIGINT：8 字节。带符号范围 -2^63 ~ 2^63-1，无符号范围 0 ~ 2^64-1。

   • FLOAT[(M,D)]：浮点数。占用 4 字节。M 指定显示长度，D 指定小数位数。

   • DOUBLE[(M,D)]：双精度浮点数。占用 8 字节，精度比 FLOAT 更大。

   • DECIMAL(M,D)：定点数。M 指定总长度，D 指定小数点位数。

2. 文本与二进制类型：

   • CHAR(size)：固定长度字符串，最大 255。

   • VARCHAR(size)：可变长度字符串，最大字节数 65535。

   • BLOB：二进制数据。

   • TEXT：大文本。注意：不支持全文索引，不支持默认值。

3. 时间日期类型：

   • DATE：日期类型（yyyy-mm-dd）。

   • DATETIME：日期时间类型（yyyy-mm-dd hh:mm）。

   • TIMESTAMP：时间戳（yyyy-mm-dd hh:mm）。

4. 枚举与集合类型：

   • ENUM：枚举对象。值为字符串，单选，值来自表创建时规定的选项。

   • SET：集合对象。值为字符串，可零或多选，成员间用逗号隔开（成员本身不能含逗号）。

2.1 TINYINT 与 UNSIGNED 机制#

• 概念解释：无符号类型（UNSIGNED） 是通过牺牲负数范围，将符号位（最高位）作为数据位来使用，从而使正数部分的存储上限翻倍的整数类型机制。

• 笔记：

  • 默认行为：在 MySQL 中，整型默认是有符号的。可以通过 UNSIGNED 关键字显式声明为无符号。

  • 越界报错：如果插入的数据超出了类型的有效范围（例如向 TINYINT 插入 128，或向 TINYINT UNSIGNED 插入 -1 或 256），MySQL 会拦截并报错 ERROR 1264 (22003): out of range value for column。

  • 【深度拓展：为什么不建议依赖 UNSIGNED 扩容？】

    • 提问：如果一个整型字段的数据可能超过当前类型的最大值，应该使用 UNSIGNED 还是直接升级数据类型？

    • 解答：尽量不使用 UNSIGNED。对于 INT 类型存放不下的正数据，INT UNSIGNED 虽然提升了一倍的上限，但也极其容易再次触顶。与其如此，不如在表结构设计时，直接将 INT 提升为 BIGINT 类型，同时还能避免无符号数在相减运算时产生的隐式溢出 Bug。

2.2 BIT 类型#

• 概念解释：位图存储（Bit-field） 是一种极致压缩空间的数据结构，直接操作二进制位（0或1）来记录状态。

• 笔记：

  • 基本语法：BIT[(M)]。M 表示位字段的位数，范围 1~64，省略 M 则默认为 1。

  • 显示特性：BIT 字段在客户端显示时，是按照其数值对应的 ASCII 码值 显示的。（例如插入数值 10，在普通查询中可能显示为空白或特殊字符，因为 ASCII 中的 10 是换行符）。

  • 应用场景：当字段只有两种状态（如 0 或 1）时，强烈建议定义为 BIT(1) 以最大化节省存储空间。

2.3 浮点数 (FLOAT) 与定点数 (DECIMAL)#

• 概念解释：定点数（Fixed-point） 与底层采用科学计数法近似存储的浮点数不同，定点数在数据库底层采用字符串或分段整数的方式精确存储每一位数字，绝对不会发生精度丢失。

• 笔记：

  • FLOAT 特性：

    • 语法：FLOAT[(M,D)] [UNSIGNED]。M=显示总长度，D=小数位数，占 4 字节。

    • 范围案例：FLOAT(4,2) 带符号范围为 -99.99 ~ 99.99；无符号（UNSIGNED）范围为 0 ~ 99.99。

    • 舍入规则：MySQL 保存浮点数值时，若超出指定小数位，会自动进行四舍五入。

    • 精度限制：FLOAT 的实际精度大约只有 7 位，超过此精度的数据会出现尾数偏差。

  • DECIMAL 特性：

    • 语法：DECIMAL(M,D) [UNSIGNED]。

    • 极限参数：整数最大总位数 M 为 65（默认10），支持的小数最大位数 D 为 30（默认0）。

    • 核心对比：DECIMAL 和 FLOAT 最大的区别在于精度。DECIMAL 的精度绝对准确。

    • 最佳实践：如果业务场景要求小数精度极高（例如财务金额、货币计算），必须选择 DECIMAL。

3.1 CHAR 类型#

• 概念解释：定长分配机制（Fixed-length Allocation） 指无论存入的数据实际有多长，系统都会在磁盘上预先划定固定大小的存储空间。不足部分通常以空格填充。

• 笔记：

  • 语法：CHAR(L)。L 表示可存储的字符数量（非字节数）。

  • 最大极限：L 的最大值不能超过 255。如果声明 CHAR(256) 会报错，并提示使用 BLOB 或 TEXT。

  • 特点：直接开辟好对应空间，效率高，但浪费磁盘空间。

3.2 VARCHAR 类型与编码计算#

• 概念解释：字符编码空间占用差异。在 UTF-8 编码中，一个汉字或字符通常占用 3 个字节；而在 GBK 编码中，一个汉字或字符占用 2 个字节。这直接影响了变长字符串的最大声明长度。

• 笔记：

  • 语法：VARCHAR(L)。L 表示字符长度。

  • 字节上限：VARCHAR 整行的最大字节长度被限制在 65535 个字节。

  • 变长底层开销：VARCHAR 会额外使用 1 到 3 个字节用于记录当前数据的实际大小，因此 65535 个字节中，有效数据字节数实际为 65532 字节。

  • L 值极限推导：

    • UTF-8 编码下：最大字符数 L = 65532 / 3 = 21844。若声明 VARCHAR(21845) 将直接报错 Row size too large。

    • GBK 编码下：最大字符数 L = 65532 / 2 = 32766。

  • 特点：在不超过自定义范围的情况下，用多少开辟多少。磁盘空间节省，但需要计算长度，效率相对较低。

3.3 定长与变长的业务选型策略#

• 笔记：

  • 选用 CHAR：数据确定长度都一致时使用（如：身份证号、手机号、MD5哈希值等）。

  • 选用 VARCHAR：数据长度有变化时使用（如：姓名、地址），但必须确保设定的最大长度能兼容最长业务数据。

4.1 DATE、DATETIME 与 TIMESTAMP#

• 概念解释：时间戳（Timestamp） 指自格林威治时间 1970年01月01日 00时00分00秒起至现在的总秒数。在 MySQL 中它是一个会随操作自动更新的动态时间字段。

• 笔记：

  • DATE：日期类型，格式为 'yyyy-mm-dd'，占用 3 字节。

  • DATETIME：日期时间格式，格式为 'yyyy-mm-dd HH:ii:ss'，支持范围从 1000 年到 9999 年，占用 8 字节。

  • TIMESTAMP：时间戳类型，格式同 DATETIME，记录从 1970 年开始的时间，占用 4 字节。

  • 核心特性：当我们对一行数据进行 INSERT（未显式指定时间戳字段）或 UPDATE（更新了该行的其他字段）操作时，TIMESTAMP 类型的字段会自动补上或更新为当前的系统时间。

5.1 ENUM (单选) 与 SET (多选)#

• 概念解释：位掩码（Bitmask）映射。为了在极小的空间内存储多个组合状态，系统将每个选项映射为一个二进制位（2的次幂，如 1, 2, 4, 8）。当选中多个选项时，只需将它们的映射数字相加即可产生一个唯一的标识值。

• 笔记：

  • ENUM 类型：“单选”类型。

    • 语法：ENUM('选项1', '选项2', '选项3'...)。

    • 底层存储：最终单元格实际只存储一个值。出于效率考虑，这些值在底层实际存储的是数字。每个选项依次对应数字：1, 2, 3… 最多支持 65535 个选项。

    • 操作：插入数据时，既可以写字符串常量，也可以直接写入对应的数字编号（通常当作正常数组下标来理解，但起始为1）。

  • SET 类型：“多选”类型。

    • 语法：SET('选项1', '选项2', '选项3'...)。

    • 底层存储：单元格可存储任意多个值。同样出于效率考虑底层存储为数字。

    • 映射规则：基于比特位位置，选项依次对应数字：1, 2, 4, 8, 16, 32… 最多支持 64 个选项。

  • 规范建议：强烈不建议在插入或查询 ENUM/SET 字段时直接使用底层的映射数字，这会严重降低 SQL 语句的可读性。

5.2 FIND_IN_SET#

• 函数名：FIND_IN_SET

• 函数原型：

  SQL

  1
  INT FIND_IN_SET(STRING sub, STRING str_list);

  功能与参数说明：

  • 功能：用于在一个以逗号分隔的字符串集合（str_list）中，精确查找某个指定的子串（sub）。这是解决“如何查询 SET 类型中包含某个具体选项的所有记录”的官方标准方案（因为使用 = 只能做完全匹配，无法实现集合的包含查询）。

  • 参数：

    • sub：需要查找的目标字符串（如选项名 '登山'）。

    • str_list：被查找的逗号分隔字符串集（如数据库中的 SET 字段名 hobby）。

  • 返回值：

    • 如果 sub 存在于 str_list 中，则返回其在列表中的索引下标（正整数，起始位置视具体版本而定，通常非0即为真）。

    • 如果 sub 不在 str_list 中，或者 str_list 为空字符串，则返回 0。