SQL Datatypes 相容性整理

我只整理數值、序號、文字和日期這四種類型。至於 BINARY, BIT, MONEY, 地理位置, XML 等通用性太低的類型，則不使用。

下列是個人使用的資料庫系統，按個人喜好程度排列：

• SQLite 3 (sqlite)
• PostgreSQL 9.6 或更新版本 (pgsql)
• SQL Server 2019 或更新版本 (mssql)
• MariaDB 10.3 或更新版本 (mysql)

數值

高相容性。

INTEGER, INT

INTEGER 和 INT 是同義字。精準的整數數值。

INTEGER 一般使用 4 bytes 儲存容量，有效數值介於 -2147483648 到 2147483647 之間。 超過此區間的整數字面，通常會被 DBMS 轉型為 NUMERIC 型態，而不是 INTEGER 型態。

SAMLLINT 使用 2 bytes 儲存容量； BIGINT 使用 8 bytes 儲存容量。

• sqlite 依數值自動調整 INTEGER 型態的儲存容量，介於 1 ~ 8 bytes 間。

NUMERIC, DECIMAL

NUMERIC 和 DECIMAL 是同義字。適用於帶小數點的精準數值，或比 INTEGER 更大的整數。

NUMERIC/DECIMAL 可用引數:

• precision 包含小數點左右兩側數目的有效位數。每家 DBMS 的最大範圍不同。省略 precision 引數時，各家預設也不同。
• scale 小數位數。

NUMERIC(precision, scale)

-- 省略小數位數。SQL 標準預設是 0 。
NUMERIC(precision)

-- 使用此 DBMS 的最大有效位數。
NUMERIC

• mssql 省略 precision 引數時，預設為 18 。其他 DBMS 未說明。
• pgsql 省略 scale 引數時，預設為小數位數的上限，而不是 0 。

REAL, FLOAT

REAL 和 FLOAT 用於儲存浮點近似數值。兩者不是同義字。差別在 REAL 沒有引數；而 FLOAT 接受一個引數，用於指定有效位數。

REAL 按 SQL 標準是單精度浮點數(single-precision)。有效數值介於 - 3.40E + 38 到 -1.18E - 38、0 及 1.18E - 38 到 3.40E + 38 。

FLOAT 可用引數:

• precision 有效位數。範圍是 1 到 53 。SQL 標準預設為 53 。

按照 SQL 標準，FLOAT(24) 佔 4 bytes 儲存容量，與 REAL 同義。 FLOAT(53) 佔 8 bytes 儲存容量，與 DOUBLE PRECISION 同義。

-- 有效位數。範圍是 1 到 53 。
FLOAT(precision)

-- 省略有效位數。與 DOUBLE PRECISION 同義。
FLOAT

• sqlite 的 REAL 佔 8 bytes 儲存容量，與 FLOAT(53)/DOUBLE PRECISION 同義。
• mysql 的 REAL 與 FLOAT(53) 同義。設定 sql_mode 為 ansi 時，REAL 才與 FLOAT(24) 同義。
• mysql 的 FLOAT 省略引數時，預設為 FLOAT(24) ，而不是 FLOAT(53) 。

序號

不具相容性。

sqlite 自動建立為表格中每一筆資料行建立 rowid/oid/_rowid_ ，除非表格宣告不用 (WITHOUT ROWID)。 若表格欄位的型態是 INTEGER PRIMARY KEY ，則此欄位自動代替 rowid/oid 成為資料行序號。 注意，sqlite 不會將 INT PRIMARY KEY 視為 INTEGER PRIMARY KEY 。

-- sqlite
CREATE TABLE s (v INTEGER);
INSERT INTO s VALUES (11), (22);
SELECT rowid,* FROM s;
-- select 的欄位清單必須明確寫出 rowid ，才會包含 rowid 內容。

CREATE TABLE s2 (id INTEGER PRIMARY KEY, v INTEGER);
INSERT INTO s2 (v) VALUES (33), (44);
SELECT * FROM s2;

pgsql 使用 SERIAL 資料型態產生自動序號(4 bytes)。需要更大範圍的序號時，可用 BIGSERIAL 資料型態 (8 bytes)。

mssql 使用 IDENTITY 資料型態屬性產生序號，通常配合 INTEGER 或 BIGINT 資料型態。例如:

-- mssql
CREATE TABLE t1 (id INTEGER IDENTITY);

mysql 使用 AUTO_INCREMENT 資料型態屬性產生序號。它也提供 SERIAL 資料型態，等同於 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE。例如:

-- mysql
CREATE TABLE t1 (id SERIAL);
-- 等於
CREATE TABLE t1 (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE);

字元、文字

高相容性。

CHAR 和 CHARACTER 是同義字。VARCHAR 和 CHARACTER VARYING 是同義字。

CHAR 和 VARCHAR 都接受最大長度的引數，省略時為 1。不同 DBMS 的最大儲存容量 (最大長度限制) 並不相同。

按現今 DBMS 普遍支援 UTF-8 編碼的情況，似乎沒有必要使用 NCHAR/NVARCHAR 。

sqlite 的文字

sqlite 忽視長度引數，也就是它沒有長度限制。所以 VARCHAR 等於 TEXT 。

pgsql 的文字

• 最大可用儲存容量大約為 1 GB。
• 這三種型別之間並沒有效能差異。事實上，由於額外的儲存成本，CHARACTER(n) 通常是三者中最慢的。在大多數情況下，應使用 TEXT 或 CHARACTER VARYING。

mssql 的文字

• VARCHAR 最大長度為 8000 。超過此長度則應指定 VARCHAR(max) 。
• 未來版本計劃移除 TEXT 型態，改用 VARCHAR (max) 取代。
• SQL Server 2019 起，CHAR/VARCHAR 可指定 UTF-8 encoding 。

mysql 的文字

• CHAR 最長 255 。
• VARCHAR 最大儲存容量 64KB ，所以用 UTF-8 編碼時，可指定的最大字元長度小於 21844。超過此限制時，應改用 MEDIUMTEXT。
• TEXT 最大儲存容量和 VARCHAR 一樣。早先的版本不能對 TEXT 型態的欄位建立全文索引。

日期、時間

低相容性。

日期和時間的型態與定義在 DBMS 差異很大。但所有 DBMS 都承認 ISO-8601 的表達格式。如下列所示：

• 日期: yyyy-mm-dd 。例如 2021-07-14 (西元2021年7月14日)。
• 時間: hh:mm:ss[.fff] 。24小時制。例如 01:23:45 (上午1時23分45秒) 或 13:23:45.678 (下午1時23分45秒又678毫秒)。
• 時區: 在時間後加上正負時數。例如台北時間 +08:00 。
• 日期和時間: dateTtime ，’T’ 是分隔字元。例如 2021-07-14T01:23:45 。有些 DBMS 接受用空格分隔日期和時間。

如果你不打算讓 DBMS 進行日期時間運算 (例如搜尋自某日起一週的資料) ，可以考慮用 CHAR(n) 或 TEXT 儲存日期和時間。 sqlite 就是這麼做的。

-- pgsql
CREATE TABLE dt (d DATE, t TIME, dt TIMESTAMP);

INSERT INTO dt VALUES (
    '2021-07-14', 
    '01:23:45.678', 
    '2021-07-14T01:23:45');

sqlite 的日期與時間

sqlite 接受 DATE, DATETIME 等型態名稱，但沒有對應的儲存類別。實務上是用 TEXT 或 INTEGER 儲存，再用它內建的日期與時間函數計算。

DATE, TIME

DATE 儲存日期，TIME 儲存時間。一般不包含時區。

DATETIME, TIMESTAMP

當你想在一個欄位中同時儲存日期和時間，各 DBMS 給的資料型態名稱都不一樣。

pgsql 是 TIMESTAMP ，mssql 是 DATETIME2 ，mysql 則是 DATETIME 。

mysql 有 TIMESTAMP 型態，但這是自動產生資料，不可修改。

參考來源

• SQLite: Datatypes In SQLite Version 3
• PostgreSQL: Chapter 8. Data Types
• SQL Server: T-SQL 參考
• MariaDB: Data Types in MariaDB