一文搞懂 SQL 数据类型：详解与实战举例，让你轻松上手！

SQL 数据类型及举例

SQL 数据类型根据其存储数据的性质和用途可以分为以下几类：

4.1 数值类型 (Numeric Types)

数值类型用于存储整数和浮点数。这些类型确保数据在存储和计算时具有高效性和准确性。

• **INT
  **：存储整数。例如，123
  。

  CREATE TABLE example (
      id INT,
      age INT
  );
  
  

• **DECIMAL
  或 NUMERIC
  **：用于存储精确的小数。格式为 DECIMAL(M, D)
  ，其中 M
  是数字总长度，D
  是小数点后的位数。例如，123.45
  。

  CREATE TABLE example (
      salary DECIMAL(10, 2)
  );
  
  

• **FLOAT
  **：用于存储浮点数。它们在存储和计算时可能会丢失精度。例如，123.456
  。

  CREATE TABLE example (
      temperature FLOAT
  );
  
  

4.2 日期和时间类型 (Date and Time Types)

日期和时间类型用于存储日期、时间或两者的组合。

• **DATE
  **：存储日期。格式为 YYYY-MM-DD
  。例如，2024-06-02
  。

  CREATE TABLE example (
      birthdate DATE
  );
  
  

• **TIME
  **：存储时间。格式为 HH:MM:SS
  。例如，14:30:00
  。

  CREATE TABLE example (
      appointment_time TIME
  );
  
  

• **DATETIME
  **：存储日期和时间的组合。格式为 YYYY-MM-DD HH:MM:SS
  。例如，2024-06-02 14:30:00
  。

  CREATE TABLE example (
      created_at DATETIME
  );
  
  

• **TIMESTAMP
  **：存储时间戳，通常用于记录行的创建或更新时间。格式同 DATETIME
  ，但具有自动更新特性。例如，2024-06-02 14:30:00
  。

  CREATE TABLE example (
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
  );
  
  

4.3 文本字符串类型 (Character String Types)

文本字符串类型用于存储文本数据。长度可以是固定的或可变的。

• **CHAR
  **：存储固定长度的字符串。如果存储的字符串长度小于指定长度，剩余部分将用空格填充。例如，'Hello '
  （长度为 6）。

  CREATE TABLE example (
      gender CHAR(1)
  );
  
  

• **VARCHAR
  **：存储可变长度的字符串。长度范围在定义时指定，实际存储时仅占用实际字符的空间。例如，'Hello'
  （长度为 5）。

  CREATE TABLE example (
      name VARCHAR(50)
  );
  
  

• **TEXT
  **：存储大文本数据。适用于存储长字符串，例如文章、博客内容等。

  CREATE TABLE example (
      description TEXT
  );
  
  

4.4 二进制字符串类型 (Binary String Types)

二进制字符串类型用于存储二进制数据，例如图像、文件等。

• **BINARY
  **：存储固定长度的二进制数据。如果数据不足指定长度，剩余部分将用 0x00
  填充。

  CREATE TABLE example (
      binary_data BINARY(16)
  );
  
  

• **VARBINARY
  **：存储可变长度的二进制数据。长度范围在定义时指定，实际存储时仅占用实际数据的空间。

  CREATE TABLE example (
      binary_data VARBINARY(256)
  );
  
  

• **BLOB
  **：存储大二进制对象。适用于存储图像、音频、视频等大数据。

  CREATE TABLE example (
      picture BLOB
  );
  
  

举例

1. 创建包含不同数据类型的表：

   CREATE TABLE example (
       id INT AUTO_INCREMENT PRIMARY KEY,
       name VARCHAR(100),
       birthdate DATE,
       email VARCHAR(255),
       profile_picture BLOB,
       created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   );
   
   

2. 插入数据：

   INSERT INTO example (name, birthdate, email)
   VALUES ('John Doe', '1980-05-15', 'john.doe@example.com');
   
   

3. 查询数据：

   SELECT * FROM example;
   
   

这些示例展示了如何在 SQL 中使用各种数据类型来创建表、插入数据和查询数据。选择适当的数据类型可以提高数据库的性能和存储效率。

案例1

create table orders (
    id int primary key auto_increment,
    item_id int,
    amount int,
    unit_price decimal(12, 4),
    price decimal(12, 4),
    ts timestamp default current_timestamp
);

1. create table orders

这部分语句表示创建一个名为 orders
的表。

2. id int primary key auto_increment

• id
  ：列名
• int
  ：数据类型，表示 id
  列是整数类型。
• primary key
  ：定义 id
  列为主键。主键在表中必须唯一，并且不能为 NULL。
• auto_increment
  ：表示 id
  列的值会自动递增，每插入一行记录，id
  值会自动加 1。这确保一个唯一的标识符。

3. item_id int

• item_id
  ：列名。
• int
  ：数据类型，表示 item_id
  列是整数类型。

4. amount int

• amount
  ：列名。
• int
  ：数据类型，表示 amount
  列是整数类型。

5. unit_price decimal(12, 4)

• unit_price
  ：列名。
• decimal(12, 4)
  ：数据类型，表示 unit_price
  列是一个具有 12 位数字和 4 位小数的十进制数。这确保有很高的精度。

6. price decimal(12, 4)

• price
  ：列名。
• decimal(12, 4)
  ：数据类型，表示 price
  列是一个具有 12 位数字和 4 位小数的十进制数。

7. ts timestamp default current_timestamp

• ts
  ：列名。
• timestamp
  ：数据类型，表示 ts
  列是一个时间戳类型。时间戳用于存储日期和时间。
• default current_timestamp
  ：表示 ts
  列的默认值为当前时间。每次插入新记录时，如果没有显式提供 ts
  值，数据库将自动使用当前时间作为 ts
  列的值。

详细解释 create table orders
语句

create table orders (
    id int primary key auto_increment,
    item_id int,
    amount int,
    unit_price decimal(12, 4),
    price decimal(12, 4),
    ts timestamp default now()
);

案例2

create table orders (
    id int primary key auto_increment,
    item_id int,
    amount int,
    unit_price decimal(12, 4),
    price decimal(12, 4),
    description varchar(2000),
    picture blob,
    ts timestamp default now()
);

解释：

1. id int primary key auto_increment：

• id
  列是整数类型，作为主键，并且是自增的。

2. item_id int：

• item_id
  列是整数类型，用于唯一标识符。

3. amount int：

• amount
  列是整数类型。

4. **unit_price decimal(12, 4)**：

• unit_price
  列是具有 12 位数字和 4 位小数的十进制数。

5. **price decimal(12, 4)**：

• price
  列是具有 12 位数字和 4 位小数的十进制数。

6. **description varchar(2000)**：

• description
  列是可变长度的字符串类型，最大长度为 2000 字节。

7. picture blob：

• picture
  列是 BLOB
  类型，用于存储图片等。BLOB
  类型是二进制大对象，可以存储二进制数据如图像文件等。标准的 BLOB
  类型可以存储最大 64KB 的数据，如果图片大小不超过 100KB，可以使用 MEDIUMBLOB
  类型。

8. **ts timestamp default now()**：

• ts
  列是时间戳类型，默认值为当前时间。每次插入新记录时，ts
  列会自动记录插入时间。