MySQL数据类型详解

数据类型

MySQL支持多种数据类型，每种数据类型都有不同的特点和用途。以下是一些常见的MySQL数据类型：

1. 数值型数据类型：
   • INT：整数类型，占用4个字节。
   • BIGINT：大整数类型，占用8个字节。
   • FLOAT：单精度浮点数类型。
   • DOUBLE：双精度浮点数类型。
   • DECIMAL：固定精度的小数类型 decimal(9,2)：整数7位，小数2位，一共9位。
   • BIT：存储比特位（位）数据，在 BIT 类型中，较低的比特位（位）在存储时先放置，较高的比特位（位）在后放置
2. 字符串型数据类型：
   • CHAR：固定长度的字符类型，最多可存储255个字符。
   • VARCHAR：可变长度的字符类型，最多可存储65535个字符。
   • TEXT：可变长度的文本类型，最多可存储65535个字符。
3. 日期和时间型数据类型：
   • DATE：日期类型(年月日)，格式为’YYYY-MM-DD’。
   • TIME：时间类型（小时分钟秒），格式为’HH:MM:SS’。
   • DATETIME：日期和时间类型（年月日 小时分钟秒 ），格式为’YYYY-MM-DD HH:MM:SS’。
   • TIMESTAMP：时间戳类型，用于记录时间的变化，格式为’YYYY-MM-DD HH:MM:SS’。
   • ENUM：枚举类型，用于存储从预定义值列表中选择的一个值。
   • SET：集合类型，用于存储从预定义值列表中选择的多个值。
   • BINARY：二进制类型，存储固定长度的二进制数据。
   • VARBINARY：可变长度的二进制类型，存储可变长度的二进制数据。
   • BLOB：二进制大对象类型，用于存储大量的二进制数据。
4. 布尔型数据类型：
   • BOOL：布尔类型，取值为TRUE或FALSE。
   • BOOLEAN：布尔类型，取值为TRUE或FALSE。

此外，MySQL还提供了其他一些数据类型，如JSON、GEOMETRY等，用于存储特定类型的数据。每种数据类型都有其特定的存储需求和使用场景，根据实际情况选择合适的数据类型可以提高数据库的性能和数据的完整性。

整型

1. TINYINT：占用1个字节，范围为-128到127（有符号）或0到255（无符号）。

   • TINYINT可以用于存储较小的整数值或表示布尔类型的数据。

2. SMALLINT：占用2个字节，范围为-32,768到32,767（有符号）或0到65,535（无符号）。

   • SMALLINT适合存储中等范围的整数数据。

3. MEDIUMINT：占用3个字节，范围为-8,388,608到8,388,607（有符号）或0到16,777,215（无符号）。

   • MEDIUMINT可用于存储较大范围的整数数据。

4. INT（或INTEGER）：占用4个字节，范围为-2,147,483,648到2,147,483,647（有符号）或0到4,294,967,295（无符号）。

   • INT是MySQL中常用的整型类型，适合大多数整数数据存储需求。

5. BIGINT：占用8个字节，范围为-9,223,372,036,854,775,808到9,223,372,036,854,775,807（有符号）或0到18,446,744,073,709,551,615（无符号）。

   • BIGINT适合存储非常大的整数数据，如主键、自增ID等。

以上整型类型均可以选择**有符号（SIGNED）（默认）或无符号（UNSIGNED）**属性。有符号类型可以存储正负整数，而无符号类型只能存储非负整数，但无符号类型的范围更大。

char、varchar

在 MySQL 中，CHAR 和 VARCHAR 是两种不同的数据类型，用于存储字符数据。

1. CHAR：CHAR 类型是一种固定长度的字符类型。当定义一个 CHAR 列时，您需要指定该列可以容纳的字符的固定长度。如果插入的值长度小于指定的长度，MySQL 会在值的右侧填充空格，使其达到指定长度。例如，如果定义一个 CHAR(10) 列，并插入一个长度为 5 的字符串，MySQL 会在字符串的右侧填充 5 个空格，使其总长度为 10。

2. VARCHAR：VARCHAR 类型是一种可变长度的字符类型。当定义一个 VARCHAR 列时，您需要指定该列可以容纳的字符的最大长度。与 CHAR 不同，VARCHAR 列只占用实际插入的数据所需的存储空间。如果插入的值长度小于指定的最大长度，它将仅占用实际值的存储空间，而不会填充空格。

主要区别如下：

• 存储空间：CHAR 类型列总是占用其定义的固定长度的存储空间，无论实际插入的值长度如何；而 VARCHAR 类型列只占用实际插入的值的存储空间。

• 填充空格：CHAR 类型会在实际值的右侧填充空格，以达到定义的固定长度；VARCHAR 类型不会填充空格，它仅占用实际值的存储空间。

• 索引效率：由于 CHAR 列的固定长度，它在使用索引进行查询时的效率可能会更高。而 VARCHAR 类型由于可变长度的特性，可能在索引效率上稍逊一筹。

基于上述区别，通常建议在以下情况下使用 CHAR 类型：

• 列的值长度固定且相对较小。
• 列将被频繁地更新。
• 列将被用于索引和高性能查询。

而在以下情况下，使用 VARCHAR 类型更合适：

• 列的值长度可变或较大。
• 列的值可能为空或包含不同长度的数据。
• 列将不经常被更新，或者更新时增加的长度较少。

date、time、datetime、timestamp

在 MySQL 中，有四个与时间相关的数据类型，它们是 DATE、TIME、DATETIME 和 TIMESTAMP。每个数据类型都有其特定的用途和存储范围。

1. DATE 数据类型：

   • DATE 数据类型用于存储日期值，表示一个具体的日期。
   • 它以 ‘YYYY-MM-DD’ 格式表示，其中 YYYY 表示年份，MM 表示月份（01-12），DD 表示日期（01-31）。
   • DATE 数据类型可以存储的范围为 ‘1000-01-01’ 到 ‘9999-12-31’。
   • 例如，‘2023-07-07’ 表示公历2023年7月7日。

2. TIME 数据类型：

   • TIME 数据类型用于存储时间值，表示一个具体的时刻。
   • 它以 ‘HH:MM:SS’ 格式表示，其中 HH 表示小时（00-23），MM 表示分钟（00-59），SS 表示秒（00-59）。
   • TIME 数据类型可以存储的范围为 ‘-838:59:59’ 到 ‘838:59:59’。
   • 例如，‘12:34:56’ 表示下午 12 点 34 分 56 秒。

3. DATETIME 数据类型：

   • DATETIME 数据类型用于存储日期和时间值，表示一个具体的日期和时刻。
   • 它以 ‘YYYY-MM-DD HH:MM:SS’ 格式表示，其中 YYYY 表示年份，MM 表示月份（01-12），DD 表示日期（01-31），HH 表示小时（00-23），MM 表示分钟（00-59），SS 表示秒（00-59）。
   • DATETIME 数据类型可以存储的范围为 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’。
   • 例如，‘2023-07-07 12:34:56’ 表示公历2023年7月7日下午12点34分56秒。

4. TIMESTAMP 数据类型：

   • TIMESTAMP 数据类型用于存储日期和时间值，表示从 ‘1970-01-01 00:00:01’（UNIX 时间戳的起始时间）到当前时间的秒数。
   • 它以 ‘YYYY-MM-DD HH:MM:SS’ 格式表示，具有与 DATETIME 相同的范围和格式。
   • TIMESTAMP 数据类型存储的值会自动更新为当前的时间戳，通常用于记录数据的创建或修改时间。
   • 例如，‘2023-07-07 12:34:56’ 表示公历2023年7月7日下午12点34分56秒。

需要注意以下几点：

• DATE、TIME、DATETIME 和 TIMESTAMP 数据类型都用于存储和操作时间和日期值，但它们的存储方式和范围略有不同。
• DATE 和 DATETIME 存储的是实际的日期和时间值，而 TIME 存储的是具体的时刻。
• TIMESTAMP 类型存储的是自动更新的时间戳，可以用于跟踪记录的创建或修改时间。
• TIMESTAMP 类型的存储空间较小，通常使用 4 字节，而 DATETIME 使用 8 字节。

ENUM

ENUM 是一种用于定义枚举类型的列类型。枚举类型允许您在一组预定义的值中选择一个单独的值作为列的值。

下面是创建和使用 ENUM 类型的一般语法：

column_name ENUM(value1, value2, value3, ...)

其中，column_name 是要创建的列的名称，而 value1, value2, value3 是枚举类型的可选值列表。

例如，以下是一个表示用户角色的 ENUM 类型的示例：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    role ENUM('admin', 'user', 'guest')
);

在上述示例中，role 列使用 ENUM 类型，并且它的值只能是 ‘admin’、‘user’ 或 ‘guest’ 中的一个。

当插入数据时，只能使用枚举类型中定义的值：

INSERT INTO users (id, name, role) VALUES (1, 'John', 'admin');

您还可以通过查询表的元数据来了解 ENUM 列的可能值：

SHOW COLUMNS FROM users LIKE 'role';

在查询数据时，可以使用标准的 SELECT 语句来检索和筛选 ENUM 列的数据：

SELECT * FROM users WHERE role = 'admin';

需要注意以下几点：

• 枚举类型是有限的，只能从预定义的值中选择一个。如果尝试插入一个不在枚举列表中的值，将会引发错误。

• 枚举类型的存储和检索效率通常比字符串类型更高，因为它们使用整数值来表示每个枚举值。

• 枚举类型的顺序是基于它们在列定义中的顺序。它们将按照定义的顺序进行排序。

• 可以使用 ALTER TABLE 语句来修改现有的 ENUM 列，例如添加、删除或重排序枚举值。

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+
UPDATE shirts SET size = 'small' WHERE size = 'large';
COMMIT;