大网站开发,建设网站上海,柳州专业做网站设计,化妆品网站建设规模设想文章目录 前言#x1f380;一、数据类型分类#x1f380;二、整数类型#xff08;举例 TINYINT 和 INT #xff09;#x1f3ab;2.1 TINYINT 和 INT 类型的定义2.1.1 TINYINT2.1.2 INT #x1f3ab;2.2 表的操作示例2.2.1 创建包含 TINYINT 和 INT 类型的表2.2.2 插入数据… 文章目录 前言一、数据类型分类二、整数类型举例 TINYINT 和 INT 2.1 TINYINT 和 INT 类型的定义2.1.1 TINYINT2.1.2 INT 2.2 表的操作示例2.2.1 创建包含 TINYINT 和 INT 类型的表2.2.2 插入数据示例2.2.3 查询数据2.2.4 更新数据2.2.5 删除记录 2.3 不同类型之间的问题2.3.1 类型范围问题2.3.2 有符号和无符号类型的转换问题2.3.3 自动类型提升2.3.4 整数类型与其他类型的转换2.3.5 数据存储效率 2.4 示例查看不同整数类型之间的比较和行为 三、浮点数类型3.1 浮点数类型的定义3.1.1 FLOAT3.1.2 DOUBLE3.1.3 DECIMAL 3.2 表的操作示例3.2.1 创建包含浮点数类型的表3.2.2 插入数据3.2.3 查询数据3.2.4 更新数据 3.3 不同类型间的问题3.3.1 浮点精度问题3.3.2 存储大小与性能3.3.3 精度和范围的权衡3.3.4 类型转换问题 3.4 示例浮点类型的比较和行为3.4.1 计算浮点数3.4.2 使用 DECIMAL 进行精确计算 总结 四、字符串类型举例 CHAR和 VARCHAR4.1 CHAR 类型4.1.1 特点4.1.2 使用场景4.1.3 示例4.1.4 插入数据4.1.5 查询数据 4.2 VARCHAR 类型4.2.1 特点4.2.2 使用场景4.2.3 示例4.2.4 插入数据4.2.5 查询数据 4.3 CHAR 和 VARCHAR 的区别与选择4.3.1 区别总结4.3.2 选择建议 4.4 示例CHAR 与 VARCHAR 的混合使用4.4.1 插入数据4.4.2 查询数据 4.5 性能和存储空间的考量 五、日期和时间类型5.1 日期和时间类型的定义5.1.1DATE5.1.2 TIME5.1.3DATETIME5.1.4TIMESTAMP5.1.5 YEAR 5.2 表的操作示例5.2.1 创建包含日期和时间字段的表5.2.2 插入数据5.2.3 查询数据5.2.4 更新数据 5.3 不同日期和时间类型的区别与选择5.3.1 **DATETIME 与 TIMESTAMP 的区别**5.3.2 **DATE 与 DATETIME 的选择**5.3.3 **YEAR 的使用** 5.4 日期和时间的操作5.4.1 获取当前日期和时间5.4.2 日期和时间的格式化5.4.3 日期加减操作5.4.4 时间差计算 总结 六、枚举和集合类型6.1 ENUM 类型6.1.1 定义6.1.2 特点6.1.3 使用场景6.1.4 示例6.1.5 插入数据6.1.6 查询数据6.1.7 注意 6.2 SET 类型6.2.1 定义6.2.2 特点6.2.3 使用场景6.2.4 示例6.2.5 插入数据6.2.6 查询数据6.2.7 注意 6.3 ENUM 和 SET 的区别6.4 示例混合使用 ENUM 和 SET6.4.1 插入数据6.4.2 查询用户状态为 active 且兴趣中包含 music 的用户 6.5 注意事项总结 结语 前言
在数据库设计中选择合适的数据类型对性能、存储效率和数据完整性至关重要。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 类型的表
CREATE TABLE user_info (user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 无符号的INT常用于主键age TINYINT, -- 存储年龄使用TINYINT因为年龄不会超过127score INT, -- 存储分数使用INTstatus TINYINT UNSIGNED -- 存储状态码使用无符号的TINYINT
);2.2.2 插入数据示例
INSERT INTO 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 350
WHERE user_id 1;2.2.5 删除记录
DELETE FROM user_info
WHERE user_id 2;2.3 不同类型之间的问题
2.3.1 类型范围问题 当插入超过类型范围的值时可能会触发溢出或报错。例如 对于 TINYINT如果插入的值超过 127有符号或 255无符号会导致溢出。对于 INT插入超过 2,147,483,647有符号或 4,294,967,295无符号范围的值时也会出错。 示例 INSERT INTO user_info (age, score, status) VALUES (128, 1000, 1); -- 错误age 超过 TINYINT 的范围2.3.2 有符号和无符号类型的转换问题
无符号类型只能存储正数而有符号类型可以存储负数。在不同类型之间转换时可能会导致数据变化。如果将负数插入无符号的 TINYINT 或 INT 列中MySQL 会转换为非常大的正数。
示例
CREATE TABLE test_conversion (val_signed TINYINT,val_unsigned TINYINT UNSIGNED
);INSERT INTO test_conversion (val_signed, val_unsigned) VALUES (-1, -1); -- 无符号字段的值会被转换SELECT * FROM test_conversion;
-- 结果val_signed -1, val_unsigned 2552.3.3 自动类型提升
当不同大小的整数类型进行运算时MySQL 会自动将较小的类型提升为较大的类型。例如在 TINYINT 和 INT 的运算中TINYINT 会被提升为 INT以避免溢出。
示例
SELECT age score FROM user_info; -- age 为 TINYINTscore 为 INTage 会自动提升为 INT 进行运算2.3.4 整数类型与其他类型的转换
MySQL 在处理整数与其他类型如字符串、浮点数之间的转换时可能会发生数据丢失或精度问题。例如将浮点数转换为整数时小数部分会被截断。
示例
SELECT CAST(123.456 AS INT); -- 结果为 123浮点数的小数部分被去掉2.3.5 数据存储效率
使用 TINYINT 存储小的整数数据可以节省空间。例如对于年龄、状态码等数据TINYINT 是更合适的选择因为它比 INT 节省内存。但是如果数据范围可能超过 TINYINT 的范围就需要使用 INT 或其他更大的类型。
2.4 示例查看不同整数类型之间的比较和行为
SELECT 128 CAST(128 AS TINYINT); -- 结果为 0因为 128 超出 TINYINT 的范围被转换为 -128总结TINYINT 和 INT 类型主要在存储空间和数值范围上有所不同合理选择合适的类型可以提高数据库的存储效率和性能。在操作时要注意数据范围和类型转换问题以避免意外的结果。
三、浮点数类型
3.1 浮点数类型的定义
3.1.1 FLOAT
FLOAT 类型用于存储单精度浮点数使用 4 字节的存储空间。存储范围 有符号-3.402823466E38 到 -1.175494351E-38以及 1.175494351E-38 到 3.402823466E38无符号0 到 3.402823466E38 FLOAT 的有效精度通常是 7 位十进制数。当需要存储精度较低但范围较大的数值时可以使用 FLOAT 类型。
3.1.2 DOUBLE
DOUBLE 类型用于存储双精度浮点数使用 8 字节的存储空间。存储范围 有符号-1.7976931348623157E308 到 -2.2250738585072014E-308以及 2.2250738585072014E-308 到 1.7976931348623157E308无符号0 到 1.7976931348623157E308 DOUBLE 的有效精度通常是 15 位十进制数。适合需要高精度数值的场景如科学计算或金融计算。
3.1.3 DECIMAL
DECIMAL 类型用于存储定点小数通常用于需要高精度的货币计算或财务数据。通过指定 精度总位数和 标度小数位数来控制存储的数值。例如DECIMAL(10, 2) 表示最多可以存储 10 位数其中 2 位是小数位。不同于 FLOAT 和 DOUBLEDECIMAL 是准确存储小数点后的值不存在浮点误差。
3.2 表的操作示例
3.2.1 创建包含浮点数类型的表
CREATE TABLE products (product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,product_name VARCHAR(100),price DECIMAL(10, 2), -- 使用 DECIMAL 类型存储价格精确到两位小数discount FLOAT, -- 使用 FLOAT 类型存储折扣精度不高但范围大rating DOUBLE -- 使用 DOUBLE 类型存储产品评分需要更高的精度
);3.2.2 插入数据
INSERT INTO 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.20
WHERE product_id 1;3.3 不同类型间的问题
3.3.1 浮点精度问题
FLOAT 和 DOUBLE 都是近似浮点数这意味着它们无法精确地存储所有小数。这在某些应用中可能导致精度丢失特别是在金融计算中。例如浮点运算时可能会有微小的误差
SELECT 0.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 时高精度部分可能会被截断。例如
CREATE TABLE test_float_conversion (val_float FLOAT,val_double DOUBLE
);INSERT INTO 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 示例
CREATE TABLE char_example (country_code CHAR(2), -- 国家代码例如 US、CNzip_code CHAR(5) -- 固定长度的邮政编码例如 12345
);4.1.4 插入数据
INSERT INTO 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 示例
CREATE TABLE varchar_example (full_name VARCHAR(50), -- 用户的全名最多 50 个字符email VARCHAR(100) -- 用户的电子邮件地址最多 100 个字符
);4.2.4 插入数据
INSERT INTO varchar_example (full_name, email)
VALUES (John Doe, john.doeexample.com), (Jane Smith, jane.smithexample.com);4.2.5 查询数据
SELECT * FROM varchar_example;在 VARCHAR 类型中插入的字符串长度是可变的。比如如果定义了 VARCHAR(50)插入的字符串 John Doe 实际只占用 8 个字符的存储空间而不会自动填充到 50 个字符。
4.3 CHAR 和 VARCHAR 的区别与选择
4.3.1 区别总结
特性CHARVARCHAR长度处理固定长度不足部分填充空格可变长度存储实际的字符数存储效率对于固定长度数据效率更高对于可变长度数据节省空间最大长度最多 255 个字符最多 65,535 个字符适用场景长度固定的字段如国家代码等长度不固定的字段如姓名、描述等
4.3.2 选择建议
如果数据长度是固定的如国家代码、邮政编码等使用 CHAR。如果数据长度不固定使用 VARCHAR 以节省空间。
4.4 示例CHAR 与 VARCHAR 的混合使用
CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(20), -- 用户名长度不固定country_code CHAR(2), -- 国家代码长度固定为2phone_number VARCHAR(15) -- 电话号码长度不固定
);4.4.1 插入数据
INSERT INTO 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.1DATE
定义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.3DATETIME
定义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.4TIMESTAMP
定义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-199900 到 69表示 2000-2069
5.2 表的操作示例
5.2.1 创建包含日期和时间字段的表
CREATE TABLE events (event_id INT AUTO_INCREMENT PRIMARY KEY,event_name VARCHAR(100), -- 事件名称event_date DATE, -- 事件日期只存储日期部分event_start_time TIME, -- 事件开始时间只存储时间部分event_end_time TIME, -- 事件结束时间created_at DATETIME DEFAULT CURRENT_TIMESTAMP, -- 创建时间存储日期和时间updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新时间自动更新
);5.2.2 插入数据
INSERT INTO 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 则不进行时区转换存储时按原格式存储检索时也是原格式。 示例 CREATE TABLE test_timestamps (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,dt DATETIME DEFAULT CURRENT_TIMESTAMP
);INSERT INTO test_timestamps () VALUES ();SELECT * FROM test_timestamps;在不同的时区下TIMESTAMP 的值会有所不同而 DATETIME 不会变化。
5.3.2 DATE 与 DATETIME 的选择
如果只需要存储日期例如生日、纪念日等使用 DATE。如果需要同时存储日期和时间例如事件发生的精确时间使用 DATETIME 或 TIMESTAMP。
5.3.3 YEAR 的使用 YEAR 类型适用于只存储年份的场景例如汽车生产年份、毕业年份等。 示例 CREATE TABLE car_models (model_name VARCHAR(50),production_year YEAR
);5.4 日期和时间的操作
5.4.1 获取当前日期和时间
MySQL 提供了多种函数来获取当前日期和时间
NOW()返回当前日期和时间DATETIME 类型。CURDATE()返回当前日期DATE 类型。CURTIME()返回当前时间TIME 类型。CURRENT_TIMESTAMP()返回当前时间戳TIMESTAMP 类型。
SELECT NOW(), 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, INTERVAL 7 DAY) AS new_date;-- 减少 1 个月
SELECT DATE_SUB(2024-10-24, INTERVAL 1 MONTH) 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 示例
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,status ENUM(pending, shipped, delivered, cancelled) NOT NULL -- 订单状态
);6.1.5 插入数据
INSERT INTO orders (status)
VALUES (pending), (shipped);6.1.6 查询数据
SELECT * FROM orders WHERE status shipped;6.1.7 注意 如果插入的值不在定义的枚举列表中MySQL 会插入空字符串 并生成一个警告。 示例 INSERT INTO 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 示例
CREATE TABLE user_preferences (user_id INT AUTO_INCREMENT PRIMARY KEY,interests SET(reading, music, sports, movies, travel) -- 用户的兴趣
);6.2.5 插入数据
INSERT INTO user_preferences (interests)
VALUES (reading,music), (sports,travel);6.2.6 查询数据
SELECT * FROM user_preferences WHERE FIND_IN_SET(music, interests);6.2.7 注意 插入的值可以是多个选项的组合用逗号分隔。 示例 INSERT INTO user_preferences (interests) VALUES (reading,music,sports);如果插入的值不在定义的 SET 列表中MySQL 会忽略该值并插入合法的部分。
6.3 ENUM 和 SET 的区别
特性ENUMSET存储的值数量只能选择一个值可以选择 0 个或多个值定义的最大值数最多 65,535 个不同值最多 64 个不同值存储效率整数索引存储空间使用少使用位存储多个值组合时效率高适用场景状态、分类、单项选择如订单状态、性别多选场景如兴趣、标签、权限索引和排序ENUM 类型的值按索引存储查询和排序较快SET 查询时需用 FIND_IN_SET() 函数插入非法值插入非法值会插入空字符串并生成警告插入非法值会忽略它并生成警告
6.4 示例混合使用 ENUM 和 SET
我们可以在一个表中同时使用 ENUM 和 SET 来存储不同类型的数据比如存储用户的状态和兴趣
CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50),status ENUM(active, inactive, banned), -- 用户状态hobbies SET(reading, sports, music, movies) -- 用户兴趣
);6.4.1 插入数据
INSERT INTO users (username, status, hobbies)
VALUES (Alice, active, reading,music), (Bob, inactive, sports,movies);6.4.2 查询用户状态为 active 且兴趣中包含 music 的用户
SELECT * FROM users WHERE status active AND FIND_IN_SET(music, hobbies);6.5 注意事项 ENUM 和 SET 字段的更新和维护 一旦表中定义了 ENUM 或 SET 字段修改其值列表例如添加新的枚举值会比较麻烦可能需要使用 ALTER TABLE 修改列定义。 ALTER TABLE orders MODIFY COLUMN status ENUM(pending, shipped, delivered, cancelled, returned);索引性能 ENUM 类型因为其底层使用整数索引所以在查询和排序时的性能要比 SET 好一些。如果需要对该列进行大量的排序操作可以优先选择 ENUM。 组合查询 使用 SET 类型时如果需要查找包含多个选项的记录可以结合 FIND_IN_SET() 函数。对于复杂的组合查询SET 可能不如单独的布尔型字段灵活。
总结
ENUM用于从一组预定义的值中选择一个值适合表示状态、分类或单项选择。SET用于从一组预定义的值中选择一个或多个值的组合适合表示兴趣、标签或多项选择。
通过合理使用 ENUM 和 SET 类型可以帮助我们确保数据的完整性限制字段值的范围并且在某些情况下提升存储效率。 结语
数据类型的选择不仅影响数据库的存储效率还可能对应用程序的性能产生直接影响。通过深入理解 MySQL 的数据类型并根据实际需求进行优化可以有效提高数据库的运行效率和稳定性。希望本篇文章能够帮助读者在数据库设计中做出更好的决策使得 MySQL 数据库在项目中更好地发挥作用。
今天的分享到这里就结束啦如果觉得文章还不错的话可以三连支持一下17的主页还有很多有趣的文章欢迎小伙伴们前去点评您的支持就是17前进的动力
