数据库--字段计划规范

目录
一.整数类型
1. 整数类型及其存储空间和取值范围
2. 整数类型选择原则
3. 常见场景的计划发起
3.1 避免的误区
3.2 字段计划示例

二.字符串类型
1.字符串类型的长度限制选择
1.1. 常用字符串类型
1.2. 常见场景计划发起
1.3. char和varchar的选择
二、硬盘存储和内存
2.1 char(10) 和char(6)的区别
2.2 varchar(100)和varchar(50)都存储50个字符，有什么区别 ?
2.2.1 字段定义的最大长度不同
2.2.2 数据库约束和检查本钱
2.2.3 内存和临时存储的利用
三、知识扩展
3.1 varchar存储结构
3.2 VARCHAR 存储示例
3.3 特点与注意事项
三、浮点数和定点数
3.1 浮点数（FLOAT 和 DOUBLE）
特点
实用场景
计划发起
3.2 定点数（DECIMAL）
特点
实用场景
计划发起 
3. 3 浮点数和定点数的选择
四、日期和时间类型
4.1 日期和时间类型及其特点
4.2 日期和时间类型的选择
4.3 日期和时间字段计划发起
常见字段计划
小数秒精度
4.4 注意事项
4.5 示例
五. 二进制数据类型
5.1 二进制数据类型概述
5.2 二进制数据类型选择
5.3 计划发起和最佳实践
5.3.1 利用合适的数据类型
5.3.2 思量存储方式
5.3.3 查询性能优化
5.3.4 示例
六. 枚举和集合
6.1 ENUM 类型
6.2 SET 类型
6.3 ENUM 和 SET 类型的比力
6.4 计划中的思量事项 

---

在MySQL中，为字段选择合适的类型黑白常重要的，因为它直接影响到数据的存储效率、查询性能以及数据完整性。以下是一些关于MySQL字段类型选择的规范和最佳实践：
一.整数类型

1. 整数类型及其存储空间和取值范围

• 根据数值的范围选择最小的合适类型，如 TINYINT, SMALLINT, MEDIUMINT, INT, 或 BIGINT。
  
• 假如数值永久不会是负数，利用 UNSIGNED 属性。
  
• 避免不必要的零添补，除非有特定的显示需求。
  

数据类型	存储空间	无符号范围	有符号范围
TINYINT	1 字节	0 到 255	-128 到 127
SMALLINT	2 字节	0 到 65535	-32768 到 32767
MEDIUMINT	3 字节	0 到 16777215	-8388608 到 8388607
INT	4 字节	0 到 4294967295	-2147483648 到 2147483647
BIGINT	8 字节	0 到 18446744073709551615	-9223372036854775808 到 9223372036854775807

2. 整数类型选择原则

• 根据数据范围选择：选择整数类型时，重要依据该字段可能存储的数据范围。
  

• 
  
  
  
  • 小范围的标识：例如，假如一个字段只需要表示状态（如 0 和 1），那么 TINYINT 就充足了。
    
  • 中等范围的计数值：例如，计数、积分等一般利用 INT。
    
  • 大范围的唯一标识：例如，假如要表示超大范围的用户 ID，可以利用 BIGINT。
    
  
  

• 利用无符号类型：当明白知道一个字段只会存储非负整数（如年岁、数目等）时，利用无符号类型（UNSIGNED），可以扩大数值的取值范围。
  
• 根据表的增长速率来决定 ID 字段：
  

• 
  
  
  
  • 对于自增 ID 字段，应预估表的增长速率和数据总量。假如表每年新增不到 100 万行数据，用 INT 充足。假如表增长速率非常快（每年几千万行），并预计要长期生存，可以思量用 BIGINT。
    
  • 常见的自增 ID 计划利用 INT UNSIGNED，这样可表示的正整数范围从 0 到 4,294,967,295，这对大多数应用已充足。
    
  
  

3. 常见场景的计划发起

• 状态标志：利用 TINYINT（1 字节）。例如，0 表示未处理，1 表示已处理等。
  
• 评分、等级：通常 TINYINT 或 SMALLINT 充足，除非需要极大范围。
  
• 数目、计数：视范围选择 INT（4 字节），对于较大的计数值（如累计金额等），可选择 BIGINT。
  
• 自增主键：
  

• 小型体系：INT UNSIGNED 一般够用。
  
• 大型体系：假如体系预期有极大数据量，用 BIGINT UNSIGNED。
  

3.1 避免的误区

• 不要盲目利用 BIGINT：BIGINT 会占用更多的存储空间和内存，通常只有在数据量非常大、需要极大范围时才选用。
  
• 不要过分分配存储空间：避免为了"保险"选择大类型，比如计数用 BIGINT。在大表中，利用小的数据类型可以显著节流空间和加快查询速率。
  

3.2 字段计划示例

• 用户ID：INT UNSIGNED，在超大规模体系中用 BIGINT UNSIGNED。
  
• 性别：TINYINT，值为 0 表示男、1 表示女。
  
• 年岁：TINYINT UNSIGNED，范围 0-255，充足。
  
• 积分：SMALLINT 或 INT，根据积分范围设定。
  

合理选择整数类型可以有效优化数据库的空间利用和性能。


二.字符串类型

1.字符串类型的长度限制选择

   小知识：
  varchar(n)中的数字在5.0以下的版本表示字节，在5.0以上表示字符
  例子：varchar(100)，在5.0以下表示存储100个字节，5.0以上表示存储100个字符；所以5.0以上，在定义类型的时，盼望不超过多少字，可以直接写入在括号中；
  
字符串类型的长度应根据实际的需求来设置：
1.1. 常用字符串类型

   

• CHAR(n)：用于固定长度的字符串（如国家代码 ISO 代码 CHAR(2)）。char的长处是存储空间固定，所以存储读取速率快。缺点是空间冗余，对于数据量大的表，非固定长度属性利用char字段，空间浪费。
  
• VARCHAR(n)：用于可变长度的字符串，是最常用的字符串类型。n 表示最大字符数，在 UTF-8 编码下，每字符可占用 1-3 字节。合理设置 n 的大小可以节流存储空间，提高查询效率。存储的空间根据存储的内容变化，空间长度为L+size，存储内容长度+描述存储内容长度信息，长处就是空间节约，缺点就是读取和存储时候，需要读取信息盘算下标，才能获取完整内容。
  
• TEXT：用于较长的文本，适合大段描述信息。TEXT 不需要指定长度，但因为其存储方式会影响查询性能，发起仅在字段长度不确定的环境下利用。
  

  
1.2. 常见场景计划发起

• 姓名：VARCHAR(50)，50 个字符足以满足大多数语言的姓名。
  
• 电子邮件：VARCHAR(100)，邮箱一般不会超过 100 个字符。
  
• 电话号码：VARCHAR(15)，因为电话号码前通常会包含国家代码，利用 VARCHAR(15) 适合大多数环境。
  
• 地点：VARCHAR(255)，假如较长可以利用 TEXT，但发起避免利用太长的 VARCHAR。
  

1.3. char和varchar的选择

   

• 思量其长度是否相近或者固定，假如某个字段其长度固然比力长，但是其长度总是近似的，如一般在90个到100个字符之间，甚至是雷同的长度。此时比力适合采用CHAR字符类型。比力典范的应用就是MD5哈希值。当利用MD5哈希值来存储用户密码时，就非常利用采用CHAR字符类型。因为其长度是雷同的。另外，像用来存储用户的身份证号码等等，一般也发起利用CHAR类型的数据。
  
• 对于较长的且不固定长度的属性，用varchar
  
• 字符串的长度 n 不宜过大，通常在实际最大长度的基础上略微加大(10%)即可。
  
• 避免在不必要时利用过大的 VARCHAR 长度（如 VARCHAR(255)），以节流存储空间和提高查询效率。
  

  
二、硬盘存储和内存

2.1 char(10) 和char(6)的区别

   首先char类型最大存储长度是0-255字符，但是很多时候我们存储一个数据是用不到那么大的空间的，所以通过括号里面的数字来限制空间大小，比如我们确定一个字段最多放10个字符，就可以利用char(10)来限制。
  那么char(6)和char（10），每个信息占用的空间分别为10个字符和6个字符，不够限定的大小就是在右侧加空格补齐，会造成空间浪费。
  
2.2 varchar(100)和varchar(50)都存储50个字符，有什么区别 ?

   

• 硬盘上的存储空间固然都是根据实际字符长度来分配存储空间的
  
• 对于内存来说，则不是。当时利用固定大小的内存块(限定的最大值)来生存值。
  

  
在 MySQL 中，VARCHAR(100) 和 VARCHAR(50) 都存储 50 个字符时，在存储内容方面没有区别，因为它们都只存储实际数据的字节数和长度信息。然而，它们在计划和实际利用中存在以下几点差别：
2.2.1 字段定义的最大长度不同

• VARCHAR(50)：答应存储最多 50 个字符。
  
• VARCHAR(100)：答应存储最多 100 个字符。
  

固然在存储 50 个字符时，VARCHAR(100) 和 VARCHAR(50) 实际消耗的空间是雷同的，但 VARCHAR(100) 在定义上答应更长的字符串。因此，假如数据超过 50 个字符，VARCHAR(50) 会报错或截断，而 VARCHAR(100) 能存储完整的数据。
2.2.2 数据库约束和检查本钱

较大的 VARCHAR 字段会让数据库在插入和更新数据时耗费更多时间检查输入数据的长度。例如：

• 当插入数据时，VARCHAR(100) 字段答应更长的输入，需要数据库多花点时间来检查是否超出上限。
  
• 对于 VARCHAR(50)，数据库只需检查是否超过 50 个字符，约束较紧，因此检查本钱稍低。
  

2.2.3 内存和临时存储的利用

在某些环境下（如排序和临时表存储时），数据库可能会根据 VARCHAR 定义的最大长度分配内存或磁盘空间。更大的 VARCHAR 字段定义（如 VARCHAR(100)）可能会导致更多内存分配和临时存储需求。

三、知识扩展

3.1 varchar存储结构

VARCHAR 是 MySQL 中用于存储可变长度字符串的数据类型，具有机动的存储结构。VARCHAR 类型的字段在存储时会生存以下内容：
   

• 实际字符串数据：VARCHAR 字段存储实际的字符内容。
  
• 长度信息：为记载字符串的实际长度，VARCHAR 会额外存储长度信息（1 到 2 字节），用于标明字符串数据的实际字节数。
  

  VARCHAR 的存储结构包含两部门：

• 长度字节：用于存储字符串的实际长度：
  
• 假如字符串长度 ≤ 255 字节，利用 1 个字节记载长度信息。
  
  
  
  • 假如字符串长度 > 255 字节，利用 2 个字节记载长度信息。
    
  
  
• 实际字符串数据：存储实际的字符串内容，按字符的编码字节数存储（在 utf8mb3 编码下，每个字符最多占 3 字节）。
  

3.2 VARCHAR 存储示例

假设 VARCHAR(10) 字段中存储字符串 "hello"：

• 在 utf8mb3 编码下，"hello" 由 5 个字符构成，每字符 1 字节，占 5 字节。
  
• 因为字符串长度 ≤ 255 字节，用 1 个字节存储长度信息，记载长度为 5。
  
• 统共存储空间为 1 + 5 = 6 字节。
  

3.3 特点与注意事项

• 节流存储空间：VARCHAR 仅存储实际字符内容的字节数，未用满的长度不会占用额外空间（相较于固定长度的 CHAR）。
  
• 字符串长度限制：VARCHAR 的最大长度取决于字符集和 MySQL 存储引擎的限制。
  

• 在 utf8mb3 编码下，VARCHAR 字段的最大字符数是 21,844 个字符（65,535 字节的最大行存储限制减去长度字节等元数据）。
  

三、浮点数和定点数

   

• 浮点数:对于需要小数点的数值，假如精度要求不高，可以利用 FLOAT 或 DOUBLE。
  
• 定点数: 对于需要高精度盘算(无偏差)的场景（如金融应用），应利用 DECIMAL 或 NUMERIC 类型，并明白指定精度和小数位数。
  

  
3.1 浮点数（FLOAT 和 DOUBLE）

浮点数用于表示具有小数部门的数值，通常用于不需要高精度的盘算（如科学盘算、统计数据）。

• FLOAT：占用 4 字节，精度约为 7 位有效数字。适合存储精度要求不高的数值数据。
  
• DOUBLE：占用 8 字节，精度约为 15-16 位有效数字。适合存储较大数值，且需要较高精度的数据。
  

特点

• 近似存储：浮点数利用二进制近似表示，有可能出现精度偏差（如 0.1 + 0.2 != 0.3）。
  
• 较小存储空间：在需要大量数据存储时，FLOAT 和 DOUBLE 能够节流存储空间。
  
• 盘算速率较快：在盘算时，浮点数的运算速率通常比定点数快。
  

实用场景

• 科学、统计等领域，答应肯定程度偏差的场景。
  
• 温度、海拔、经纬度等不需要精确到最后一位的小数值。
  

计划发起

• 利用 FLOAT 时，将数据控制在 7 位有效数字以内。
  
• 利用 DOUBLE 时，将数据控制在 15 位有效数字以内，以包管数据精度的合理性。
  

3.2 定点数（DECIMAL）

定点数重要用于存储需要精确的小数数据，尤其适实用于财政数据（如金额、汇率）。

• DECIMAL(M, D)：M 表示总位数，D 表示小数位数。例如，DECIMAL(10, 2) 表示最多 10 位数，其中 2 位为小数，剩下 8 位为整数。
  
• 精确存储：定点数不会出现二进制浮点数的偏差，适合需要精确盘算的应用场景。
  

特点

• 精确性：定点数以十进制存储，不会出现浮点数的精度丢失问题。
  
• 存储空间：定点数的存储空间依据 M 的大小而定，每 9 位数字占用 4 字节。对于 DECIMAL(10, 2)，需要 5 字节存储。
  
• 性能相对较慢：相比浮点数，定点数的存储和盘算性能稍慢。
  

实用场景

• 财政、银行体系的金额盘算（如商品价格、买卖业务金额等）。
  
• 汇率、利率等对精度要求高的应用。
  

计划发起

• 合理设置 M 和 D：M 不宜过大，应以实际需要的精度来设置。例如，金额可以用 DECIMAL(10, 2)，表示最多 10 位数，2 位小数，适合表示百万级的货币金额。
  
• 避免不必要的高精度：避免利用超出需求的高精度，以节流存储空间和提高性能。
  

3. 3 浮点数和定点数的选择

• 选择浮点数（FLOAT、DOUBLE）：
  

• 需要大量数据存储、对精度要求不严格的数据。科学或统计数据、物理测量数据、地理数据等。
  
• 选择定点数（DECIMAL）：
  
• 财政数据或需要精确表示的小数值（如金额、汇率等）。数据的存储和盘算不答应出现精度偏差。
  

四、日期和时间类型

4.1 日期和时间类型及其特点

类型	占用字节	格式	范围	实用场景
DATE	3	YYYY-MM-DD	1000-01-01 到 9999-12-31	仅需存储日期，无需时间的环境
TIME	3	HH:MM:SS	-838:59:59 到 838:59:59	仅需存储时间（不含日期）的环境
YEAR	1	YYYY	1901 到 2155 ，或 0000	存储年份，如记载年份字段
DATETIME	5 (MySQL 5.6+)	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 到 9999-12-31 23:59:59	不受时区影响的日期和时间
TIMESTAMP	4	YYYY-MM-DD HH:MM:SS	1970-01-01 00:00:01 到 2038-01-19 03:14:07	受时区影响的时间戳，记载变乱时间点
DATETIME(f)	5 + f	YYYY-MM-DD HH:MM:SS[.fraction]	同 DATETIME ，支持小数秒精度	带微秒精度的日期和时间
TIMESTAMP(f)	4 + f	YYYY-MM-DD HH:MM:SS[.fraction]	同 TIMESTAMP ，支持小数秒精度	带微秒精度的时间戳

4.2 日期和时间类型的选择

• DATE：用于仅记载日期而无需时间的场景，例如生日、入职日期等。节流空间且查询效率较高。
  
• TIME：用于存储时间（不包括日期），适合记载持续时间、业务时间等。
  
• YEAR：用于仅需存储年份的场景，如记载年份、年度报表等。较节流空间。
  
• DATETIME：适合不受时区影响的日期和时间数据，通常用于需要记载日期和时间但不需要跨时区的场景（如订单时间）。
  
• TIMESTAMP：用于存储与时区相干的变乱时间点，通常适合表示全局变乱的时间，如记载体系日志时间。
  

• 
  
  
  
  • 主动初始化和更新：TIMESTAMP 支持主动初始化和更新时间，实用于 created_at 和 updated_at 等字段。
    
  
  

• 小数秒精度（DATETIME(f) 和 TIMESTAMP(f)）：
  

• 
  
  
  
  • 假如应用需要亚秒级精度（如买卖业务数据），可以利用小数秒的 DATETIME(f) 或 TIMESTAMP(f)，其中 f 为小数秒的精度（0 到 6）。
    
  
  

4.3 日期和时间字段计划发起

常见字段计划

• 创建时间（created_at） 和 更新时间（updated_at）：
  

• 
  
  
  
  • 利用 TIMESTAMP 类型，且利用其主动更新特性（DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP）。
    
  • 若不关心时区，可以用 DATETIME 来避免时区转换的复杂性。
    
  
  

• 变乱发生时间：
  

• 
  
  
  
  • 利用 TIMESTAMP 可以确保跨时区时记载时间的一致性，实用于记载精确的全局变乱时间。
    
  • 对本地应用（不涉实时区转换）的变乱时间，可以用 DATETIME。
    
  
  

• 仅日期数据：
  

• 
  
  
  
  • 用 DATE 节流存储空间，且查询和存储都更简单。
    
  
  

• 仅时间数据：
  

• 
  
  
  
  • 用 TIME 存储时间数据（如变乱持续时间、工时等）。
    
  
  

小数秒精度

假如需要微秒精度，选择带小数秒的 DATETIME(f) 或 TIMESTAMP(f)。例如：

• DATETIME(3)：精度到毫秒，适合买卖业务时间等需亚秒级精度的应用。
  

4.4 注意事项

• 避免利用不必要的高精度：例如，对于大多数业务应用，秒级精度即可，不要设置过高的精度以免浪费存储空间。
  
• 时区处理：TIMESTAMP 会主动处理时区转换，实用于跨时区应用，而 DATETIME 则不受时区影响。
  
• 存储空间优化：合理选择类型，避免利用不必要的高占用类型（如不涉实时间的环境下不利用 DATETIME）。
  

4.5 示例

假设一个订单表 orders 需要记载创建时间、发货日期和订单处理时长：

1. CREATE TABLE orders (
2.     order_id INT AUTO_INCREMENT PRIMARY KEY,
3.     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,      -- 记录订单创建时间 自动初始化
4.     shipped_date DATE,                                   -- 记录订单发货日期
5.     processing_duration TIME                             -- 记录订单处理时长
6. );

复制代码

此计划在包管时区一致性、节流空间和便于查询的基础上选择了合适的日期和时间类型。


五. 二进制数据类型

在 MySQL 中，二进制数据类型用于存储图片、音频、视频、文件等非文本数据，或者需要精确存储的字符串（如加密内容、哈希值）。MySQL 提供了多种实用于二进制数据的字段类型，包括 BINARY、VARBINARY 和 BLOB 系列类型。

• 小数据选用 BINARY 或 VARBINARY，如哈希值或定长二进制数据。
  
• 较大数据选用 BLOB 系列类型，根据实际数据大小选择合适类型。
  
• 大文件慎重存储，必要时将大文件存储在文件体系，并将路径存储在数据库中。
  

5.1 二进制数据类型概述

类型	最大存储空间	特点
BINARY(n)	n 字节	固定长度的二进制字符串，长度不敷会添补 0x00 。
VARBINARY(n)	n 字节	可变长度的二进制字符串，根据实际数据长度存储，节流空间。
TINYBLOB	255 字节	适合小型二进制数据。
BLOB	65,535 字节（约 64 KB）	中型二进制数据，适合较小的图片或文档。
MEDIUMBLOB	16,777,215 字节（约 16 MB）	适合大型文件，如音频和大图片。
LONGBLOB	4,294,967,295 字节（约 4 GB）	超大文件，如视频、完整文档等。

5.2 二进制数据类型选择

在计划数据库时，根据二进制数据的大小和用途选择合适的数据类型：

• BINARY(n) 和 VARBINARY(n)：适合存储较短且固定大小或可变大小的二进制数据（如加密密钥、哈希值等）。
  

• 
  
  
  
  • BINARY(n) 是固定长度的，假如数据长度小于 n，体系会添补 0x00，占满 n 字节。
    
  • VARBINARY(n) 是可变长度的，根据数据实际长度存储，较为节流空间。
    
  
  

• BLOB 系列类型：适合存储大数据的二进制数据（如图片、音频、视频文件等）。
  

• 
  
  
  
  • TINYBLOB：适合小文件或小数据片断。
    
  • BLOB：常用于存储中等大小的文件，如缩略图、文档附件等。
    
  • MEDIUMBLOB：适合较大文件，如音乐、高清图片。
    
  • LONGBLOB：适合超大文件，如高清视频、超大文档等。
    
  
  

5.3 计划发起和最佳实践

5.3.1 利用合适的数据类型

• 定长数据（如哈希值）：利用 BINARY 或 VARBINARY，如 MD5 哈希可利用 BINARY(16)，SHA-256 哈希可利用 BINARY(32)。
  
• 小文件或图片：利用 BLOB 或 TINYBLOB。
  
• 较大文件（如音频、图片）：利用 MEDIUMBLOB 或 LONGBLOB。
  

5.3.2 思量存储方式

• 大文件存储：假如文件特别大（如视频），可思量将文件存储在文件体系或对象存储（如 Amazon S3），数据库中仅存储文件路径或引用。
  
• 空间效率：BLOB 数据存储在单独的表空间中，因此大量 BLOB 数据字段会增长数据库的负载。定期维护和备份对性能尤为重要。
  

5.3.3 查询性能优化

• 避免频繁查询 BLOB 字段：二进制数据查询开销较大，频繁读取 BLOB 字段会影响性能。可利用缩略图或引用的方式，镌汰直接读取大数据的需求。
  
• 按需加载：在查询中可将二进制数据字段设置为懒加载，仅在需要时加载，提高查询性能。
  

5.3.4 示例

假设一个表 documents 存储不同类型的文件（如缩略图、文件内容等）：

1. CREATE TABLE documents (
2.     doc_id INT AUTO_INCREMENT PRIMARY KEY,
3.     title VARCHAR(100),
4.     thumbnail TINYBLOB,                    -- 存储小型的图片缩略图
5.     content MEDIUMBLOB,                    -- 存储较大的文件内容
6.     checksum BINARY(32)                    -- 存储文件的 SHA-256 校验和
7. );

复制代码

在这个计划中：

• thumbnail 利用 TINYBLOB，适合小型图片。
  
• content 利用 MEDIUMBLOB，适合中等大小的文件内容。
  
• checksum 利用 BINARY(32) 存储文件校验和。
  

六. 枚举和集合

• ENUM 类型：实用于值的选择是互斥的（如性别、状态），且只有一个选项。
  
• SET 类型：实用于字段可以选择多个值的环境（如用户权限、标签、分类等），但需要注意查询性能和数据的复杂性。
  

6.1 ENUM 类型

ENUM 类型用于表示一个字段只能从一组预定义的值中选择一个。每个 ENUM 类型的字段存储的值是一个整数，这个整数代表在定义值集合中的位置，尽管存储的实际上是该位置对应的文本值。
语法：

1. ENUM('value1', 'value2', 'value3', ...)

复制代码

特点：

• 存储：ENUM 类型在内部利用整数表示，每个选项都会被赋予一个从 1 开始的数字，这使得存储空间非常高效。
  
• 最大值：最多支持 65,535 个选项。
  
• 默认值：可以指定默认值，也可以不指定，默以为 NULL。
  
• 顺序值：ENUM 内部存储的是选项的位置（从 1 开始），而不是字符串，因此可以根据位置值来举行排序。
  

利用场景：

• 表示一个字段只能选定一个值的场景。例如，状态字段、性别字段、用户角色等。
  

计划发起：

• 可读性和易维护性：尽量利用具有可读性的名称，并确保值不会过多。
  
• 避免过多枚举值：ENUM 值不宜过多（一般不要超过 10-20 个），超过 20 个时发起思量利用其他表存储（例如通过外键引用一个常量表）。
  
• 字段顺序影响存储：固然利用 ENUM 类型时，存储的是一个整数，但它的顺序值是根据定义顺序来分配的。注意修改顺序时可能会导致数据含义改变。
  

示例：

1. CREATE TABLE employees (
2.     employee_id INT AUTO_INCREMENT PRIMARY KEY,
3.     name VARCHAR(100),
4.     gender ENUM('Male', 'Female', 'Other') NOT NULL,      -- 性别
5.     status ENUM('Active', 'Inactive', 'On Leave') DEFAULT 'Active'  -- 状态
6. );

复制代码

在此示例中：

• gender 字段只能取 'Male'、'Female' 或 'Other'。
  
• status 字段默认值为 'Active'，而且只能取 'Active'、'Inactive' 或 'On Leave'。
  

6.2 SET 类型

SET 类型与 ENUM 类型类似，不同之处在于 SET 可以存储多个值，而 ENUM 只能存储一个值。SET 类型实用于字段值可以选择多个项的场景。
语法：

1. SET('value1', 'value2', 'value3', ...)

复制代码

特点：

• 存储：SET 类型内部存储为一个整数，其中每一位对应一个集合的选项。假如多个选项被选择，则每一位的值都为 1，表示该选项被选中。
  
• 最大值：最多支持 64 个选项（固然通常不发起这么多）。
  
• 排序：SET 值的顺序是有意义的，因此它会按照集合定义的顺序存储。
  

利用场景：

• 表示字段可以存储多个值的场景。例如，用户拥有多个角色权限，商品的多种标签等。
  

计划发起：

• 限制选择数目：固然 SET 答应多个值同时存储，但为了简化数据管理和提高查询效率，不发起选择过多的选项。
  
• 查询性能问题：由于 SET 类型的数据存储方式是按位存储（每个选项占一位），查询时可能需要通过位运算来筛选数据，处理时稍显复杂。
  
• 避免重复的值：SET 类型确保每个选项最多只能选中一次，因此不支持重复选项。
  

示例：

1. CREATE TABLE products (
2.     product_id INT AUTO_INCREMENT PRIMARY KEY,
3.     name VARCHAR(100),
4.     tags SET('Electronics', 'Clothing', 'Furniture', 'Toys', 'Sports')      -- 商品标签
5. );

复制代码

在此示例中：

• tags 字段存储多个选项（如 'Electronics'、'Clothing' 等）。
  
• 一件商品可以有多个标签，如 ('Electronics', 'Toys')，表示它既是电子产品也是玩具。
  

6.3 ENUM 和 SET 类型的比力

特性	ENUM	SET
存储多个值	不支持，仅能选择一个值	支持，最多 64 个选项，可以选择多个值
存储方式	存储为整数，表示选中的位置	存储为整数，按位表示选中的多个值
利用场景	用于选择单一值，如性别、状态、等级等	用于选择多个值，如标签、角色、权限等
查询复杂性	简单，直接通过值查询	需要利用位运算来查询多个选项
支持的最大项数	65,535 个选项	64 个选项
默认值	可指定一个默认值，通常为某个有效值	可指定默认值，但不常用
空间效率	较高，存储为一个整数	由于位操作存储，效率较高，实用于小集合

6.4 计划中的思量事项

• 数据完整性：ENUM 和 SET 都确保只能存储预定义的值，因此它们在数据验证方面非常有效，能够镌汰无效数据的插入。
  
• 机动性和扩展性：随着业务需求的变化，增长或修改枚举或集合的选项可能会比力麻烦，因此在计划时要思量到未来的扩展性。对于较大的选项集，发起利用关联表（如通过外键引用一个选项表）来代替 ENUM 或 SET。
  
• 查询和更新性能：对于非常大的选项集（如 SET 类型有 64 个选项时），可能会导致查询性能下降。此时可以通过利用关联表（M:N 关系）来避免性能瓶颈。
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。