SQL 数据类型:预定义数据类型简介

SQL 标准定义了三种数据类型:

  1. 预定义数据类型(如字符、数字、日期等)。
  2. 构造类型(如数组、集合、引用、行类型)。
  3. 用户自定义类型(类似于面向对象语言中的类,可包含构造器、方法等)。

以下主要介绍 预定义数据类型,它们是最常用的数据类型。


数据类型概览

SQL 标准将预定义数据类型分为以下几组:

  1. 字符类型

    • CHAR:定长字符串。
    • VARCHAR:变长字符串。
    • CLOB:大对象字符串。
  2. 二进制类型

    • BINARY:定长二进制数据。
    • VARBINARY:变长二进制数据。
    • BLOB:大对象二进制数据(如图像、音频)。
  3. 数值类型

    • 精确数值类型(NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT)。
    • 近似数值类型(FLOAT, REAL, DOUBLE PRECISION)。
  4. 日期和时间类型

    • DATE:日期(年、月、日)。
    • TIME:时间(小时、分钟、秒,可带时区)。
    • TIMESTAMP:时间戳(日期 + 时间,可带时区)。
    • INTERVAL:时间间隔。
  5. 布尔类型BOOLEAN(值为 TRUEFALSE)。

  6. 扩展类型

    • XML(SQL:1999 引入)。
    • JSON(SQL:2016 引入)。

数据类型详细说明

字符类型

字符类型用于存储字符串:

  • CHARNo:定长字符串,长度固定为 n
  • VARCHARNo:变长字符串,长度上限为 n
  • CLOB:存储超大字符串(例如,MySQL 中称为 LONGTEXT)。

示例

-- 表中包含定长、变长和大对象字符串
CREATE TABLE datatypes_1 (
  id    DECIMAL PRIMARY KEY,
  col_1 CHAR(10),       -- 恰好 10 个字符
  col_2 VARCHAR(150),   -- 最多 150 个字符
  col_3 CLOB            -- 大型字符串
);

二进制类型

二进制类型存储任意二进制数据(如文件、图片):

  • BINARYNo:定长二进制数据,长度为 n 字节。
  • VARBINARYNo:变长二进制数据,最大长度为 n 字节。
  • BLOB:大对象二进制数据。

示例

-- 表中包含定长、变长和大对象二进制数据
CREATE TABLE datatypes_2 (
  id    DECIMAL PRIMARY KEY,
  col_1 BINARY(10),     -- 恰好 10 字节
  col_2 VARBINARY(150), -- 最多 150 字节
  col_3 BLOB            -- 大型二进制对象,如 JPEG、MP3
);

精确数值类型

精确数值类型包括带小数和不带小数的数值:

  • NUMERIC(p, s)DECIMAL(p, s)
    • p(精度):总位数。
    • s(标度):小数位数,必须满足 p ≥ s ≥ 0
  • SMALLINTINTEGERBIGINT:整数类型(不带小数)。

示例

-- 表中使用精确数值类型
CREATE TABLE datatypes_3 (
  id    DECIMAL PRIMARY KEY,
  col_1 DECIMAL(5,2),    -- 总 5 位,小数 2 位
  col_2 SMALLINT,        -- 小整数,无小数点
  col_3 INTEGER,         -- 普通整数
  col_4 BIGINT           -- 大整数(Oracle 不支持)
);

近似数值类型

近似数值类型存储浮点数,通常用于对精度要求不高的计算:

  • FLOAT:存储近似值。
  • REALDOUBLE PRECISION:提供不同精度的浮点数。

创建表和列定义

创建表时,使用 CREATE TABLE 定义数据类型。例如:

CREATE TABLE example_table (
  column1 CHAR(10),        -- 定长字符列
  column2 VARCHAR(255),    -- 变长字符列
  column3 NUMERIC(10, 2),  -- 精确数值列
  column4 DATE             -- 日期列
);

注意事项

  1. 默认值:可以为列设置默认值。例如:

    CREATE TABLE example (
      id INTEGER PRIMARY KEY,
      status CHAR(10) DEFAULT 'active'
    );
    
  2. 兼容性:不同 DBMS 对数据类型的支持可能存在差异。例如:

    • Oracle 使用 RAW 表示变长二进制数据。
    • MySQL 使用 TEXTBLOB 表示大型对象。

通过这些数据类型的定义,您可以为数据库表的设计提供更多灵活性。

近似数值类型 (Approximate Numeric)

近似数值类型用于存储具有实现定义精度的数值(小数点后的精度由具体实现决定)。所有近似数值类型均为带符号类型,主要用于科学计算。

近似数值类型包括以下三种:

  • FLOAT(p):其中 p 表示保证的小数点后位数。
  • REALDOUBLE PRECISION:精度由具体实现定义。

示例:使用近似数值类型创建表

CREATE TABLE datatypes_4 (
  id    DECIMAL PRIMARY KEY,
  col_1 FLOAT(2),     -- 小数点后两位或更多
  col_2 REAL,         -- 实现定义的精度
  col_3 DOUBLE PRECISION -- 实现定义的更高精度
);

时间类型 (Temporal)

与时间相关的数据类型包括:

  • DATE:存储年、月、日。
  • TIME:存储小时、分钟、秒。
  • TIMESTAMP:存储年、月、日、小时、分钟、秒,秒数可以有小数点后的精度。
  • INTERVAL:存储时间间隔。

SQL 标准定义了两种类型的 INTERVAL

  1. INTERVAL YEAR TO MONTH:表示年和月的间隔。
  2. INTERVAL DAY TO SECOND:表示天、小时、分钟、秒的间隔(秒数可以有小数位)。

示例:使用时间数据类型创建表

CREATE TABLE datatypes_5 (
  id    DECIMAL PRIMARY KEY,
  col_1 DATE,                       -- 存储年、月、日(Oracle:还包括小时、分钟、秒)
  col_2 TIME,                       -- 存储时间
  col_3 TIMESTAMP(9),               -- 时间戳,小数点后 9 位精度
  col_4 TIMESTAMP WITH TIME ZONE,   -- 包含时区信息的时间戳
  col_5 INTERVAL YEAR TO MONTH,     -- 表示年到月的时间间隔
  col_6 INTERVAL DAY TO SECOND(6)   -- 表示天到秒的时间间隔,秒数小数点后 6 位精度
);

注意:

  • Oracle 用户:不支持 TIME 类型,使用 DATE 替代。
  • MySQL 用户:不支持时区作为数据类型的一部分,需使用其他方法处理时区。不支持秒的小数部分和 INTERVAL 类型,但支持 INTERVAL 数据值。

布尔类型 (Boolean)

SQL 支持三值逻辑(3-value-logic):TRUE(真)、FALSE(假)和 UNKNOWN(未知)。布尔数据类型的列可以存储 TRUEFALSEUNKNOWN 通过存储 NULL 表示。

示例:布尔类型

CREATE TABLE datatypes_6 (
  id    DECIMAL PRIMARY KEY,
  col_1 BOOLEAN     -- 不被 Oracle 支持
);

XML 类型

SQL 标准(第 14 部分)扩展了预定义数据类型,新增了 XML 类型,并为其定义了大量专用函数。

示例:XML 数据类型

CREATE TABLE datatypes_7 (
  id    DECIMAL PRIMARY KEY,
  col_1 XML
);

注意:

  • Oracle 用户:将 XML 类型表示为 XMLType
  • MySQL 用户:不支持 XML 数据类型。

域 (Domains)

域(Domain)是数据类型的一种扩展,用于约束列中可以存储的有效值集合。域的概念是用户自定义类型的早期前身,可能已经过时。


清理示例表

DROP TABLE datatypes_1;
DROP TABLE datatypes_2;
DROP TABLE datatypes_3;
DROP TABLE datatypes_4;
DROP TABLE datatypes_5;
DROP TABLE datatypes_6;
DROP TABLE datatypes_7;
Last modified: Tuesday, 28 January 2025, 1:51 PM