当前位置: 首页 > news >正文

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

在这里插入图片描述

文章目录

  • 前言
    • 🎀一、数据类型分类
    • 🎀二、整数类型(举例 `TINYINT` 和 `INT` )
      • 🎫2.1 `TINYINT` 和 `INT` 类型的定义
        • 2.1.1 `TINYINT`
        • 2.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 `FLOAT`
        • 3.1.2 `DOUBLE`
        • 3.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`和 `VARCHAR`)
      • 🎫4.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.1`DATE`
        • 5.1.2 `TIME`
        • 5.1.3`DATETIME`
        • 5.1.4`TIMESTAMP`
        • 5.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` 和 `SET`
        • 6.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集合类型,可以包含零个或多个字符串值-

🎀二、整数类型(举例 TINYINTINT

🎫2.1 TINYINTINT 类型的定义

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 创建包含 TINYINTINT 类型的表
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 有符号和无符号类型的转换问题
  • 无符号类型只能存储正数,而有符号类型可以存储负数。在不同类型之间转换时,可能会导致数据变化。
  • 如果将负数插入无符号的 TINYINTINT 列中,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 = 255
2.3.3 自动类型提升
  • 当不同大小的整数类型进行运算时,MySQL 会自动将较小的类型提升为较大的类型。例如,在 TINYINTINT 的运算中,TINYINT 会被提升为 INT,以避免溢出。

示例:

SELECT age + score FROM user_info;  -- age 为 TINYINT,score 为 INT,age 会自动提升为 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

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

🎀三、浮点数类型

🎫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 位是小数位。
  • 不同于 FLOATDOUBLEDECIMAL 是准确存储小数点后的值,不存在浮点误差。

🎫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 浮点精度问题
  • FLOATDOUBLE 都是近似浮点数,这意味着它们无法精确地存储所有小数。这在某些应用中可能导致精度丢失,特别是在金融计算中。
  • 例如,浮点运算时可能会有微小的误差:
SELECT 0.1 + 0.2;  -- 结果可能是 0.30000000000000004,而不是预期的 0.3

因此,在需要精确计算的场合(如金额),应使用 DECIMAL 类型而不是 FLOATDOUBLE

3.3.2 存储大小与性能
  • FLOAT 使用 4 字节,DOUBLE 使用 8 字节。DECIMAL 的存储空间取决于定义的精度和标度。如果你需要存储大范围的浮点数,并且对精度要求不高,可以选择 FLOATDOUBLE,以节省存储空间。
  • DECIMAL 相比 FLOATDOUBLE 的性能稍差,因为它需要进行更多的数学计算来确保精度。
3.3.3 精度和范围的权衡
  • FLOATDOUBLE 提供了更大的数值范围,但它们的精度有限。
  • 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:适用于需要精确小数的场景,尤其是财务、货币计算等,不会出现浮点数的精度误差。

🎀四、字符串类型(举例 CHARVARCHAR

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

🎫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'、'CN'zip_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.doe@example.com'), ('Jane Smith', 'jane.smith@example.com');
4.2.5 查询数据:
SELECT * FROM varchar_example;

VARCHAR 类型中,插入的字符串长度是可变的。比如,如果定义了 VARCHAR(50),插入的字符串 "John Doe" 实际只占用 8 个字符的存储空间,而不会自动填充到 50 个字符。

🎫4.3 CHARVARCHAR 的区别与选择

4.3.1 区别总结:
特性CHARVARCHAR
长度处理固定长度,不足部分填充空格可变长度,存储实际的字符数
存储效率对于固定长度数据,效率更高对于可变长度数据,节省空间
最大长度最多 255 个字符最多 65,535 个字符
适用场景长度固定的字段,如国家代码等长度不固定的字段,如姓名、描述等
4.3.2 选择建议:
  • 如果数据长度是固定的(如国家代码、邮政编码等),使用 CHAR
  • 如果数据长度不固定,使用 VARCHAR 以节省空间。

🎫4.4 示例:CHARVARCHAR 的混合使用

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-019999-12-31
5.1.2 TIME
  • 定义TIME 类型用于存储时间值,不包含日期部分。可以存储正或负的时间值。
  • 格式HH:MM:SS(例如:13:45:30
  • 存储范围-838:59:59838:59:59
5.1.3DATETIME
  • 定义DATETIME 类型用于存储日期和时间的组合。
  • 格式YYYY-MM-DD HH:MM:SS(例如:2024-10-24 13:45:30
  • 存储范围1000-01-01 00:00:009999-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_TIMESTAMPON UPDATE CURRENT_TIMESTAMP 自动更新。
5.1.5 YEAR
  • 定义YEAR 类型用于存储年份值。
  • 格式YYYY 或者 YY(例如:202424
  • 存储范围19012155(四位),或者 7099(两位表示 1970-1999),0069(表示 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 DATETIMETIMESTAMP 的区别
  • 时区处理

    • 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 DATEDATETIME 的选择
  • 如果只需要存储日期(例如生日、纪念日等),使用 DATE
  • 如果需要同时存储日期和时间(例如事件发生的精确时间),使用 DATETIMETIMESTAMP
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 ENUMSET 的区别

特性ENUMSET
存储的值数量只能选择一个值可以选择 0 个或多个值
定义的最大值数最多 65,535 个不同值最多 64 个不同值
存储效率整数索引存储,空间使用少使用位存储,多个值组合时效率高
适用场景状态、分类、单项选择(如订单状态、性别)多选场景(如兴趣、标签、权限)
索引和排序ENUM 类型的值按索引存储,查询和排序较快SET 查询时需用 FIND_IN_SET() 函数
插入非法值插入非法值会插入空字符串并生成警告插入非法值会忽略它并生成警告

🎫6.4 示例:混合使用 ENUMSET

我们可以在一个表中同时使用 ENUMSET 来存储不同类型的数据,比如存储用户的状态和兴趣:

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 注意事项

  1. ENUM 和 SET 字段的更新和维护

    • 一旦表中定义了 ENUMSET 字段,修改其值列表(例如,添加新的枚举值)会比较麻烦,可能需要使用 ALTER TABLE 修改列定义。
    ALTER TABLE orders MODIFY COLUMN status ENUM('pending', 'shipped', 'delivered', 'cancelled', 'returned');
    
  2. 索引性能

    • ENUM 类型因为其底层使用整数索引,所以在查询和排序时的性能要比 SET 好一些。如果需要对该列进行大量的排序操作,可以优先选择 ENUM
  3. 组合查询

    • 使用 SET 类型时,如果需要查找包含多个选项的记录,可以结合 FIND_IN_SET() 函数。对于复杂的组合查询,SET 可能不如单独的布尔型字段灵活。

总结

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

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


结语

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

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

在这里插入图片描述

相关文章:

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

文章目录 前言🎀一、数据类型分类🎀二、整数类型(举例 TINYINT 和 INT )🎫2.1 TINYINT 和 INT 类型的定义2.1.1 TINYINT2.1.2 INT 🎫2.2 表的操作示例2.2.1 创建包含 TINYINT 和 INT 类型的表2.2.2 插入数据…...

使用任意绘图软件自学并结合上课所学内容完成数据库原理图绘制

本次绘图采用亿图图示软件...

static、 静态导入、成员变量的初始化、单例模式、final 常量(Content)、嵌套类、局部类、抽象类、接口、Lambda、方法引用

static static 常用来修饰类的成员:成员变量、方法、嵌套类 成员变量 被static修饰:类变量、成员变量、静态字段 在程序中只占用一段固定的内存(存储在方法区),所有对象共享可以通过实例、类访问 (一般用类名访问和修…...

基于SSM的智能养生平台管理系统源码带本地搭建教程

技术栈与架构 技术框架:采用SSM(Spring Spring MVC MyBatis)作为后端开发框架,结合前端技术栈layui、JSP、Bootstrap与jQuery,以及数据库MySQL 5.7,共同构建项目。 运行环境:项目在JDK 8环境…...

Latex中文排版字体和字号

中文排版 最近常用latex排版,也遇到了很多问题。这里对于主要的参考文章做一个总结和推荐。 一份不太简短的 LaTeX2ε 介绍【中文资料】ctex宏包用户手册,用户手册使用 命令行texdoc ctex 这两个文档都是中文的,而且几乎解决了我90%的排版…...

[C++ 11] 列表初始化:轻量级对象initializer_list

C发展历史 C11是C语言的第二个主要版本,也是自C98以来最重要的一次更新。它引入了大量的新特性,标准化了已有的实践,并极大地改进了C程序员可用的抽象能力。在2011年8月12日被ISO正式采纳之前,人们一直使用“C0x”这个名称&#…...

【NodeJS】NodeJS+mongoDB在线版开发简单RestfulAPI (八):API说明(暂时完结,后续考虑将在线版mongoDB变为本地版)

本项目旨在学习如何快速使用 nodejs 开发后端api,并为以后开展其他项目的开启提供简易的后端模版。(非后端工程师) 由于文档是代码写完之后,为了记录项目中需要注意的技术点,因此文档的叙述方式并非开发顺序&#xff0…...

manictime整合两个数据库的数据

作用 老电脑崩溃了,有个1t.db, 新电脑有个3t.db 那么重装系统后就想整合起来用。 整合前文件大小 整合命令 .\mtdb.exe importtimelines -sdbpa ManicTimeCore-1t.db -dbpa ManicTimeCore-3t.db -tt ManicTime/ComputerUsage,ManicTime/Applications,ManicTime…...

Spring Boot植物健康系统:智慧农业的新趋势

6系统测试 6.1概念和意义 测试的定义:程序测试是为了发现错误而执行程序的过程。测试(Testing)的任务与目的可以描述为: 目的:发现程序的错误; 任务:通过在计算机上执行程序,暴露程序中潜在的错误。 另一个…...

(三)第一个Qt程序“Qt版本的HelloWorld”

一、随记 我们在学习编程语言的时候,各种讲解编程语言的书籍中通常都会以一个非常经典的“HelloWorld”程序展开详细讲解。程序虽然简短,但是“麻雀虽小,五脏俱全”,但是却非常适合用来熟悉程序结构、规范,快速形成对编…...

【Python知识】一个强大的数据分析库Pandas

文章目录 Pandas概述1. 安装 Pandas2. 基本数据结构3. 数据导入和导出4. 数据清洗5. 数据选择和过滤6. 数据聚合和摘要7. 数据合并和连接8. 数据透视表9. 时间序列分析10. 数据可视化 📈 如何使用 Pandas 进行复杂的数据分析?1. 数据预处理2. 处理缺失值…...

10.26学习

1.整形的定义和输出 在C语言中,整形(Integer)是一种基本数据类型,用于存储整数。整形变量可以是正数、负数或零。在定义和输出整形变量时,需要注意以下几点: ①定义整形变量: 使用 int 关键字…...

CSS易漏知识

复杂选择器可以通过(id的个数,class的个数,标签的个数)的形式,计算权重。 如果我们需要将某个选择器的某条属性提升权重,可以在属性后面写!important;注意!importent要写在;前面 很多公司不允许…...

【10天速通Navigation2】(三) :Cartographer建图算法配置:从仿真到实车,从原理到实现

前言 往期内容: 第一期:【10天速通Navigation2】(一) 框架总览和概念解释第二期:【10天速通Navigation2】(二) :ROS2gazebo阿克曼小车模型搭建-gazebo_ackermann_drive等插件的配置和说明 本教材将贯穿nav2的全部内容&#xff0c…...

测试造数,excel转insert语句

目录 excel转sql的insert语句一、背景二、直接上代码 excel转sql的insert语句 一、背景 在实际测试工作中,需要频繁地进行测试造数并插入数据库验证,常规的手写sql语句过于浪费时间,为此简单写个脚本,通过excel来造数&#xff0…...

Python 应用可观测重磅上线:解决 LLM 应用落地的“最后一公里”问题

作者:彦鸿 背景 随着 LLM(大语言模型)技术的不断成熟和应用场景的不断拓展,越来越多的企业开始将 LLM 技术纳入自己的产品和服务中。LLM 在自然语言处理方面表现出令人印象深刻的能力。然而,其内部机制仍然不明确&am…...

从零开始:用Spring Boot搭建厨艺分享网站

2 相关技术 2.1 Spring Boot框架简介 Spring Boot是由Pivotal团队提供的全新框架,其设计目的是用来简化新Spring应用的初始搭建以及开发过程。该框架使用了特定的方式来进行配置,从而使开发人员不再需要定义样板化的配置。通过这种方式,Sprin…...

《2024中国泛娱乐出海洞察报告》解析,垂直且多元化方向发展!

随着以“社交”为代表的全球泛娱乐市场规模不断扩大以及用户需求不断细化,中国泛娱乐出海产品正朝着更加垂直化、多元化的方向发展。基于此,《2024中国泛娱乐出海洞察报告》深入剖析了中国泛娱乐行业出海进程以及各细分赛道出海现状及核心特征。针对中国…...

强化学习数学原理学习(一)

前言 总之开始学! 正文 先从一些concept开始吧,有一个脉络比较好 state 首先是就是状态和状态空间,显而易见,不多说了 action 同理,动作和动作空间 state transition 状态转换,不多说 policy 策略,不多说 reward 奖励,不多说 MDP(马尔科夫) 这里需要注意到就是这个是无…...

获 Sei 基金会投资的 MetaArena :掀起新一轮链上游戏革命

MetaArena 是一个综合性的 Web3 游戏开发和发布平台,集成了最先进的技术架构,包括 Unreal Engine 5.3、去中心化虚拟资产交易市场和分布式计算资源支持。平台不仅为开发者提供了高效的开发工具,还通过跨链功能和 AI 模块,极大简化…...

react-signature-canvas 实现画笔与橡皮擦功能

react-signature-canvas git 地址 代码示例 import React, { Component } from react import { createRoot } from react-dom/clientimport SignaturePad from ../../src/index.tsximport * as styles from ./styles.module.cssclass App extends Component {state { trimmed…...

004:ABBYY PDF Transformer安装教程

引言:本文主要讲解。 一、软件介绍 ABBYY PDF Transformer由ABBYY公司出品,属于一款家庭及商业都适用的PDF文档转换工具。它结合了ABBYY的OCR(光学字符识别)技术和Adobe PDF库技术,以确保能够便捷地处理任何类型的PDF…...

FlinkSQL之temporary join开发

在实时开发中,双流join获取目标对应时刻的属性时,经常使用temporary join。笔者在流量升级的实时迭代中,需要让流量日志精准的匹配上浏览时间里对应的商品属性,使用temporary join开发过程中踩坑不少,将一些经验沉淀在…...

第二十六节 直方图均衡化

图像直方图均衡化 图像直方图均衡化可以增强图像增强,对输入图像进行直方图均衡化处理,提升后续对象检测的准确率在Opencv人脸检测的代码演示中已经很常见了,此外对医学影像图像与卫星遥感图像也经常通过直方图均衡化来提升图像质量 Opencv…...

工单管理用什么工具好?8款推荐清单

本文推荐的8款项目工单管理系统有:1. PingCode; 2.Worktile; 3.Teambition; 4.致远OA; 5.TAPD; 6.Gitee; 7.Wrike; 8.Trello。 很多企业在处理项目工单时,依然依赖电子邮件、Excel表格,甚至是手动记录。这样做不仅效率低下,还容易导致工单遗漏…...

工地安全新突破:AI视频监控提升巡检与防护水平

在建筑工地和其他劳动密集型行业,工人的安全一直是管理工作的重中之重。为了确保工地的安全管理更加高效和智能化,AI视频监控卫士。通过人工智能技术,系统不仅能实时监控,还能自动识别工地现场的安全隐患,为工地管理者…...

World of Warcraft [CLASSIC][80][the Ulduar]

Ulduar 奥杜尔副本介绍 奥杜尔共计14个BOSS,通常说的10H就是10个苦难模式就是全通,9H就是除了【观察者奥尔加隆】,特别说明开启【观察者奥尔加隆】,是需要打掉困难模式4个守护者的。 所以人们经常说的类似“10H 观察者”、“10H…...

python实现数据库的增删改查功能,图形化版本

import tkinter from tkinter import * import psycopg2 from tkinter import messagebox#连接信息 t_conn{"dbname": "d1","user": "u1","password": "123qqq...A","port": "15400","h…...

pipeline开发笔记

pipeline开发笔记 jenkins常用插件Build Authorization Token Root配置GitLab的webhooks(钩子)配置构建触发器--示例 piblish over sshBlue OceanWorkspace Cleanup PluginGit插件PipelineLocalization: Chinese (Simplified) --中文显示Build Environment Plugin 显示构建过程…...

spark读取parquet文件

源码 parquet文件读取的入口是FileSourceScanExec,用parquet文件生成对应的RDD 非bucket文件所以走createNonBucketedReadRDD方法。 createNonBucketedReadRDD 过程: 确定文件分割参数 openCostInBytes4M 相关参数spark.sql.files.openCostInBytes4M…...

本地升级wordpress/关键词你们懂的

HTTP 的 Keep-Alive,是由应用层(用户态) 实现的,称为 HTTP 长连接; TCP 的 Keepalive,是由 TCP 层(内核态) 实现的,称为 TCP 保活机制 HTTP 的 Keep-Alive HTTP 是基于…...

在线音乐网站开发摘要/免费b站推广短视频

在本教程中,我们将学习44矩阵键盘以及Arduino键盘接口的工作原理。键盘是一种输入设备,用于输入密码,拨打号码,浏览菜单甚至控制机器人。您可能已经看到ATM上使用了键盘,安全系统,电话等,允许用…...

企业标志logo/seo课堂

目录 一、String 对象 1、属性 2、常用方法 二、Array 对象 1、属性 2、常用方法 三、Date 日期对象 1、创建 Date 日期对象 2、常用方法 四、Math 对象 一、String 对象 1、属性 length:表示字符串的长度 2、常用方法 【1】与 HTML 相关的方法 bold(…...

哪几个网站适合自己做外贸/app优化推广

一到十二题由于比较简单,所以我直接给出了命令和输出。十二题后详述了解决方法和做题思路。这次做题后对grep、cut 、awk、wc等命令印象更加深刻,学到很多知识。但是学生初来乍到,有错误在所难免,还请老师们纠正。以下是我完成得作…...

展厅设计案例100例/全国最好网络优化公司

ARM开发经典学习网站推荐 1. EG3 关于嵌入式开发的站点,提供非常多关于嵌入式开发的资料。包括开发公司,技术文档,免费资源等等。版面包括busses & boards,embedded software,dsp,embedded systems,opensource,rtos,embedded chips,system-on-a-chip 等等。 强烈推荐…...

wordpress使用置顶文章没用/网络平台的推广方法

PPT文件上呦限制编辑,不知道密码应该怎么去掉? 取消限制编辑在不知道密码的情况下,需要用到工具的帮助【PPT解密大师】快速找回密码_轻松移除使用限制-奥凯丰okfone 选择【解除限制】将PPT文件添加到软件中,点击一下【开始】就可…...