字符串存储类型选择
PostgreSQL 支持几种字符类型：
| 类型 | 说明 | 特点 | 适用场景 | 优缺点 |
|---|---|---|---|---|
| char(n) | 固定长度 n 字符 | 不够会补空格 | 适合固定长度字段，如性别、状态码 | 优点：占用固定长度，检索快；缺点：空间浪费，操作需 TRIM |
| varchar(n) | 可变长度，长度上限 n | 实际存储长度 + 1~4 字节 | 常规短文本，如用户名、邮箱 | 优点：节省空间，长度可控；缺点：长度受限，超长报错 |
| text | 可变长度，无上限 | 任意长度文本 | 长文本字段，如文章内容、日志 | 优点：无限制；缺点：部分函数可能不能使用索引（B-Tree 默认可，LIKE ‘%xxx%’ 无索引） |
实践建议：
- 标题、用户名、状态字段 -> varchar(n)
- 内容、描述、日志 -> text
- 避免用 char(n)，除非业务明确固定长度（比如状态码、国别码）
MySQL 常用字符类型：
| 类型 | 说明 | 特点 | 适用场景 | 优缺点 |
|---|---|---|---|---|
| CHAR(n) | 固定长度 n 字节 | 不够补空格 | 性别、状态码 | 优点：检索快；缺点：空间浪费 |
| VARCHAR(n) | 可变长度 n 字节 | 实际长度 + 1~2 字节 | 常规短文本 | 优点：节省空间，支持索引，兼容性好；缺点：超长报错 |
| TEXT / TINYTEXT / MEDIUMTEXT / LONGTEXT | 大文本 | LOB 存储 | 内容、日志 | 优点：可存储大文本；缺点：索引有限制，写入稍慢 |
实践建议：
- 用户名、email、code -> VARCHAR(n)
- 描述、文章、日志 -> TEXT
- 少用 CHAR(n)，只适合小固定字段
| 特性 | PostgreSQL | MySQL | 建议 |
|---|---|---|---|
| 可变长度短文本 | varchar(n) | VARCHAR(n) | 二者类似 |
| 无长度限制长文本 | text | TEXT / MEDIUMTEXT | PostgreSQL 更统一，MySQL 有多种 TEXT 类型，需根据大小选择 |
| 索引支持 | varchar 与 text 默认支持 B-Tree，但 text LIKE ‘%xxx%’ 不走索引 | VARCHAR 可建索引，TEXT 默认只能索引前 255 字节 | 短文本建 varchar，长文本建 text/mediumtext |
| 空间消耗 | char(n) 补空格，varchar(n) 与 text 按长度存 | 同 PostgreSQL | 尽量少用 char，varchar 可控 |
| 查询性能 | varchar + 索引性能高，text 查询大文本慢 | 同 PostgreSQL | 长文本尽量避免频繁 LIKE ‘%xxx%’ |
-
短文本字段（长度可控）
- PostgreSQL: varchar(n)
- MySQL: VARCHAR(n)
- 例：用户名、邮箱、股票代码
-
长文本字段（长度不固定）
- PostgreSQL: text
- MySQL: TEXT / MEDIUMTEXT（根据最大长度选择）
- 例：文章内容、日志、描述字段
-
固定长度字段（仅少量使用）
- PostgreSQL / MySQL: char(n)
- 例：状态码（“A”/“B”/“C”）、国别码（“CN”、“US”）
-
索引考虑
- 短文本字段 -> 可加 B-Tree 索引
- 长文本 -> 避免 LIKE ‘%xxx%’，用 GIN / trigram / fulltext 索引
- 短文本 -> varchar(n)（受长度控制 + 支持索引）
- 长文本 -> text（无限制 + 灵活，但索引受限）
- 固定长度 -> char(n)（少用，空间固定但浪费）
- PostgreSQL text/varchar 可以存储任意长度；MySQL TEXT 需选合适类型（TINY / MEDIUM / LONG）
- 索引策略必须结合字段类型考虑
需求：
- 文章标题
- 长度不确定，但通常在某个长度范围内
- 经常做模糊查询 LIKE ‘%xxx%’
这在 PostgreSQL 和 MySQL 下处理方式略有不同，因为原生 B-Tree 索引无法高效支持前置 % 的模糊查询。
- text 或 varchar(n)（长度可控，text 更灵活）
- 因为文章标题长度不固定，text 更方便
- varchar(n) 也可以，如果你想限制最大长度
- B-Tree 无法加速 LIKE ‘%xxx%’
- 推荐使用 pg_trgm 扩展 + GIN 或 GiST 索引
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_trgm;
-- 创建 trigram GIN 索引
CREATE INDEX idx_articles_title_trgm ON articles USING GIN (title gin_trgm_ops);
查询：
SELECT *
FROM articles
WHERE title LIKE '%关键词%';
性能会比全表扫描快很多（百万级数据也可接受）。
- VARCHAR(n) 或 TEXT
- VARCHAR(n)：如果标题长度有最大值（如 500），推荐
- TEXT：长度不确定且可能很长
- B-Tree 索引只能加速前缀匹配：LIKE ‘xxx%’
- LIKE ‘%xxx%’ 无法用 B-Tree
- 推荐使用 FULLTEXT 索引（MySQL 5.6+ 支持 InnoDB）
-- VARCHAR 类型示例
ALTER TABLE articles ADD FULLTEXT INDEX ft_title(title);
-- 查询
SELECT *
FROM articles
WHERE MATCH(title) AGAINST('关键词');
FULLTEXT 支持自然语言搜索，性能优于 LIKE ‘%xxx%’。
如果需要严格 LIKE 模糊匹配，也可以考虑将关键字拆分到另一个列存储或者使用 ElasticSearch。
| 特性 | PostgreSQL | MySQL |
|---|---|---|
| 存储类型 | text 或 varchar(n) | VARCHAR(n) 或 TEXT |
| 模糊搜索优化 | pg_trgm + GIN/GiST | FULLTEXT 索引 |
| LIKE ‘%xxx%’ | 不走 B-Tree，需 trigram | 不走 B-Tree，需 FULLTEXT |
| 数据量 | 支持百万以上 | 支持百万以上 |
| 适合场景 | 灵活、复杂查询 | 常规搜索、简单匹配 |
- PostgreSQL：text + pg_trgm GIN 索引
- MySQL：VARCHAR(500) + FULLTEXT
都可以满足标题长度不确定 + 高频模糊搜索的需求，同时保证查询性能。