PostgreSql
在 **PostgreSQL** 中，`citext` 是一个内置的扩展模块，提供了 **不区分大小写的文本类型**，即 `citext`（全称：case-insensitive text）。
使用 `citext` 类型存储字符串时，**比较操作（如 `=`、`LIKE` 等）会忽略字母的大小写**，非常适用于用户名、邮箱等**不区分大小写**的业务字段。
---
## ✅ 一、如何在 PostgreSQL 中使用 citext
### 步骤 1：启用 `citext` 扩展
在使用 `citext` 类型之前，**必须先启用该扩展**，因为它是 PostgreSQL 的一个 **可选扩展模块**，不是默认可用的数据类型。
使用以下 SQL 命令启用：
```sql
CREATE EXTENSION IF NOT EXISTS citext;
```
> ✅ 通常在数据库初始化后执行一次即可，不需要每次都执行。
你可以在 `psql` 中运行，或者在你的应用初始化脚本中加入这句。
---
### 步骤 2：在表中使用 `citext` 类型
启用扩展后，你就可以像使用普通的 `text` 或 `varchar` 一样，使用 `citext` 类型来定义列。
#### 示例：创建一个用户表，使用 `citext` 存储不区分大小写的用户名和邮箱
```sql
CREATE TABLE users (
id serial PRIMARY KEY,
username citext UNIQUE NOT NULL,
email citext UNIQUE NOT NULL,
created_at timestamp DEFAULT now()
);
```
> ✅ 这里的 `username` 和 `email` 都是 **不区分大小写** 的，例如 `'Alice'` 和 `'alice'` 会被视为相等。
---
### 步骤 3：插入和查询数据（大小写不敏感）
#### 插入数据：
```sql
INSERT INTO users (username, email) VALUES ('Alice', 'alice@example.com');
```
#### 查询时，大小写不敏感：
```sql
SELECT * FROM users WHERE username = 'alice'; -- 可以查到
SELECT * FROM users WHERE username = 'ALICE'; -- 也可以查到
SELECT * FROM users WHERE username = 'Alice'; -- 当然也可以
```
> ✅ 即使你插入的是 `'Alice'`，用 `'alice'` 或 `'ALICE'` 去查询，**都能匹配到同一条记录**，因为 `citext` 在比较时自动忽略大小写。
---
### 步骤 4：唯一约束（UNIQUE）也支持不区分大小写
由于 `citext` 的比较是大小写不敏感的，所以如果你对 `citext` 字段加了 `UNIQUE` 约束，比如：
```sql
username citext UNIQUE
```
那么：
- `'Alice'`
- `'alice'`
- `'ALICE'`
这些值都会被视为 **相同**，**插入第二个时会违反唯一约束**，从而报错。
> ✅ 这正是我们期望的行为：业务上用户名不区分大小写，那么 `'Alice'` 和 `'alice'` 应该算同一个用户。
---
## ✅ 二、其他注意事项
### 1. `citext` 是一种数据类型，不是函数
- 不是 `LOWER(username) = LOWER('input')` 这种手动处理的方式。
- `citext` 是 PostgreSQL 内置的 **数据类型**，内部已经实现了不区分大小写的比较逻辑，性能和易用性更好。
### 2. 可以像普通文本类型一样使用
`citext` 支持所有常规的文本操作，比如：
- `=`, `!=`, `LIKE`, `ILIKE`（虽然 `ILIKE` 本来就是不区分大小写的，和 `citext` 的 `=` 效果类似）
- 长度函数：`length()`, `substring()` 等
- 可以索引：支持 B-tree 等索引优化
### 3. 适合哪些字段？
推荐在以下业务字段使用 `citext`：
- 用户名（username）
- 邮箱（email）
- 标签名、角色名等 **需要不区分大小写比较且唯一** 的字段
---
## ✅ 三、示例完整流程
### 1. 创建数据库并启用 citext
```sql
CREATE DATABASE test_ci;
\c test_ci
CREATE EXTENSION IF NOT EXISTS citext;
```
### 2. 创建使用 citext 的表
```sql
CREATE TABLE users (
id serial PRIMARY KEY,
username citext UNIQUE NOT NULL,
email citext UNIQUE NOT NULL,
created_at timestamp DEFAULT now()
);
```
### 3. 插入数据
```sql
INSERT INTO users (username, email) VALUES ('JohnDoe', 'johndoe@example.com');
```
### 4. 查询（大小写不敏感）
```sql
SELECT * FROM users WHERE username = 'johndoe'; -- 可以查到
SELECT * FROM users WHERE username = 'JOHNDOE'; -- 也可以查到
```
---
## ✅ 四、总结（简明中文）
| 项目 | 说明 |
|------|------|
| **citext 是什么？** | PostgreSQL 提供的一个 **不区分大小写的文本类型**，用于存储字符串但比较时不区分大小写 |
| **如何启用？** | 执行 SQL：`CREATE EXTENSION IF NOT EXISTS citext;` |
| **如何使用？** | 定义字段时用 `citext` 类型，如：`username citext` |
| **有什么好处？** | 不需要手动调用 `LOWER()`，查询时 `= 'abc'` 和 `= 'ABC'` 效果一样，适合用户名、邮箱等业务字段 |
| **支持唯一约束吗？** | 支持，且唯一性比较也是不区分大小写的，`'ABC'` 和 `'abc'` 会被认为是重复的 |
| **性能如何？** | 底层优化良好，支持索引，性能接近普通 `text` 类型 |