五种主流数据库：常用数据类型

csdn推荐

在设计数据库的表结构时，我们需要明确表中包含哪些字段以及字段的数据类型。字段的数据类型定义了该字段能够存储的数据种类以及支持的操作。

本文将会介绍五种主流数据库中常用的数据类型以及如何选择合适的数据类型，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。常见的 SQL 数据类型包括数字类型、字符串类型、日期时间类型以及二进制类型。

数据类型MySQLOracleSQL ServerPostgreSQLSQLite

精确数字

TINYINT

SMALLINT

MEDIUMINT

INTEGER

BIGINT

NUMERIC

SMALLINT

INTEGER

NUMERIC

SMALLINT

MEDIUMINT

INTEGER

BIGINT

NUMERIC

SMALLINT

MEDIUMINT

INTEGER

BIGINT

NUMERIC

动态类型

近似数字

FLOAT

DOUBLE PRECISION

BINARY_FLOAT

BINARY_DOUBLE

REAL

DOUBLE PRECISION

REAL

DOUBLE PRECISION

动态类型

定长字符串

CHAR

CHAR

CHAR

CHAR

动态类型

变长字符串

VARCHAR

VARCHAR2

VARCHAR

VARCHAR

动态类型

字符串大对象

TEXT

CLOB

VARCHAR(MAX)

TEXT

动态类型

日期

DATE

DATE

DATE

DATE

动态类型

时间

TIME

--

TIME

TIME

动态类型

时间戳

TIMESTAMP

TIMESTAMP

DATETIME2

TIMESTAMP

动态类型

二进制

BINARY

VARBINARY

BLOB

BLOB

BINARY

VARBINARY

BYTEA

动态类型

注意：SQLite 默认使用动态类型，字段的数据类型由实际存储的内容而不是定义时的数据类型决定。例如，我们可以将某个字段定义为整数类型，然后用于存储字符串数据。另外，SQLite 3.37.0 开始支持严格（）的数据类型。

数字类型

数字类型主要分为两类：精确数字和近似数字。4 种主流数据库对于常用数字类型的支持如下表所示。

提示：SQLite 默认使用动态数据类型，任何类型都可以存储数字。如果使用了严格（STRICT）的数据类型，可以使用 INTEGER 或者 ANY 类型存储整数，使用 REAL 或者 ANY 类型存储单精度浮点数。

精确数字

精确数字类型用于存储整数或者包含固定小数位的数字。其中，SMALLINT、INTEGER 和 BIGINT 都可以表示整数。另外，INT 是 INTEGER 的同义词。

Oracle 中的 SMALLINT 和 INTEGER 都是 NUMBER(38, 0) 的同义词，其不支持 BIGINT 类型。

MySQL、Microsoft SQL Server 以及 PostgreSQL 中的 SMALLINT 类型支持的整数范围为-215~215-1，INTEGER 支持的整数范围为-231~231-1，BIGINT 支持的整数范围为-263~263-1。

MySQL 还支持 TINYINT 和 MEDIUMINT 两种整数类型。另外，MySQL 中的所有数字类型都分为有符号类型（INTEGER、INTEGER SIGNED 等）和无符号类型（INTEGER UNSIGNED等），无符号整型支持的正整数范围（0~232-1）比有符号整型扩大了一倍。

NUMERIC(p, s)用于存储包含小数的精确数字。其中精度 p 表示总的有效位数，刻度 s 表示小数点后允许的位数。例如，123.04 的精度为 5，刻度为 2。

p 和 s 都是可选的参数，s 为 0 表示整数。SQL 标准要求 p 大于或等于 s，s 大于或等于 0，并且 p 大于 0。

另外，DECIMAL 和 DEC 都是 NUMERIC 的同义词。Oracle 中的 NUMERIC 和 DECIMAL都是 NUMBER 的同义词。

整数类型通常用于存储数字编号、产品数量、课程得分等数字。NUMERIC(p, s) 类型通常用于存储产品价格、销售金额等包含小数并且准确度要求高的数字。

近似数字

近似数字也被称为浮点型数字，一般较少使用，主要用于科学计算领域。浮点数的运算比普通数字类型更快，但是其可能丢失精度，从而导致非预期的结果。

其中，REAL 表示单精度浮点数，通常精确到小数点后 6 位。DOUBLE PRECISION 表示双精度浮点数，通常精确到小数点后 15 位。

Oracle 使用 BINARY_FLOAT 和 BINARY_DOUBLE 表示浮点型数字。

MySQL 使用 FLOAT 表示单精度浮点型数字，同时区分有符号浮点数（FLOAT）和无符号浮点数（FLOAT UNSIGNED）。

字符串类型

字符串类型用于存储文本数据，主要包含 3 种具体的类型：定长字符串、变长字符串以及字符串大对象。4 种主流数据库对于常用字符串类型的支持如下表所示。

提示：SQLite 默认使用动态数据类型，任何类型都可以存储字符串。如果使用了严格（STRICT）的数据类型，可以使用 TEXT 或者 ANY 类型存储字符串。

定长字符串

CHAR(n) 表示长度固定的字符串，其中 n 表示字符串的长度。CHARACTER 和 CHAR 是同义词。定长字符串数据类型的常见定义方式如下：

对于定长字符串类型，如果输入的字符串长度不够，数据库将会使用空格进行填充。例如，对于数据类型为 CHAR(5)的字段，如果输入值为“A”，实际存储的内容为“A ”，也就是一个字符“A”加上 4 个空格。当我们在查询条件中使用这种字段的值进行比较时，数据库会将字符串右侧的空格截断后再参与比较。

通常只有存储固定长度的字符串时我们才需要考虑使用定长字符串类型，比如 18 位身份证号或者 6 位邮政编码等。

变长字符串

VARCHAR(n) 表示长度不固定的字符串，其中 n 表示允许存储的最大长度。CHARACTER VARYING 和 CHAR VARYING 都是 VARCHAR 的同义词。

对于变长字符串类型，如果输入的字符串长度不够，数据库不会使用空格进行填充。例如，对于数据类型为 VARCHAR(5)的字段，如果输入值为“A”，实际存储的内容为“A”。

Oracle 使用 VARCHAR2 表示变长字符串类型，虽然目前 VARCHAR 是 VARCHAR2 的同义词，但是将来其会被定义为一种新的数据类型。

变长字符串类型一般用于存储长度不固定的文本，比如姓名、电子邮箱、产品描述等。

字符串大对象

CLOB 表示字符串大对象（Character Large Object），用于存储普通字符串类型无法支持的大型文本数据，比如整篇文章、备注、评论等内容。CHARACTER LARGE OBJECT 和 CHAR LARGE OBJECT 都是 CLOB 的同义词。

MySQL 提供了 TINYTEXT、TEXT、MEDIUMTEXT 以及 LONGTEXT，分别用于存储不同长度的文本数据。

Microsoft SQL Server 使用 VARCHAR( MAX )存储大文本数据。

PostgreSQL 使用 TEXT 类型存储任意长度的字符串数据。

日期时间类型

SQL 中与日期和时间相关的数据类型主要包括以下 3 种：

4 种主流数据库对于常用字符串类型的支持如下表所示。

提示：SQLite 不支持原生的日期时间类型，可以将日期时间存储为数字或者字符串。

Oracle 中的 DATE 类型包含了额外的时、分、秒信息，其不支持 TIME 类型。

MySQL 中的 DATETIME 也表示时间戳类型，选择时需要注意它和 TIMESTAMP 之间的区别。

Microsoft SQL Server 使用 DATETIME2 表示时间戳类型。另外，它也支持 TIMESTAMP 类型，但这是一个 rowversion 数据类型的同义词，和时间戳无关。

某些数据库中的 TIME 和 TIMESTAMP 还支持 WITH TIME ZONE 选项，用于指定一个时区偏移量。例如，UTC（协调世界时）标准时间的 0 点相当于北京时间的早上 8 点。时区选项通常用在支持全球化的应用系统中。

二进制类型

二进制数据类型用于存储二进制文件，比如文档、图片，视频等。SQL 二进制类型具体包含以下 3 种形式：

Oracle 支持 BLOB 二进制类型。

MySQL 提供了 BINARY、VARBINARY 以及 TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB 等二进制类型。

Microsoft SQL Server 支持 BINARY、VARBINARY 以及 VARBINARY(MAX)二进制类型。

PostgreSQL 支持 BYTEA 二进制类型。

SQLite 默认使用动态数据类型，任何类型都可以存储二进制数据。如果使用了严格（STRICT）的数据类型，可以使用 BLOB 或者 ANY 类型存储二进制数据。

选择合适的数据类型

我们在选择字段的数据类型时，首先应该满足存储业务数据的要求，其次还需要考虑性能和使用的便捷性。一般来说，我们可以先确定基本的类型：

然后，我们进一步确定具体的数据类型。

在满足数据存储和扩展的前提下，尽量使用更小的数据类型。这样可以节省一些存储，通常性能也会更好。例如，对于一个小型公司而言，员工人数通常不会超过几百，可以使用 SMALLINT 类型存储员工编号。对于 MySQL 而言，如果无须支持负数，就可以考虑使用无符号的数字类型。

如果我们需要存储精确的数字，要避免使用浮点数字类型。例如，与财务相关的数据，我们应该使用 NUMERIC(p, s)数据类型。另外，我们也可以将数值乘以 10 的 N 次方进行存储，比如将 10.35 存储为整数 103 500，然后在应用程序中进行处理和转换显示。

对于字符串数据，优先使用 VARCHAR 类型。如果字符串的长度固定，我们可以考虑使用 CHAR 类型。另外，只有在普通字符串类型的长度无法满足需求时，才会考虑使用大字段类型。

不建议使用字符串存储日期时间数据，因为它们无法支持数据的运算，比如返回两个日期之间的时间间隔。另外，最好也不要使用当前时间距离 1970 年 1 月 1 日的毫秒数来表示时间，因为这种方式在显示时需要进行额外的转换。

此外，如果一个字段同时出现在多个表中，我们应该使用相同的数据类型。例如，员工表中的部门编号（dept_id）字段与部门表的编号（dept_id）字段应该保持名称和类型一致。

文章来源:https://blog.csdn.net/horses/article/details/139355694