探秘 MySQL 数据类型的艺术：性能与存储的精妙平衡

文章目录

---

前言

在数据库设计中，选择合适的数据类型对性能、存储效率和数据完整性至关重要。MySQL 提供了丰富的数据类型，帮助开发者更灵活地处理不同的数据需求。然而，不同的数据类型各有优缺点，了解这些特性可以帮助我们更高效地设计和管理数据库。本篇文章将深入探讨 MySQL 的主要数据类型、使用场景和优化建议，帮助读者在开发过程中做出明智的选择。

---

🎀一、数据类型分类

以下是 MySQL 数据类型分类的表格形式：
类别数据类型描述最大长度数值类型TINYINT1 字节整数，范围 -128 到 1271 字节SMALLINT2 字节整数，范围 -32,768 到 32,7672 字节MEDIUMINT3 字节整数，范围 -8,388,608 到 8,388,6073 字节INT4 字节整数，范围 -2,147,483,648 到 2,147,483,6474 字节BIGINT8 字节整数，范围 -9,223,372,036,854,775,808 到 9,223,372,036,854,775,8078 字节FLOAT4 字节单精度浮点数4 字节DOUBLE8 字节双精度浮点数8 字节DECIMAL/NUMERIC精确小数，指定精度和小数位数根据定义而定BIT位类型，用于存储位值最大 8 字节（每个比特）字符串类型CHAR固定长度字符串最大 255 字符VARCHAR可变长度字符串最大 65,535 字符TINYTEXT最大 255 字符的文本255 字符TEXT最大 65,535 字符的文本65,535 字符BINARY固定长度二进制字符串最大 255 字节VARBINARY可变长度二进制字符串最大 65,535 字节TINYBLOB最大 255 字节的二进制数据255 字节BLOB最大 65,535 字节的二进制数据65,535 字节MEDIUMBLOB最大 16,777,215 字节的二进制数据16,777,215 字节LONGBLOB最大 4,294,967,295 字节的二进制数据4,294,967,295 字节日期和时间类型DATE日期，格式为 ‘YYYY-MM-DD’-TIME时间，格式为 ‘HH:MM’-DATETIME日期和时间，格式为 ‘YYYY-MM-DD HH:MM’-TIMESTAMP自 1970 年 1 月 1 日以来的时间戳-YEAR年份，格式为 ‘YYYY’-其他类型ENUM枚举类型，字符串的集合-SET集合类型，可以包含零个或多个字符串值-

🎀二、整数类型（举例

TINYINT

和

INT

）

🎫2.1

TINYINT

和

INT

类型的定义

2.1.1

TINYINT

• TINYINT 是 MySQL 中的最小整数类型，使用 1 字节（8 位）来存储数值。
• 有符号范围：-128 到 127
• 无符号范围：0 到 255

2.1.2

INT

• INT 是 MySQL 中常用的标准整数类型，使用 4 字节（32 位）来存储数值。
• 有符号范围：-2,147,483,648 到 2,147,483,647
• 无符号范围：0 到 4,294,967,295

🎫2.2 表的操作示例

2.2.1 创建包含

TINYINT

和

INT

类型的表

CREATETABLE user_info (
    user_id INTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,-- 无符号的INT，常用于主键
    age TINYINT,-- 存储年龄，使用TINYINT，因为年龄不会超过127
    score INT,-- 存储分数，使用INTstatusTINYINTUNSIGNED-- 存储状态码，使用无符号的TINYINT);

2.2.2 插入数据示例

INSERTINTO user_info (age, score,status)VALUES(25,300,1),(45,2000,0),(30,500,1);

2.2.3 查询数据

SELECT*FROM user_info;

2.2.4 更新数据

UPDATE user_info 
SET score =350WHERE user_id =1;

2.2.5 删除记录

DELETEFROM user_info 
WHERE user_id =2;

🎫2.3 不同类型之间的问题

2.3.1 类型范围问题

• 当插入超过类型范围的值时，可能会触发溢出或报错。例如：- 对于 TINYINT，如果插入的值超过 127（有符号）或 255（无符号），会导致溢出。- 对于 INT，插入超过 2,147,483,647（有符号）或 4,294,967,295（无符号）范围的值时也会出错。示例：INSERTINTO user_info (age, score,status)VALUES(128,1000,1);-- 错误，age 超过 TINYINT 的范围

2.3.2 有符号和无符号类型的转换问题

• 无符号类型只能存储正数，而有符号类型可以存储负数。在不同类型之间转换时，可能会导致数据变化。
• 如果将负数插入无符号的 TINYINT 或 INT 列中，MySQL 会转换为非常大的正数。

示例：

CREATETABLE test_conversion (
    val_signed TINYINT,
    val_unsigned TINYINTUNSIGNED);INSERTINTO test_conversion (val_signed, val_unsigned)VALUES(-1,-1);-- 无符号字段的值会被转换SELECT*FROM test_conversion;-- 结果：val_signed = -1, val_unsigned = 255

2.3.3 自动类型提升

• 当不同大小的整数类型进行运算时，MySQL 会自动将较小的类型提升为较大的类型。例如，在 TINYINT 和 INT 的运算中，TINYINT 会被提升为 INT，以避免溢出。

示例：

SELECT age + score FROM user_info;-- age 为 TINYINT，score 为 INT，age 会自动提升为 INT 进行运算

2.3.4 整数类型与其他类型的转换

• MySQL 在处理整数与其他类型（如字符串、浮点数）之间的转换时，可能会发生数据丢失或精度问题。例如，将浮点数转换为整数时，小数部分会被截断。

示例：

SELECT CAST(123.456ASINT);-- 结果为 123，浮点数的小数部分被去掉

2.3.5 数据存储效率

• 使用 TINYINT 存储小的整数数据可以节省空间。例如，对于年龄、状态码等数据，TINYINT 是更合适的选择，因为它比 INT 节省内存。
• 但是，如果数据范围可能超过 TINYINT 的范围，就需要使用 INT 或其他更大的类型。

🎫2.4 示例：查看不同整数类型之间的比较和行为

SELECT128= CAST(128ASTINYINT);-- 结果为 0，因为 128 超出 TINYINT 的范围，被转换为 -128

总结：

TINYINT

和

INT

类型主要在存储空间和数值范围上有所不同，合理选择合适的类型可以提高数据库的存储效率和性能。在操作时要注意数据范围和类型转换问题，以避免意外的结果。

🎀三、浮点数类型

🎫3.1 浮点数类型的定义

3.1.1

FLOAT

• FLOAT 类型用于存储单精度浮点数，使用 4 字节的存储空间。
• 存储范围： - 有符号：-3.402823466E+38 到 -1.175494351E-38，以及 1.175494351E-38 到 3.402823466E+38- 无符号：0 到 3.402823466E+38
• FLOAT 的有效精度通常是 7 位十进制数。当需要存储精度较低但范围较大的数值时，可以使用 FLOAT 类型。

3.1.2

DOUBLE

• DOUBLE 类型用于存储双精度浮点数，使用 8 字节的存储空间。
• 存储范围： - 有符号：-1.7976931348623157E+308 到 -2.2250738585072014E-308，以及 2.2250738585072014E-308 到 1.7976931348623157E+308- 无符号：0 到 1.7976931348623157E+308
• DOUBLE 的有效精度通常是 15 位十进制数。适合需要高精度数值的场景，如科学计算或金融计算。

3.1.3

DECIMAL

• DECIMAL 类型用于存储定点小数，通常用于需要高精度的货币计算或财务数据。
• 通过指定 精度（总位数）和 标度（小数位数）来控制存储的数值。例如，DECIMAL(10, 2) 表示最多可以存储 10 位数，其中 2 位是小数位。
• 不同于 FLOAT 和 DOUBLE，DECIMAL 是准确存储小数点后的值，不存在浮点误差。

🎫3.2 表的操作示例

3.2.1 创建包含浮点数类型的表

CREATETABLE products (
    product_id INTUNSIGNEDAUTO_INCREMENTPRIMARYKEY,
    product_name VARCHAR(100),
    price DECIMAL(10,2),-- 使用 DECIMAL 类型存储价格，精确到两位小数
    discount FLOAT,-- 使用 FLOAT 类型存储折扣，精度不高但范围大
    rating DOUBLE-- 使用 DOUBLE 类型存储产品评分，需要更高的精度);

3.2.2 插入数据

INSERTINTO products (product_name, price, discount, rating)VALUES('Product A',199.99,0.15,4.5678),('Product B',299.50,0.10,4.1234),('Product C',499.99,0.20,4.9876);

3.2.3 查询数据

SELECT*FROM products;

3.2.4 更新数据

UPDATE products 
SET price =189.99, discount =0.20WHERE product_id =1;

🎫3.3 不同类型间的问题

3.3.1 浮点精度问题

• FLOAT 和 DOUBLE 都是近似浮点数，这意味着它们无法精确地存储所有小数。这在某些应用中可能导致精度丢失，特别是在金融计算中。
• 例如，浮点运算时可能会有微小的误差：

SELECT0.1+0.2;-- 结果可能是 0.30000000000000004，而不是预期的 0.3

因此，在需要精确计算的场合（如金额），应使用

DECIMAL

类型而不是

FLOAT

或

DOUBLE

。

3.3.2 存储大小与性能

• FLOAT 使用 4 字节，DOUBLE 使用 8 字节。DECIMAL 的存储空间取决于定义的精度和标度。如果你需要存储大范围的浮点数，并且对精度要求不高，可以选择 FLOAT 或 DOUBLE，以节省存储空间。
• DECIMAL 相比 FLOAT 和 DOUBLE 的性能稍差，因为它需要进行更多的数学计算来确保精度。

3.3.3 精度和范围的权衡

• FLOAT 和 DOUBLE 提供了更大的数值范围，但它们的精度有限。
• DECIMAL 提供了更高的精度，但它的数值范围有限。例如，DECIMAL(65,30) 的范围可以最大到 65 位十进制数，其中 30 位是小数位。

3.3.4 类型转换问题

• 在不同浮点类型之间进行转换时，可能会丢失精度。例如，从 DOUBLE 转换为 FLOAT 时，高精度部分可能会被截断。
• 例如：

CREATETABLE test_float_conversion (
    val_float FLOAT,
    val_double DOUBLE);INSERTINTO test_float_conversion (val_float, val_double)VALUES(123456.789,123456.789);SELECT val_float, val_double FROM test_float_conversion;-- 结果中 val_float 可能会显示为 123456.78，因为精度有限

🎫3.4 示例：浮点类型的比较和行为

3.4.1 计算浮点数

SELECT price * discount AS discounted_price 
FROM products 
WHERE product_id =1;

3.4.2 使用

DECIMAL

进行精确计算

SELECT price -(price * discount)AS final_price 
FROM products;

总结

• **FLOAT**：适用于对精度要求不高、但数值范围较大的场合。比如温度传感器的读数、折扣百分比等。
• **DOUBLE**：适用于需要更高精度的浮点数操作，比如科学计算、评级系统等。
• **DECIMAL**：适用于需要精确小数的场景，尤其是财务、货币计算等，不会出现浮点数的精度误差。

🎀四、字符串类型（举例

CHAR

和

VARCHAR

）

在 MySQL 中，

CHAR

和

VARCHAR

是两种常见的字符串类型，主要用于存储文本数据。它们的区别在于存储方式和数据长度的处理。下面通过详细的解释和示例来说明

CHAR

和

VARCHAR

类型的特点及应用。

🎫4.1

CHAR

类型

4.1.1 特点：

• 固定长度：CHAR 类型用于存储固定长度的字符串。如果插入的字符串长度小于定义的长度，MySQL 会在字符串的右侧用空格填充。
• 性能较好：由于其固定长度，CHAR 类型在处理长度相对固定的数据时性能更高，比如状态码、国家代码等。
• 最大长度：最多可以存储 255 个字符。

4.1.2 使用场景：

适合存储长度固定的字段，例如国家代码、邮政编码、电话号码的国家区号等。

4.1.3 示例：

CREATETABLE char_example (
    country_code CHAR(2),-- 国家代码，例如 'US'、'CN'
    zip_code CHAR(5)-- 固定长度的邮政编码，例如 '12345');

4.1.4 插入数据：

INSERTINTO char_example (country_code, zip_code)VALUES('US','12345'),('CN','54321');

4.1.5 查询数据：

SELECT*FROM char_example;

在

CHAR

类型中，如果插入的字符串长度不足，会自动填充空格。例如，

CHAR(5)

类型插入

AB

后，实际存储的是

"AB "

，而不是仅存储

AB

。

🎫4.2

VARCHAR

类型

4.2.1 特点：

• 可变长度：VARCHAR 用于存储可变长度的字符串，不像 CHAR 会填充空格。存储时只占用实际长度的字符数加上一个或两个字节（根据存储的长度）来记录字符串的长度。
• 性能稍差：由于其长度是可变的，存取时的性能稍微低于 CHAR，但它节省了存储空间。
• 最大长度：最多可以存储 65,535 个字符（具体长度取决于列的最大长度和表的行大小）。

4.2.2 使用场景：

适合存储长度不固定的字段，例如姓名、电子邮件地址、描述性文本等。

4.2.3 示例：

CREATETABLE varchar_example (
    full_name VARCHAR(50),-- 用户的全名，最多 50 个字符
    email VARCHAR(100)-- 用户的电子邮件地址，最多 100 个字符);

4.2.4 插入数据：

INSERTINTO varchar_example (full_name, email)VALUES('John Doe','[email protected]'),('Jane Smith','[email protected]');

4.2.5 查询数据：

SELECT*FROM varchar_example;

在

VARCHAR

类型中，插入的字符串长度是可变的。比如，如果定义了

VARCHAR(50)

，插入的字符串

"John Doe"

实际只占用 8 个字符的存储空间，而不会自动填充到 50 个字符。

🎫4.3

CHAR

和

VARCHAR

的区别与选择

4.3.1 区别总结：

特性

CHAR

VARCHAR

长度处理固定长度，不足部分填充空格可变长度，存储实际的字符数存储效率对于固定长度数据，效率更高对于可变长度数据，节省空间最大长度最多 255 个字符最多 65,535 个字符适用场景长度固定的字段，如国家代码等长度不固定的字段，如姓名、描述等

4.3.2 选择建议：

• 如果数据长度是固定的（如国家代码、邮政编码等），使用 CHAR。
• 如果数据长度不固定，使用 VARCHAR 以节省空间。

🎫4.4 示例：

CHAR

与

VARCHAR

的混合使用

CREATETABLE users (
    user_id INTAUTO_INCREMENTPRIMARYKEY,
    username VARCHAR(20),-- 用户名，长度不固定
    country_code CHAR(2),-- 国家代码，长度固定为2
    phone_number VARCHAR(15)-- 电话号码，长度不固定);

4.4.1 插入数据：

INSERTINTO users (username, country_code, phone_number)VALUES('Alice','US','1234567890'),('Bob','CN','0987654321');

4.4.2 查询数据：

SELECT*FROM users;

在这个示例中，我们使用了

CHAR(2)

存储国家代码，因为国家代码始终是两位字符；同时使用

VARCHAR(20)

和

VARCHAR(15)

存储用户名和电话号码，因为这些字段的长度是不固定的。

🎫4.5 性能和存储空间的考量

• 存储空间：VARCHAR 更节省存储空间，适合存储长度变化较大的字符串，而 CHAR 会在长度不够时填充空格，适合长度固定的数据。
• 查询性能：CHAR 因为是固定长度，在进行查询时性能相对更好，因为数据库可以更容易计算每个字段的起始位置。

因此，在设计数据库表时，选择合适的字符串类型可以在存储空间和查询性能之间取得平衡。

🎀五、日期和时间类型

在 MySQL 中，日期和时间类型用于存储日期、时间和日期时间组合。MySQL 提供了多种日期和时间类型，以适应不同的存储需求和应用场景。下面是常见的日期和时间类型的介绍、使用示例及其区别。

🎫5.1 日期和时间类型的定义

5.1.1

DATE

• 定义：DATE 类型用于存储日期，不包含时间部分。
• 格式：YYYY-MM-DD（例如：2024-10-24）
• 存储范围：1000-01-01 到 9999-12-31

5.1.2

TIME

• 定义：TIME 类型用于存储时间值，不包含日期部分。可以存储正或负的时间值。
• 格式：HH:MM:SS（例如：13:45:30）
• 存储范围：-838:59:59 到 838:59:59

5.1.3

DATETIME

• 定义：DATETIME 类型用于存储日期和时间的组合。
• 格式：YYYY-MM-DD HH:MM:SS（例如：2024-10-24 13:45:30）
• 存储范围：1000-01-01 00:00:00 到 9999-12-31 23:59:59
• 精度：可以支持微秒精度（DATETIME(fsp)，其中 fsp 表示小数秒的精度，范围从 0 到 6）。

5.1.4

TIMESTAMP

• 定义：TIMESTAMP 类型用于存储时间戳，表示从 1970-01-01 00:00:01 UTC 开始的秒数。
• 格式：与 DATETIME 相同，YYYY-MM-DD HH:MM:SS
• 存储范围：1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
• 自动更新：通常用来记录数据的创建或更新时间。可以通过 DEFAULT CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP 自动更新。

5.1.5

YEAR

• 定义：YEAR 类型用于存储年份值。
• 格式：YYYY 或者 YY（例如：2024 或 24）
• 存储范围：1901 到 2155（四位），或者 70 到 99（两位表示 1970-1999），00 到 69（表示 2000-2069）

🎫5.2 表的操作示例

5.2.1 创建包含日期和时间字段的表

CREATETABLE events (
    event_id INTAUTO_INCREMENTPRIMARYKEY,
    event_name VARCHAR(100),-- 事件名称
    event_date DATE,-- 事件日期，只存储日期部分
    event_start_time TIME,-- 事件开始时间，只存储时间部分
    event_end_time TIME,-- 事件结束时间
    created_at DATETIMEDEFAULTCURRENT_TIMESTAMP,-- 创建时间，存储日期和时间
    updated_at TIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP-- 更新时间，自动更新);

5.2.2 插入数据

INSERTINTO events (event_name, event_date, event_start_time, event_end_time)VALUES('Company Meeting','2024-11-01','09:00:00','11:00:00');

5.2.3 查询数据

SELECT*FROM events;

5.2.4 更新数据

UPDATE events 
SET event_name ='Annual Company Meeting', 
    event_end_time ='12:00:00'WHERE event_id =1;

🎫5.3 不同日期和时间类型的区别与选择

5.3.1 **

DATETIME

与

TIMESTAMP

的区别**

• 时区处理：- TIMESTAMP 与 UTC 时间相关联，MySQL 会根据服务器的时区自动进行转换。存储和检索 TIMESTAMP 时，MySQL 会考虑时区的差异。- DATETIME 则不进行时区转换，存储时按原格式存储，检索时也是原格式。示例：CREATETABLE test_timestamps ( ts TIMESTAMPDEFAULTCURRENT_TIMESTAMP, dt DATETIMEDEFAULTCURRENT_TIMESTAMP);INSERTINTO test_timestamps ()VALUES();SELECT*FROM test_timestamps;在不同的时区下，TIMESTAMP 的值会有所不同，而 DATETIME 不会变化。

5.3.2 **

DATE

与

DATETIME

的选择**

• 如果只需要存储日期（例如生日、纪念日等），使用 DATE。
• 如果需要同时存储日期和时间（例如事件发生的精确时间），使用 DATETIME 或 TIMESTAMP。

5.3.3 **

YEAR

的使用**

• YEAR 类型适用于只存储年份的场景，例如汽车生产年份、毕业年份等。示例：CREATETABLE car_models ( model_name VARCHAR(50), production_year YEAR);

🎫5.4 日期和时间的操作

5.4.1 获取当前日期和时间

MySQL 提供了多种函数来获取当前日期和时间：

• NOW()：返回当前日期和时间（DATETIME 类型）。
• CURDATE()：返回当前日期（DATE 类型）。
• CURTIME()：返回当前时间（TIME 类型）。
• CURRENT_TIMESTAMP()：返回当前时间戳（TIMESTAMP 类型）。

SELECTNOW(), CURDATE(), CURTIME(),CURRENT_TIMESTAMP();

5.4.2 日期和时间的格式化

MySQL 提供了

DATE_FORMAT()

函数，用于自定义日期和时间的显示格式。

示例：

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s')AS formatted_datetime;

这将返回当前日期时间的格式化版本，如

2024-10-24 13:45:30

。

5.4.3 日期加减操作

可以使用

DATE_ADD()

和

DATE_SUB()

函数对日期进行加减操作。

示例：

-- 增加 7 天SELECT DATE_ADD('2024-10-24',INTERVAL7DAY)AS new_date;-- 减少 1 个月SELECT DATE_SUB('2024-10-24',INTERVAL1MONTH)AS new_date;

5.4.4 时间差计算

可以使用

TIMEDIFF()

或

DATEDIFF()

计算时间或日期之间的差异。

• TIMEDIFF()：用于计算两个时间之间的差值。
• DATEDIFF()：用于计算两个日期之间的差值。

示例：

-- 计算两个时间的差异SELECT TIMEDIFF('13:45:30','10:00:00')AS time_difference;-- 计算两个日期的差异SELECT DATEDIFF('2024-10-24','2024-10-01')AS date_difference;

总结

• **DATE**：用于存储日期，不包括时间。适合存储生日、事件日期等。
• **TIME**：用于存储时间，不包括日期。适合存储每日的特定时间，如工作时间。
• **DATETIME**：用于存储日期和时间的组合，不考虑时区。适合存储事件的精确发生时间。
• **TIMESTAMP**：用于存储时间戳，自动处理时区。适合记录记录的创建或更新时间。
• **YEAR**：用于存储年份，适合存储年份相关的简单数据。

🎀六、枚举和集合类型

在 MySQL 中，**枚举（

ENUM

）** 和 **集合（

SET

）** 是两种特殊的字符串类型，分别用于表示单个或多个预定义值的选择。它们的使用场景和功能各有不同，适用于有限选项的数据存储。下面将详细介绍它们的定义、使用方法以及它们之间的区别。

🎫6.1

ENUM

类型

6.1.1 定义：

ENUM

类型用于存储一个预定义的值列表中的单个值。你必须在插入记录时从这个列表中选择一个值，无法插入列表之外的值。

6.1.2 特点：

• ENUM 可以让开发者定义一组有限的合法值，插入数据时只能选择其中之一。
• ENUM 的存储方式是将每个值作为整数索引，存储效率高。
• 可以有最多 65,535 个枚举值。

6.1.3 使用场景：

适合用于只有一个状态或分类的字段，比如用户的性别、订单状态、商品的颜色等。

6.1.4 示例：

CREATETABLE orders (
    order_id INTAUTO_INCREMENTPRIMARYKEY,statusENUM('pending','shipped','delivered','cancelled')NOTNULL-- 订单状态);

6.1.5 插入数据：

INSERTINTO orders (status)VALUES('pending'),('shipped');

6.1.6 查询数据：

SELECT*FROM orders WHEREstatus='shipped';

6.1.7 注意：

• 如果插入的值不在定义的枚举列表中，MySQL 会插入空字符串 '' 并生成一个警告。示例：INSERTINTO orders (status)VALUES('unknown');-- 将产生警告，插入空字符串
• 可以使用 FIND_IN_SET() 函数来查找枚举值的位置：SELECT FIND_IN_SET('shipped','pending,shipped,delivered,cancelled');

🎫6.2

SET

类型

6.2.1 定义：

SET

类型用于存储从预定义值列表中选择一个或多个值的组合。每条记录可以包含 0 到多个值。

6.2.2 特点：

• SET 可以存储多个选项的组合，因此非常适合多选场景。
• 每个 SET 字段最多可以定义 64 个不同的值。
• 存储时每个选项被编码为一个位（bit），因此在空间利用上也很高效。

6.2.3 使用场景：

适合用于多个属性的组合，比如用户的兴趣、商品的标签、权限设置等。

6.2.4 示例：

CREATETABLE user_preferences (
    user_id INTAUTO_INCREMENTPRIMARYKEY,
    interests SET('reading','music','sports','movies','travel')-- 用户的兴趣);

6.2.5 插入数据：

INSERTINTO user_preferences (interests)VALUES('reading,music'),('sports,travel');

6.2.6 查询数据：

SELECT*FROM user_preferences WHERE FIND_IN_SET('music', interests);

6.2.7 注意：

• 插入的值可以是多个选项的组合，用逗号分隔。示例：INSERTINTO user_preferences (interests)VALUES('reading,music,sports');
• 如果插入的值不在定义的 SET 列表中，MySQL 会忽略该值并插入合法的部分。

🎫6.3

ENUM

和

SET

的区别
特性

ENUM

SET

存储的值数量只能选择一个值可以选择 0 个或多个值定义的最大值数最多 65,535 个不同值最多 64 个不同值存储效率整数索引存储，空间使用少使用位存储，多个值组合时效率高适用场景状态、分类、单项选择（如订单状态、性别）多选场景（如兴趣、标签、权限）索引和排序

ENUM

类型的值按索引存储，查询和排序较快

SET

查询时需用

FIND_IN_SET()

函数插入非法值插入非法值会插入空字符串并生成警告插入非法值会忽略它并生成警告

🎫6.4 示例：混合使用

ENUM

和

SET

我们可以在一个表中同时使用

ENUM

和

SET

来存储不同类型的数据，比如存储用户的状态和兴趣：

CREATETABLE users (
    user_id INTAUTO_INCREMENTPRIMARYKEY,
    username VARCHAR(50),statusENUM('active','inactive','banned'),-- 用户状态
    hobbies SET('reading','sports','music','movies')-- 用户兴趣);

6.4.1 插入数据：

INSERTINTO users (username,status, hobbies)VALUES('Alice','active','reading,music'),('Bob','inactive','sports,movies');

6.4.2 查询用户状态为

active

且兴趣中包含

music

的用户：

SELECT*FROM users WHEREstatus='active'AND FIND_IN_SET('music', hobbies);

🎫6.5 注意事项

1. ENUM 和 SET 字段的更新和维护：- 一旦表中定义了 ENUM 或 SET 字段，修改其值列表（例如，添加新的枚举值）会比较麻烦，可能需要使用 ALTER TABLE 修改列定义。ALTERTABLE orders MODIFYCOLUMNstatusENUM('pending','shipped','delivered','cancelled','returned');
2. 索引性能：- ENUM 类型因为其底层使用整数索引，所以在查询和排序时的性能要比 SET 好一些。如果需要对该列进行大量的排序操作，可以优先选择 ENUM。
3. 组合查询：- 使用 SET 类型时，如果需要查找包含多个选项的记录，可以结合 FIND_IN_SET() 函数。对于复杂的组合查询，SET 可能不如单独的布尔型字段灵活。

总结

• **ENUM**：用于从一组预定义的值中选择一个值，适合表示状态、分类或单项选择。
• **SET**：用于从一组预定义的值中选择一个或多个值的组合，适合表示兴趣、标签或多项选择。

通过合理使用

ENUM

和

SET

类型，可以帮助我们确保数据的完整性，限制字段值的范围，并且在某些情况下提升存储效率。

---

结语

数据类型的选择不仅影响数据库的存储效率，还可能对应用程序的性能产生直接影响。通过深入理解 MySQL 的数据类型，并根据实际需求进行优化，可以有效提高数据库的运行效率和稳定性。希望本篇文章能够帮助读者在数据库设计中做出更好的决策，使得 MySQL 数据库在项目中更好地发挥作用。

今天的分享到这里就结束啦！如果觉得文章还不错的话，可以三连支持一下，17的主页还有很多有趣的文章，欢迎小伙伴们前去点评，您的支持就是17前进的动力！