据库常用数据类型详解及实例演示

在关系型数据库中，数据类型定义了存储在表中的数据的性质和格式。选择合适的数据类型对于优化存储空间和提高查询性能至关重要。以下是数据库中常用的数据类型的详细解释及示例。

1. 数值类型

数值类型用于存储整数和浮点数，常用于数学运算和数值比较。

整数类型

1. TINYINT：

   • 存储范围：-128 到 127（有符号）或 0 到 255（无符号）。
   • 存储大小：1字节。
   • 示例：

     CREATE TABLE products (
         id TINYINT UNSIGNED,
         quantity TINYINT
     );
     

2. SMALLINT：

   • 存储范围：-32,768 到 32,767（有符号）或 0 到 65,535（无符号）。
   • 存储大小：2字节。
   • 示例：

     CREATE TABLE employees (
         id SMALLINT UNSIGNED,
         age SMALLINT
     );
     

3. MEDIUMINT：

   • 存储范围：-8,388,608 到 8,388,607（有符号）或 0 到 16,777,215（无符号）。
   • 存储大小：3字节。
   • 示例：

     CREATE TABLE buildings (
         id MEDIUMINT UNSIGNED,
         floors MEDIUMINT
     );
     

4. INT（INTEGER）：

   • 存储范围：-2,147,483,648 到 2,147,483,647（有符号）或 0 到 4,294,967,295（无符号）。
   • 存储大小：4字节。
   • 示例：

     CREATE TABLE orders (
         id INT UNSIGNED,
         amount INT
     );
     

5. BIGINT：

   • 存储范围：-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807（有符号）或 0 到 18,446,744,073,709,551,615（无符号）。
   • 存储大小：8字节。
   • 示例：

     CREATE TABLE transactions (
         id BIGINT UNSIGNED,
         total_amount BIGINT
     );
     

浮点数类型

1. FLOAT：

   • 存储范围：3.402823466E+38 到 -3.402823466E+38。
   • 精度：单精度浮点数，通常精确到7位小数。
   • 示例：

     CREATE TABLE measurements (
         id INT,
         temperature FLOAT
     );
     

2. DOUBLE：

   • 存储范围：1.7976931348623157E+308 到 -1.7976931348623157E+308。
   • 精度：双精度浮点数，通常精确到15位小数。
   • 示例：

     CREATE TABLE calculations (
         id INT,
         precision_value DOUBLE
     );
     

3. DECIMAL（NUMERIC）：

   • 存储范围：根据定义的精度和标度（scale）而定。

   • 精度：用于精确存储十进制数，适用于存储货币等精度要求高的数据。

   • 示例：

     CREATE TABLE products (
         id INT,
         price DECIMAL(10, 2)
     );
     

     • DECIMAL(10, 2) 表示数字总长度为10位，其中小数点后有2位。

2. 字符串类型

字符串类型用于存储文本数据，通常用于存储字符、单词、句子以及长文本。

固定长度字符串

1. CHAR(n)：

   • 固定长度字符串。

   • 存储大小：n字节。

   • 示例：

     CREATE TABLE users (
         id INT,
         gender CHAR(1)
     );
     

     • CHAR(1) 用于存储单个字符，如性别 M 或 F。

可变长度字符串

1. VARCHAR(n)：

   • 可变长度字符串。

   • 存储大小：n字节，最大值取决于数据库引擎。

   • 示例：

     CREATE TABLE customers (
         id INT,
         name VARCHAR(50)
     );
     

     • VARCHAR(50) 用于存储长度不超过50个字符的名称。

长文本类型

1. TEXT：

   • 存储大量文本数据。
   • 最大长度：65,535字节。
   • 示例：

     CREATE TABLE articles (
         id INT,
         content TEXT
     );
     

2. MEDIUMTEXT：

   • 存储更大文本数据。
   • 最大长度：16,777,215字节。
   • 示例：

     CREATE TABLE blogs (
         id INT,
         content MEDIUMTEXT
     );
     

3. LONGTEXT：

   • 存储极大量文本数据。
   • 最大长度：4,294,967,295字节。
   • 示例：

     CREATE TABLE books (
         id INT,
         content LONGTEXT
     );
     

二进制数据

1. BINARY(n)：

   • 固定长度二进制数据。
   • 示例：

     CREATE TABLE files (
         id INT,
         data BINARY(100)
     );
     

2. VARBINARY(n)：

   • 可变长度二进制数据。
   • 示例：

     CREATE TABLE images (
         id INT,
         data VARBINARY(255)
     );
     

3. BLOB：

   • 存储二进制大对象数据。
   • 最大长度：65,535字节。
   • 示例：

     CREATE TABLE videos (
         id INT,
         data BLOB
     );
     

3. 日期和时间类型

日期和时间类型用于存储日期和时间信息，通常用于时间戳、日期记录等场景。

日期和时间类型

1. DATE：

   • 存储日期。
   • 格式：YYYY-MM-DD。
   • 示例：

     CREATE TABLE events (
         id INT,
         event_date DATE
     );
     

2. TIME：

   • 存储时间。
   • 格式：HH:MM:SS。
   • 示例：

     CREATE TABLE schedules (
         id INT,
         start_time TIME
     );
     

3. DATETIME：

   • 存储日期和时间。
   • 格式：YYYY-MM-DD HH:MM:SS。
   • 示例：

     CREATE TABLE logs (
         id INT,
         log_time DATETIME
     );
     

4. TIMESTAMP：

   • 存储时间戳。
   • 格式：YYYY-MM-DD HH:MM:SS。
   • 自动更新为当前时间。
   • 示例：

     CREATE TABLE sessions (
         id INT,
         created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
     );
     

5. YEAR：

   • 存储年份。
   • 格式：YYYY。
   • 示例：

     CREATE TABLE centuries (
         id INT,
         year YEAR
     );
     

4. 布尔类型

布尔类型用于存储真或假值，通常用于标识状态或标记。

布尔类型

1. BOOLEAN：

   • 存储 TRUE 或 FALSE。

   • 实际上在许多数据库中被存储为 TINYINT(1)。

   • 示例：

     CREATE TABLE tasks (
         id INT,
         completed BOOLEAN
     );
     

     • 布尔类型通常用于标识任务是否完成。

5. 枚举和集合类型

枚举和集合类型用于存储一组预定义的值或字符串集合。

枚举类型

1. ENUM：

   • 存储一组预定义的字符串值中的一个。

   • 示例：

     CREATE TABLE orders (
         id INT,
         status ENUM('pending', 'completed', 'shipped', 'cancelled')
     );
     

     • ENUM 类型用于限制列中的值为预定义选项之一，如订单状态。

集合类型

1. SET：
   • 存储一组预定义的字符串值中的零个或多个。
   • 示例：

     CREATE TABLE user_preferences (
         id INT,
         notifications SET('email',
     
     

‘sms’, ‘push’)
);
```

• SET 类型允许列包含多个预定义选项，如用户通知方式。

数据类型选择注意事项

1. 性能和存储空间：

   • 选择数据类型时，应考虑性能和存储空间。较小的数据类型通常占用更少的空间和内存。

2. 数据完整性：

   • 选择数据类型时，应确保其能够存储所有可能的值。避免因数据溢出导致的错误。

3. 索引效率：

   • 使用合适的数据类型可以提高索引效率。例如，索引整型通常比字符串更快。

4. 数据库引擎差异：

   • 不同数据库管理系统（如 MySQL、PostgreSQL、SQL Server）可能有不同的数据类型支持。需根据所用数据库引擎选择合适的数据类型。

数据库的数据类型是设计数据库表时的重要考虑因素。合适的数据类型选择可以提高数据的存储效率和查询性能。在实际应用中，需根据业务需求和数据库引擎特点合理选择数据类型，以实现高效的数据库设计。

非常感谢您读到这里！如果您觉得这篇文章对您有帮助，可以关注一下博主。关注后，您将第一时间获得最新的AI、云计算、运维（Linux、数据库，容器等）技术，以及更多实用的技能干货。

提供丰富的学习资源和实践经验，让你快速掌握AI技能；提供最新的行业动态和应用案例，帮助你在AI领域脱颖而出。