Navicat 部落格

在 MySQL 和 PostgreSQL 中儲存三元資料 2021 年 12 月 8 日,由 Robert Gravelle 撰寫

在軟體開發中,有一種用於處理二進位狀態的布林(Boolean)資料類型。因此,它只有兩種可能的狀態:true 和 false。但是,在開發中存在必須經常考慮的第三種狀態,即「以上都不是」或「其他」。在關聯式資料庫中,NULL 似乎是這種狀態的一個很好的選擇,但由於其歷史背景而並非如此。回想一下以前的文章,NULL 在結構化查詢語言(SQL)中具有非常特殊的含義,表示資料庫中不存在數據值。NULL 值實際上是由關聯式資料庫模型的創造者 E. F. Codd 本人引入的。在 SQL 中,NULL 表示「缺少和/或不適用的資訊」。從這個角度來看,NULL 很難代表「以上都不是」或「其他」條件。那麼,在關式資料庫中表示三元或三態資料的最佳方式是什麼?我們今天將在這裡為 MySQL 和 PostgreSQL 回答這個問題。下週我們將涵蓋 SQL Server 和 Oracle。

介紹列舉類型

列舉類型(Enumerated Type),也稱為 Enum,是包含一組靜態、有序值的資料類型。Enum 非常適合儲存諸如星期幾、使用者偏好以及任何其他很少變更的相關資料集合。幾十年來,已經有許多程式設計語言支援 Enum。而一些最大的關聯式資料庫(包括 MySQL 和 PostgreSQL)也引入了 Enum 類型。但可惜的是,有一些資料庫(包括 SQL Server 和 Oracle)堅持不支援,我們將在下週討論。

在 MySQL 中建立和使用 Enum

若要了解如何使用 Enum,我們可以從世界上排名第一的關聯式資料庫開始。是的,我說的是 MySQL。正如你在以下 CREATE TABLE 陳述式中所見,將欄指定為 Enum 類型非常簡單:

CREATE TABLE shirts (
  name VARCHAR(40),
  size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);

然後,你可以使用 Enum 的一個字串值來引用它:

INSERT INTO shirts (name, size) 
VALUES ('dress shirt','large'), 
       ('t-shirt','medium'),
       ('polo shirt','small');
  
SELECT name, size FROM shirts WHERE size = 'medium';

UPDATE shirts SET size = 'small' WHERE size = 'large';

關於三態問題,我們可以如下實作:

CREATE TABLE employee (
  name VARCHAR(50),
  security_clearance ENUM('enhanced', 'secret', 'none')
);

現在,嘗試在 Enum 欄插入一個無效值,這將導致錯誤並失敗:

enum_error (33K)

在 PostgreSQL 中建立和使用 Enum

在 PostgreSQL 中,Enum 類型是使用 CREATE TYPE 命令建立的:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

一旦建立了,Enum 類型就可以像任何其他類型一樣在資料表中使用:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)

總結

在今天的文章中,我們看到了如何使用枚舉類型在 MySQL 和 PostgreSQL 中表示三態資料和其他複雜的值。但是其他資料庫類型呢?他們不支援三態資料嗎?他們是支援的,但使用不同的資料類型。我們將在下週探索這些。

Navicat 文章
頻道記錄
分享
部落格封存檔