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')
);

From there, you can refer to an Enum using one of its string values:

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 文章
频道条目
分享
文章归档