【重学 MySQL】五十八、文本字符串（包括 enum set）类型

【重学 MySQL】五十八、文本字符串（包括 enum set）类型

在 MySQL 中，文本字符串类型用于存储字符数据。这些类型包括 CHAR、VARCHAR、TEXT 系列（如 TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT）、ENUM 和 SET。每种类型都有其特定的用途和存储特性。

CHAR 和 VARCHAR

• CHAR(n):
  • 固定长度字符类型。
  • 如果存储的字符串长度小于 n，则会在右侧填充空格以达到长度 n。
  • 适用于存储长度几乎相同的字符串，如国家代码、邮政编码等。
  • 存储空间是固定的，不依赖于实际存储的字符串长度。
• VARCHAR(n):
  • 可变长度字符类型。
  • 实际存储空间是字符串长度加上 1 或 2 个字节（用于存储字符串长度）。
  • 适用于存储长度变化较大的字符串，如人名、电子邮件地址等。
  • 最大长度为 65,535 字节（实际最大长度取决于字符集和最大行大小）。

哪些情况使用 CHAR 或 VARCHAR 更好

• 情况1：存储很短的信息。比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的，结果得不偿失。
• 情况2：固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。
• 情况3：十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。
• 情况4：具体存储引擎中的情况：
  • MyISAM 数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化，从而使数据检索更快，用空间换时间。
  • MEMORY 存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的。
  • InnoDB存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好。

TEXT 系列

• TINYTEXT:
  • 最大长度为 255 字节。
• TEXT:
  • 最大长度为 65,535 字节（约 64 KB）。
• MEDIUMTEXT:
  • 最大长度为 16,777,215 字节（约 16 MB）。
• LONGTEXT:
  • 最大长度为 4,294,967,295 字节（约 4 GB）。
• 特点:
  • 适用于存储大量文本数据，如文章、评论等。
  • 存储和检索速度通常比 CHAR 和 VARCHAR 慢。

ENUM

• ENUM(‘value1’, ‘value2’, …, ‘valueN’):
  • 枚举类型，允许存储一个预定义集合中的一个值。
  • 枚举值在内部以整数存储，但显示时以字符串形式呈现。
  • 适用于存储具有固定选项集合的字段，如性别（‘男’, ‘女’）、状态（‘启用’, ‘禁用’）等。
  • 枚举值在定义时必须是字符串常量，且区分大小写。

SET

• SET(‘value1’, ‘value2’, …, ‘valueN’):
  • 集合类型，允许存储一个预定义集合中的一个或多个值（以逗号分隔）。
  • 类似于 ENUM，但允许存储多个值。
  • 适用于存储具有多个选项的字段，如兴趣爱好（‘阅读’, ‘旅行’, ‘运动’）等。
  • 集合值在内部以整数存储，但显示时以字符串形式呈现。
  • 每个集合成员在定义时必须是字符串常量，且区分大小写。

示例

CREATE TABLE example (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fixed_char CHAR(10),
    variable_char VARCHAR(255),
    short_text TINYTEXT,
    long_description TEXT,
    gender ENUM('Male', 'Female', 'Other'),
    hobbies SET('Reading', 'Traveling', 'Sports', 'Cooking')
);

INSERT INTO example (fixed_char, variable_char, short_text, long_description, gender, hobbies)
VALUES ('ABC     ', 'Variable Length String', 'Short Text', 'This is a long description.', 'Male', 'Reading,Sports');

注意事项

1. 字符集和排序规则：
   • 字符类型字段可以指定字符集和排序规则（collation），以支持多语言存储和比较。
   • 常用的字符集包括 utf8、utf8mb4、latin1 等。
2. 存储和性能：
   • CHAR 和 VARCHAR 类型的字段在存储和检索时通常比 TEXT 类型的字段更快。
   • 对于大量文本数据，使用 TEXT 类型字段更为合适。
3. 索引：
   • CHAR、VARCHAR 和 TEXT 字段都可以创建索引，但 TEXT 字段的索引有一些限制（如前缀索引）。
   • ENUM 和 SET 字段也可以创建索引，且索引效率通常较高。

通过了解这些文本字符串类型及其特性，可以更好地设计数据库表结构，以满足不同应用场景的需求。