一、MySQL常用的字段类型

MySQL字段类型主要分为三大类：数值类型、字符串（文本）类型、日期时间类型。

1. 数值类型

• 整数类型：

  • TINYINT： 1字节，范围：有符号(-128_{127)，无符号(0}255)。常用于状态码、布尔值（MySQL没有原生布尔类型，常用TINYINT(1)表示TRUE/FALSE）。
  • SMALLINT： 2字节，范围：有符号(-32768_{32767)，无符号(0}65535)。
  • MEDIUMINT： 3字节。
  • INT / INTEGER： 4字节，最常用的整数类型。用于ID、数量、年龄等。
  • BIGINT： 8字节，用于非常大的数字，如自增主键在极大规模应用中。

• 浮点数类型（近似值）：

  • FLOAT： 4字节单精度浮点数。
  • DOUBLE： 8字节双精度浮点数。用于科学计算、地理坐标等需要小数但精度要求不极端的场景。

• 定点数类型（精确值）：

  • DECIMAL(M, D)： 精确的小数，以字符串形式存储。M是总位数（精度），D是小数点后的位数（标度）。必须用于存储精确的数字，如金额、汇率等。

2. 字符串（文本）类型

• 短文本：

  • CHAR(N)： 定长字符串。长度固定为N（0-255）。如果存入的字符串长度小于N，会用空格填充到指定长度。检索效率高，适合存储长度固定或非常接近的数据，如MD5哈希值（32位）、UUID（36位）、国家代码（如’CN‘）、枚举代码（如’M‘， ’F‘）。
  • VARCHAR(N)： 变长字符串。长度可变，最大长度为N（0-65535字节，实际最大长度受行大小限制，通常是65532字节）。只使用必要的空间+1或2个字节来记录长度。节省空间，是最常用的字符串类型，用于存储长度不定的数据，如姓名、标题、描述等。

• 长文本：

  • TEXT： 用于存储长文本字符串，最大长度为65,535字符。不需要指定长度。分为 TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT。适用于文章内容、产品描述等大段文字。注意：TEXT类型不能有默认值。

• 二进制数据：

  • BLOB： 用于存储二进制大对象（如图片、音频、视频等）。同样分为 TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB。通常不建议将文件直接存到数据库，而是存储文件路径，将文件本身放在对象存储或文件系统中。

• 枚举与集合：

  • ENUM(‘value1‘， ’value2‘， ...)： 枚举类型，只能从预定义的值列表中选择一个。如 gender ENUM(‘M‘， ’F‘)。节省空间但修改枚举值需要ALTER TABLE，不推荐频繁使用。
  • SET(‘value1‘， ’value2‘， ...)： 集合类型，可以从预定义的值列表中选择多个。如 hobbies SET(‘Reading‘， ’Sports‘， ’Music‘)。

3. 日期时间类型

• DATE： 仅存储日期，格式 ’YYYY-MM-DD‘。
• TIME： 仅存储时间，格式 ’HH:MM:SS‘。
• DATETIME： 存储日期和时间，格式 ’YYYY-MM-DD HH:MM:SS‘。与时区无关，存储什么值就是什么值。
• TIMESTAMP： 时间戳，存储从’1970-01-01 00:00:01‘ UTC到现在的秒数。与时区有关，存入和查询时会根据数据库会话的时区进行转换。范围比DATETIME小（2038年问题），但占用空间更小（4字节 vs DATETIME的8字节在MySQL 5.6.4之前）。
• YEAR： 存储年份。

二、如何选择字段类型及原因

选择原则的核心是：在满足业务需求的前提下，选择最精确、最小的数据类型。

1. 确定数据本质： 首先是数字、字符串还是日期？
2. 确定数值范围：
   • 整数：根据范围选择。年龄用TINYINT UNSIGNED（0-255足够），用户ID用INT UNSIGNED（约42亿），极大应用考虑BIGINT。
   • 小数：需要精确计算（如金额）用DECIMAL。可以接受近似值且范围很大时用FLOAT或DOUBLE。
3. 确定字符串特性：
   • 长度是否固定？ 固定用CHAR（性能好），不固定用VARCHAR（节省空间）。
   • 长度多大？ 短文本（255字符内）用VARCHAR，长文本（超过255）用TEXT系列。
4. 确定时间特性：
   • 是否需要记录时间？不需要用DATE。
   • 是否需要与时区交互？需要（如国际化应用）用TIMESTAMP，不需要用DATETIME。
   • 是否要记录毫秒？MySQL 5.6.4+的DATETIME和TIMESTAMP都支持小数秒。

为什么？

• 更小的数据类型 → 更少磁盘和内存占用 → 更少的I/O操作 → 更高的查询性能和并发能力。
• 更精确的数据类型 → 更好的数据完整性，从源头上避免无效数据（如用INT存年龄，就不会存入’abc‘）。

三、如何填写长度及原因

这里的“长度”主要针对数值类型的显示宽度和字符串类型的最大长度。

1. 字符串类型 (CHAR(N), VARCHAR(N))

• 如何填写： N代表字符的最大数量（在utf8mb4编码下，一个字符可能占用1-4个字节）。你应该根据业务规则确定一个合理的最大值。
  • username VARCHAR(50)： 认为50个字符足够长的用户名。
  • password CHAR(60)： 用于存储bcrypt等哈希后的固定长度密码字符串。
  • email VARCHAR(255)： 电子邮件地址的标准最大长度。
• 为什么：
  • 为VARCHAR(N)设置一个合理的最大值可以防止存储过长、无意义的数据，保证数据质量。
  • 设置过大（如所有字符串都用VARCHAR(255)）会导致查询时临时表或排序操作更容易使用磁盘而不是内存，降低性能。因为MySQL会为行分配可能的最大空间来进行这些操作。

2. 数值类型 (INT(M), DECIMAL(M, D))

• INT(M)中的M：
  • 这是一个巨大的误解！ INT(5) 和 INT(11) 的存储空间和取值范围完全相同（4字节，-2147483648~2147483647）。
  • M被称为显示宽度。它只有在与ZEROFILL一起使用时才有意义，会在数字左侧用零填充到指定宽度。ZEROFILL已被弃用，所以基本上可以忽略M。定义id INT 和 id INT(11) 没有本质区别。
• DECIMAL(M, D)中的M和D：
  • 这里的M和D是至关重要的！ 它们定义了数字的精度和标度。
  • M是总位数（1_{65），`D`是小数点后的位数（0}30，且必须<=M）。
  • 如何填写： 根据业务需求确定。例如：
    • 存储金额，精确到分：DECIMAL(10， 2) -> 总共10位，小数点后2位。最大可存 99，999，999.99。
    • 存储高精度科学数据：DECIMAL(20， 10)。

总结与最佳实践

核心思想：永远根据你要存储的数据的实际特性来选择最合适的类型，并为其指定一个足够用但又不浪费的最小空间。