【重学 MySQL】五十四、整型数据类型

在MySQL中，整型数据类型用于存储整数值，它们的主要特点是能够存储不同范围和精度的整数，并且可以通过索引和约束来提高数据的查询性能。

整型类型

TINYINT

• 描述：TINYINT是MySQL中最小的整数类型。
• 存储大小：1个字节（8位）。
• 存储范围：
  • 有符号（SIGNED）：-128到127。
  • 无符号（UNSIGNED）：0到255。
• 用途：通常用于存储布尔值（0或1）或者非常小的整数值。

SMALLINT

• 描述：SMALLINT类型用于存储较小的整数值。
• 存储大小：2个字节（16位）。
• 存储范围：
  • 有符号（SIGNED）：-32768到32767。
  • 无符号（UNSIGNED）：0到65535。
• 用途：适用于存储中等偏小范围内的整数值。

MEDIUMINT

• 描述：MEDIUMINT类型用于存储中等大小的整数值。
• 存储大小：3个字节（24位）。
• 存储范围：
  • 有符号（SIGNED）：-8388608到8388607。
  • 无符号（UNSIGNED）：0到16777215。
• 用途：适用于存储中等范围内的整数值。

INT（或INTEGER）

• 描述：INT是MySQL中最常用的整数类型之一，也是标准SQL中的INTEGER类型的同义词。
• 存储大小：4个字节（32位）。
• 存储范围：
  • 有符号（SIGNED）：-2147483648到2147483647。
  • 无符号（UNSIGNED）：0到4294967295。
• 用途：适用于存储大多数整数值的存储和计算需求。

BIGINT

• 描述：BIGINT类型用于存储极大的整数值。
• 存储大小：8个字节（64位）。
• 存储范围：
  • 有符号（SIGNED）：-9223372036854775808到9223372036854775807。
  • 无符号（UNSIGNED）：0到18446744073709551615。
• 用途：适用于存储需要极大数值范围的场景，如大数的运算和存储。

可选属性

在MySQL中，整型数据类型具有一些可选属性，这些属性允许用户根据具体需求对数据类型进行定制。

UNSIGNED

• 描述：UNSIGNED属性用于指定整数类型为无符号，即不允许存储负数。使用UNSIGNED属性可以将整数的取值范围上移到从0开始的区间，从而允许存储更大的正数。
• 适用场景：当确定某个整数列不会存储负数时，可以使用UNSIGNED属性来扩大其取值范围。

ZEROFILL

• 描述：ZEROFILL属性用于指定当整数的显示宽度不足时，用0在左边填充至指定宽度。需要注意的是，ZEROFILL属性会自动为当前列添加UNSIGNED属性，因为负数不能用0填充。

• 显示宽度：与显示宽度属性（M）配合使用，ZEROFILL属性可以确保整数的显示格式满足特定要求。例如，定义为INT(5) ZEROFILL的列，在存储数值42时，将显示为00042。

• 注意事项：

  1. ZEROFILL属性不会影响整数的存储大小或取值范围。
  2. 如果存储的整数超过了指定的显示宽度，将按照实际位数进行显示，不会进行截断或报错。

显示宽度（M）

• 描述：显示宽度属性（M）用于指定MySQL在显示整数时使用的字符数。这个属性是一个可选的显示属性，它并不限制整数的取值范围或存储大小。

• 取值范围：M的取值范围是0到255之间的整数。

• 注意事项：

  1. 对于大多数应用来说，指定显示宽度是没有实际意义的，因为它不会限制整数的合法范围或存储大小。
  2. 显示宽度属性主要用于MySQL的一些交互工具（如mysql命令行客户端）在显示数据时提供格式化效果。
  3. 从MySQL 8.0.17开始，整数数据类型不推荐使用显示宽度属性。

AUTO_INCREMENT

• 描述：AUTO_INCREMENT属性用于指定某个整数列为自增长列。当向表中插入新行时，该列的值将自动增加，通常用于生成唯一的主键值。
• 适用场景：主键列通常设置为AUTO_INCREMENT属性，以确保每条记录都有一个唯一的标识符。

注意事项

• 选择合适的数据类型：在选择整型数据类型时，应根据实际数据的范围和精度来选择最合适的类型。较小的数据类型可以减少存储空间的占用，并提高查询性能。
• 避免过度使用可选属性：虽然整型数据类型的可选属性提供了很大的灵活性，但过度使用这些属性可能会增加数据库的复杂性和维护成本。因此，在使用这些属性时应谨慎考虑其必要性和适用性。

综上所述，MySQL整型数据类型具有UNSIGNED、ZEROFILL、显示宽度（M）和AUTO_INCREMENT等可选属性。这些属性允许用户根据具体需求对数据类型进行定制，以满足不同场景下的存储和查询需求。

适合场景

在MySQL中，不同的整型数据类型适用于不同的场景，以下是对各整型数据类型适用场景的详细归纳：

TINYINT

• 适用场景：TINYINT类型适用于存储非常小的整数值，通常用于存储布尔值（0或1）、状态码、小范围的计数器或ID等。由于其存储空间小（仅1个字节），因此适合在需要节省存储空间且整数值范围较小的场景下使用。

SMALLINT

• 适用场景：SMALLINT类型适用于存储较小的整数值，如年份（特别是近现代的年份，范围在1900~2155之间，如果使用UNSIGNED则范围更大）、人口数量、小型计数器等。其存储空间为2个字节，比TINYINT稍大，但能够存储的整数值范围也更广。

MEDIUMINT

• 适用场景：MEDIUMINT类型适用于存储中等大小的整数值，如文章字数、文件大小、中等范围的计数器等。其存储空间为3个字节，能够存储的整数值范围比SMALLINT更大。

INT（或INTEGER）

• 适用场景：INT类型是最常用的整数类型之一，适用于存储大多数整数值的存储和计算需求。它通常用于存储用户ID、订单号、产品编号等需要唯一标识的整数值。INT类型的存储空间为4个字节，能够存储的整数值范围非常广，足以满足大多数应用场景的需求。

BIGINT

• 适用场景：BIGINT类型适用于存储极大的整数值，如大数的运算结果、唯一标识符（如UUID的整数部分）、金融数据中的大金额等。其存储空间为8个字节，能够存储的整数值范围非常大，足以满足需要存储极大数值的场景。

特殊场景下的选择

• UNSIGNED属性：当确定某个整数列不会存储负数时，可以使用UNSIGNED属性来扩大其取值范围。例如，存储年龄、月份、星期等时，可以使用UNSIGNED TINYINT或UNSIGNED SMALLINT来节省存储空间。
• AUTO_INCREMENT属性：当需要生成唯一的主键值时，可以使用AUTO_INCREMENT属性。这通常用于用户ID、订单号等需要唯一标识的字段。

综上所述，在选择MySQL整型数据类型时，应根据实际数据的范围和精度来选择最合适的类型。同时，还可以根据具体需求选择是否使用UNSIGNED、AUTO_INCREMENT等可选属性来进一步定制数据类型。

注意事项

1. UNSIGNED属性：可以将整型列定义为UNSIGNED，以禁止出现负数。这会将取值范围上移到从0开始的区间。对于DECIMAL类型，定义为UNSIGNED不会扩大取值范围，而只会去掉负数部分。
2. 显示宽度：在定义整型列时，可以为其指定一个可选的显示宽度M（1~255的整数）。这决定着MySQL将用多少个字符来显示该列里的值。但需要注意的是，这个显示宽度并不影响实际的存储大小或取值范围。
3. 性能考虑：在选择整型数据类型时，应根据实际数据的范围和精度来选择最合适的类型。较小的数据类型可以减少存储空间的占用，并提高查询性能。

如何选择

在MySQL中，整型数据类型的选择对于数据库的性能和存储效率至关重要。

1. 存储范围
   • TINYINT：存储范围为-128到127（有符号）或0到255（无符号），占用1个字节。适用于存储非常小的整数值，如布尔值或枚举值。
   • SMALLINT：存储范围为-32,768到32,767（有符号）或0到65,535（无符号），占用2个字节。适用于存储较小的整数值。
   • MEDIUMINT：存储范围为-8,388,608到8,388,607（有符号）或0到16,777,215（无符号），占用3个字节。适用于存储中等大小的整数值。
   • INT：存储范围为-2,147,483,648到2,147,483,647（有符号）或0到4,294,967,295（无符号），占用4个字节。这是最常用的整数类型，适用于大多数场景。
   • BIGINT：存储范围为-9,223,372,036,854,775,808到9,223,372,036,854,775,807（有符号）或0到18,446,744,073,709,551,615（无符号），占用8个字节。适用于存储极大的整数值。
2. 存储空间
   • 较小的整数类型（如TINYINT和SMALLINT）通常占用较少的存储空间，而较大的整数类型（如BIGINT）占用较多的存储空间。如果存储空间是一个关键因素，可以选择较小的整数类型。
3. 性能
   • 较小的整数类型通常比较大的整数类型具有更好的性能，因为它们需要处理的数据量更小。在处理大量数据时，这可能会产生显著的性能差异。
4. 数据范围
   • 确保所选的整数类型能够容纳数据范围。如果数据可能超出了某个整数类型的范围，需要选择一个更大的整数类型来避免数据溢出。
5. 有符号与无符号
   • 如果知道数据永远不会是负数，可以选择无符号整数类型。无符号整数类型的正数范围是有符号整数类型的两倍。

建议

1. 根据需求选择：在选择整型数据类型时，首先要明确需要存储的数据的范围和大小。根据实际需求选择合适的整数类型，避免过大或过小的类型导致存储空间的浪费或数据溢出。
2. 考虑性能：在处理大量数据时，较小的整数类型通常具有更好的性能。因此，在性能要求较高的场景下，可以考虑选择较小的整数类型。
3. 无符号类型：如果确定数据不会包含负数，可以使用无符号整数类型来扩大正数的存储范围。
4. 避免使用显示宽度：MySQL中的显示宽度并不限制数据的存储范围，只是影响查询结果的显示格式。因此，在创建表时，应避免使用整数的显示宽度（如INT(10)），直接使用整数类型（如INT）即可。

综上所述，在选择MySQL整型数据类型时，需要综合考虑存储范围、存储空间、性能、数据范围以及有符号与无符号等因素。根据实际需求选择合适的整数类型，以提高数据库的存储效率和性能。