SQL 数据库数据类型

SQL 数据类型概览

为表中的每一列选择正确的数据类型对于数据完整性、存储效率和性能至关重要。虽然 SQL 定义了标准数据类型，但具体的实现和可用类型在不同的数据库管理系统 (DBMS - Database Management System) 之间可能有所差异。本指南概述了 PostgreSQL、MySQL 和 SQL Server 等流行系统中的常见数据类型。

SQL 数据类型的通用类别

大多数 SQL 数据类型可归入以下几大类：

字符/字符串类型 (Character Strings)： 用于文本数据（例如，姓名、描述）。可以是固定长度（CHAR）或可变长度（VARCHAR，TEXT）。
数值类型 (Numeric Types)： 用于存储数字。可以是精确数值（例如，INTEGER，DECIMAL）或近似数值（例如，FLOAT，REAL）。
日期和时间类型 (Date and Time Types)： 用于存储日期、时间、时间戳和时间间隔。
布尔类型 (Boolean Type)： 用于存储真/假值。
二进制类型 (Binary Types)： 用于存储二进制数据（例如，图像、文件）。
特殊类型 (Special Types)： 包括 JSON、XML、UUID、数组、空间数据类型等，具体取决于 DBMS。

PostgreSQL 数据类型（常见示例）

PostgreSQL 提供了一系列丰富的、与 SQL 标准高度一致的数据类型。

字符类型：

Data Type	Description
`VARCHAR(n)`	Variable-length string with a limit of n characters.
`CHAR(n)`	Fixed-length string, blank-padded to n characters.
`TEXT`	Variable-length string of (effectively) unlimited length.

数值类型：

Data Type	Description
`SMALLINT`	2-byte signed integer.
`INTEGER` or `INT`	4-byte signed integer.
`BIGINT`	8-byte signed integer.
`DECIMAL(p,s)` or `NUMERIC(p,s)`	Exact numeric type with user-defined precision (p) and scale (s).
`REAL`	4-byte single-precision floating-point number.
`DOUBLE PRECISION`	8-byte double-precision floating-point number.
`SERIAL`	Auto-incrementing 4-byte integer (for primary keys).
`BIGSERIAL`	Auto-incrementing 8-byte integer (for primary keys).

日期/时间类型：

Data Type	Description
`DATE`	Stores date (year, month, day).
`TIME [WITHOUT TIME ZONE]`	Stores time of day.
`TIME WITH TIME ZONE`	Stores time of day, with time zone.
`TIMESTAMP [WITHOUT TIME ZONE]`	Stores date and time.
`TIMESTAMP WITH TIME ZONE`	Stores date and time, with time zone (converted to UTC for storage).

布尔类型：

Data Type	Description
`BOOLEAN`	Stores `TRUE`, `FALSE`, or `NULL`.

其他常见类型：

Data Type	Description
`UUID`	Universally Unique Identifier.
`JSON`, `JSONB`	For storing JSON data (`JSONB` is generally preferred for indexing and functions).
`BYTEA`	Variable-length binary string.

MySQL 数据类型（常见示例）

MySQL 提供了一系列数据类型，并带有一些特定的行为。

文本类型：

Data Type	Description (Max length/bytes depends on character set)
`CHAR(size)`	Fixed-length string, 0 to 255 characters.
`VARCHAR(size)`	Variable-length string, 0 to 65,535 bytes total for row.
`TINYTEXT`	Max 255 bytes.
`TEXT`	Max 65,535 bytes.
`MEDIUMTEXT`	Max 16,777,215 bytes.
`LONGTEXT`	Max 4,294,967,295 bytes.
`ENUM(...)`	A string object that can have only one value, chosen from a list.
`SET(...)`	A string object that can have zero or more values, chosen from a list.

数值类型：

Data Type	Description (UNSIGNED option available for non-negative numbers)
`TINYINT`	1-byte integer.
`SMALLINT`	2-byte integer.
`MEDIUMINT`	3-byte integer.
`INT` or `INTEGER`	4-byte integer.
`BIGINT`	8-byte integer.
`FLOAT(p,s)`	Single-precision floating-point. Precision optional.
`DOUBLE(p,s)`	Double-precision floating-point. Precision optional.
`DECIMAL(p,s)` or `NUMERIC(p,s)`	Fixed-point number. `p` is total digits, `s` is digits after decimal.

日期和时间类型：

Data Type	Description
`DATE`	’YYYY-MM-DD’ format. Range ‘1000-01-01’ to ‘9999-12-31’.
`DATETIME(fsp)`	’YYYY-MM-DD HH:MM:SS[.fraction]’ format. Optional fractional seconds precision (fsp).
`TIMESTAMP(fsp)`	’YYYY-MM-DD HH:MM:SS[.fraction]’ format. Range ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC. Supports automatic initialization/update to current time.
`TIME(fsp)`	’HH:MM:SS[.fraction]’ format.
`YEAR`	Year in 4-digit format (YYYY). Range 1901 to 2155.

其他常见类型：

Data Type	Description
`BLOB` family	For binary large objects (`TINYBLOB`, `BLOB`, `MEDIUMBLOB`, `LONGBLOB`).
`JSON`	Native JSON data type for storing and manipulating JSON documents.

SQL Server 数据类型（常见示例）

SQL Server 提供了一份全面的数据类型列表。

字符串类型：

Data Type	Description (Storage depends on actual length)
`CHAR(n)`	Fixed-length non-Unicode characters. Max 8,000 characters.
`VARCHAR(n)`	Variable-length non-Unicode characters. Max 8,000 characters.
`VARCHAR(MAX)`	Variable-length non-Unicode characters. Max 2^31-1 bytes (2 GB).
`NCHAR(n)`	Fixed-length Unicode characters. Max 4,000 characters.
`NVARCHAR(n)`	Variable-length Unicode characters. Max 4,000 characters.
`NVARCHAR(MAX)`	Variable-length Unicode characters. Max 2^30-1 characters (2 GB).
Deprecated: `TEXT`, `NTEXT` (use `VARCHAR(MAX)`, `NVARCHAR(MAX)` instead).

数值类型：

Data Type	Description
`BIT`	Integer data with a value of 0, 1, or NULL.
`TINYINT`	Integer data from 0 through 255.
`SMALLINT`	Integer data from -32,768 through 32,767.
`INT`	Integer data from -2,147,483,648 through 2,147,483,647.
`BIGINT`	Integer data from -2^63 through 2^63-1.
`DECIMAL(p,s)` or `NUMERIC(p,s)`	Fixed precision (p) and scale (s) numbers.
`SMALLMONEY`	Monetary data from -214,748.3648 to 214,748.3647.
`MONEY`	Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
`FLOAT(n)`	Approximate-number data values. `n` specifies precision (1-53).
`REAL`	Approximate-number data values (equivalent to `FLOAT(24)`).

日期和时间类型：

Data Type	Description
`DATE`	Stores a date (YYYY-MM-DD). Range 0001-01-01 through 9999-12-31.
`TIME(n)`	Stores a time (hh:mm:ss.fractional_seconds). Precision `n` from 0 to 7.
`DATETIME2(n)`	Stores date and time with fractional seconds. Precision `n` from 0 to 7. Preferred over `DATETIME`.
`DATETIMEOFFSET(n)`	Date and time with time zone awareness.
`SMALLDATETIME`	Date and time, accuracy to the minute. Range 1900-01-01 through 2079-06-06.
`DATETIME`	Older date and time type. Range 1753-01-01 through 9999-12-31.

其他数据类型：

Data Type	Description
`BINARY(n)`	Fixed-length binary data.
`VARBINARY(n)`	Variable-length binary data.
`VARBINARY(MAX)`	Variable-length binary data. Max 2 GB. Replaces `IMAGE`.
`UNIQUEIDENTIFIER`	Stores a globally unique identifier (GUID).
`XML`	Stores XML data. Max 2 GB.
`ROWVERSION` (formerly `TIMESTAMP`)	Database-generated unique binary number. Used for version-stamping rows. Not a date/time value.

Microsoft Access 数据类型（简述）：

虽然在新的、大规模开发中较少使用，但 MS Access 类型包括 Short Text（以前称 Text，最多 255 字符）、Long Text（以前称 Memo）、Number（Byte、Integer、Long Integer、Single、Double、Decimal）、Date/Time、Currency、AutoNumber、Yes/No（Boolean）、OLE Object、Hyperlink。现代最佳实践建议对于大型应用迁移到更健壮的服务器端 RDBMS。

选择正确的数据类型：

数据性质： 选择能准确表示数据性质的类型（例如，如果需要计算，不要使用文本类型存储数字）。
值范围： 确保所选类型可以容纳列的所有可能值。
精度： 对于数值类型，选择适当的精度（precision）和标度（scale）。
存储空间： 使用能可靠存储数据的最小数据类型，以节省空间并提高性能。
性能： 正确的数据类型会影响查询性能，特别是对于连接（joins）和索引（indexing）。
未来需求： 考虑潜在的未来需求，但避免过度指定（over-specifying）。

请始终参考您特定数据库系统版本的官方文档，以获取关于数据类型最准确和详细的信息。