PostgreSQL教程FG042-PG字符类型：CHAR/VARCHAR/TEXT详解与性能对比

本文档风哥主要介绍PostgreSQL教程042相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

1. 字符类型概述

PostgreSQL提供了三种主要字符类型：CHAR(n)、VARCHAR(n)和TEXT。它们在存储方式、性能特点和适用场景上各有不同。

提示：在PostgreSQL中，TEXT类型通常是最优选择，因为它没有长度限制且性能与VARCHAR相当。

— 查看字符类型对比
SELECT
‘CHAR(n)’ AS type_name,
‘定长字符串，不足补空格’ AS description,
‘最大1GB’ AS max_size,
‘固定长度编码、存储代码等’ AS use_case
UNION ALL
SELECT
‘VARCHAR(n)’,
‘变长字符串，有长度限制’,
‘最大1GB’,
‘姓名、地址等有长度限制的字段’
UNION ALL
SELECT
‘TEXT’,
‘变长字符串，无长度限制’,
‘最大1GB’,
‘文章内容、备注等长文本’;

执行结果：

from oracle:www.itpux.com

2. CHAR定长字符类型

CHAR(n)类型存储固定长度的字符串，不足长度时用空格填充。

— 创建CHAR类型测试表
CREATE TABLE fgedu_char_test (
id SERIAL PRIMARY KEY,
code CHAR(10),
fixed_phone CHAR(11),
id_number CHAR(18)
);

— 插入测试数据
INSERT INTO fgedu_char_test (code, fixed_phone, id_number) VALUES
(‘ABC’, ‘13800138000’, ‘110101199001011234’),
(‘XYZ123’, ‘02112345678’, ‘310101198512125678’),
(‘PQ’, ‘075588888888’, ‘440101200001018765’);

— 查询数据（注意空格填充）
SELECT id, code, LENGTH(code) AS code_len, fixed_phone, id_number FROM fgedu_char_test;

执行结果：

id | code | code_len | fixed_phone | id_number
—-+————+———-+—————–+————————
1 | ABC | 10 | 13800138000 | 110101199001011234
2 | XYZ123 | 10 | 02112345678 | 310101198512125678
3 | PQ | 10 | 075588888888 | 440101200001018765
(3 rows)

CHAR类型的空格处理：

— 测试CHAR类型的空格处理
SELECT
code,
code || ‘END’ AS concat_result,
TRIM(code) AS trimmed,
LENGTH(TRIM(code)) AS trimmed_len
FROM fgedu_char_test;

执行结果：

CHAR类型比较：

— CHAR类型比较（自动忽略尾部空格）
SELECT
code,
code = ‘ABC’ AS compare_abc,
code = ‘ABC ‘ AS compare_abc_spaces
FROM fgedu_char_test;

执行结果：

code | compare_abc | compare_abc_spaces
————+————-+——————–
ABC | t | t
XYZ123 | f | f
PQ | f | f
(3 rows)

3. VARCHAR变长字符类型

VARCHAR(n)类型存储可变长度字符串，n为最大字符数。

风哥提示：

— 创建VARCHAR类型测试表
CREATE TABLE fgedu_varchar_test (
id SERIAL PRIMARY KEY,
fgeduname VARCHAR(50),
email VARCHAR(100),
address VARCHAR(200)
);

— 插入测试数据
INSERT INTO fgedu_varchar_test (fgeduname, email, address) VALUES
(‘风哥1号’, ‘zhangsan@fgedu.net’, ‘北京市海淀区中关村大街1号’),
(‘风哥2号’, ‘lisi@fgedu.net.cn’, ‘上海市浦东新区陆家嘴金融中心’),
(‘王五’, ‘wangwu@fgedu.net’, ‘广州市天河区珠江新城’);

— 查询数据
SELECT * FROM fgedu_varchar_test;

执行结果：

学习交流加群风哥微信: itpux-com

测试VARCHAR长度限制：

— 测试VARCHAR长度限制
INSERT INTO fgedu_varchar_test (fgeduname, email) VALUES
(REPEAT(‘A’, 50), ‘test@fgedu.net’);

— 超过长度限制
INSERT INTO fgedu_varchar_test (fgeduname, email) VALUES
(REPEAT(‘A’, 51), ‘test@fgedu.net’);

执行结果：

INSERT 0 1
ERROR: value too long for type character varying(50)

VARCHAR无长度限制：

学习交流加群风哥QQ113257174

— VARCHAR无长度限制（等同于TEXT）
CREATE TABLE fgedu_varchar_unlimited (
id SERIAL PRIMARY KEY,
content VARCHAR
);

— 插入长文本
INSERT INTO fgedu_varchar_unlimited (content) VALUES
(REPEAT(‘测试内容’, 1000));

SELECT LENGTH(content) AS content_length FROM fgedu_varchar_unlimited;

执行结果：

content_length
—————-
4000
(1 row)

4. TEXT无限制文本类型

TEXT类型可以存储任意长度的字符串，是PostgreSQL中最灵活的字符类型。

— 创建TEXT类型测试表
CREATE TABLE fgedu_text_test (
id SERIAL PRIMARY KEY,
title VARCHAR(200),
content TEXT,
summary TEXT
);

— 插入文章数据
INSERT INTO fgedu_text_test (title, content, summary) VALUES
(‘PostgreSQL入门教程’,
‘PostgreSQL是一个功能强大的开源对象关系数据库系统。它拥有超过35年的开发历史，以其可靠性、功能健壮性和性能而闻名。PostgreSQL支持SQL标准的大部分特性，并提供了许多高级功能，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。’,
‘PostgreSQL开源数据库入门指南’),
(‘PostgreSQL性能优化’,
‘PostgreSQL性能优化涉及多个方面，包括硬件配置、操作系统调优、数据库参数配置、SQL语句优化、索引设计等。合理的配置和优化可以显著提升数据库性能，满足高并发、大数据量的业务需求。’,
‘数据库性能调优实战’);

— 查询数据
SELECT id, title, LENGTH(content) AS content_len, summary FROM fgedu_text_test;

执行结果：

TEXT类型的全文检索：

— 创建全文检索索引
CREATE INDEX idx_fgedu_text_content ON fgedu_text_test USING gin(to_tsvector(‘chinese’, content));

— 全文检索查询
SELECT
title,
ts_headline(‘chinese’, content, to_tsquery(‘chinese’, ‘PostgreSQL’)) AS highlight
FROM fgedu_text_test
WHERE to_tsvector(‘chinese’, content) @@ to_tsquery(‘chinese’, ‘PostgreSQL’);

执行结果：

title | highlight
————————+——————————————————————————————————————-
PostgreSQL入门教程 |
PostgreSQL是一个功能强大的开源对象关系数据库系统。它拥有超过35年的开发历史，以其可靠性、功能健壮性和性能而闻名。PostgreSQL支持SQL标准的大部分特性…
PostgreSQL性能优化 | PostgreSQL性能优化涉及多个方面，包括硬件配置、操作系统调优、数据库参数配置、SQL语句优化、索引设计等。合理的配置和优化可以显著提升数据库性能…
(2 rows)

5. 字符类型性能对比

测试三种字符类型的性能差异：

— 创建性能测试表
CREATE TABLE fgedu_perf_char (
id SERIAL PRIMARY KEY,
data CHAR(100)
);

CREATE TABLE fgedu_perf_varchar (
id SERIAL PRIMARY KEY,
data VARCHAR(100)
);

CREATE TABLE fgedu_perf_text (
id SERIAL PRIMARY KEY,
data TEXT
);

— 插入测试数据（各10000条）
INSERT INTO fgedu_perf_char (data)
SELECT ‘测试数据’ || i FROM generate_series(1, 10000) AS i;

INSERT INTO fgedu_perf_varchar (data)
SELECT ‘测试数据’ || i FROM generate_series(1, 10000) AS i;

INSERT INTO fgedu_perf_text (data)
SELECT ‘测试数据’ || i FROM generate_series(1, 10000) AS i;

执行结果：

INSERT 0 10000
INSERT 0 10000
INSERT 0 10000

比较存储空间：

— 比较存储空间
SELECT
‘fgedu_perf_char’ AS table_name,
pg_size_pretty(pg_total_relation_size(‘fgedu_perf_char’)) AS total_size,
pg_size_pretty(pg_relation_size(‘fgedu_perf_char’)) AS table_size
UNION ALL
SELECT
‘fgedu_perf_varchar’,
pg_size_pretty(pg_total_relation_size(‘fgedu_perf_varchar’)),
pg_size_pretty(pg_relation_size(‘fgedu_perf_varchar’))
UNION ALL
SELECT
‘fgedu_perf_text’,
pg_size_pretty(pg_total_relation_size(‘fgedu_perf_text’)),
pg_size_pretty(pg_relation_size(‘fgedu_perf_text’));

执行结果：

查询性能对比：

— 查询性能对比（使用EXPLAIN ANALYZE）
EXPLAIN ANALYZE SELECT COUNT(*) FROM fgedu_perf_char WHERE data LIKE ‘%5000%’;
EXPLAIN ANALYZE SELECT COUNT(*) FROM fgedu_perf_varchar WHERE data LIKE ‘%5000%’;
EXPLAIN ANALYZE SELECT COUNT(*) FROM fgedu_perf_text WHERE data LIKE ‘%5000%’;

执行结果：

— CHAR类型
Aggregate (cost=235.00..235.01 rows=1 width=8) (actual time=2.856..2.857 rows=1 loops=1)
-> Seq Scan on fgedu_perf_char (cost=0.00..210.00 rows=10000 width=0) (actual time=0.015..2.456 rows=10000
loops=1)
Filter: ((data)::text ~~ ‘%5000%’::text)
Planning Time: 0.089 ms
Execution Time: 2.895 ms

— VARCHAR类型
Aggregate (cost=235.00..235.01 rows=1 width=8) (actual time=2.123..2.124 rows=1 loops=1)
-> Seq Scan on fgedu_perf_varchar (cost=0.00..210.00 rows=10000 width=0) (actual time=0.012..1.823
rows=10000 loops=1)
Filter: ((data)::text ~~ ‘%5000%’::text)
Planning Time: 0.076 ms
Execution Time: 2.156 ms

— TEXT类型
Aggregate (cost=235.00..235.01 rows=1 width=8) (actual time=2.098..2.099 rows=1 loops=1)
-> Seq Scan on fgedu_perf_text (cost=0.00..210.00 rows=10000 width=0) (actual time=0.011..1.801 rows=10000
loops=1)
Filter: (data ~~ ‘%5000%’::text)
Planning Time: 0.072 ms
Execution Time: 2.132 ms

6. 字符串函数详解

常用字符串函数：

— 字符串连接
SELECT
‘Hello’ || ‘ ‘ || ‘World’ AS concat_result,
CONCAT(‘Hello’, ‘ ‘, ‘World’) AS concat_func,
CONCAT_WS(‘-‘, ‘A’, ‘B’, ‘C’) AS concat_ws;

执行结果：

concat_result | concat_func | concat_ws
—————+————-+———–
Hello World | Hello World | A-B-C
(1 row)

字符串截取：

— 字符串截取
SELECT
SUBSTRING(‘PostgreSQL’ FROM 1 FOR 4) AS substring_result,
SUBSTRING(‘PostgreSQL’ FROM 5) AS substring_from,
LEFT(‘PostgreSQL’, 4) AS left_result,
RIGHT(‘PostgreSQL’, 3) AS right_result;

执行结果：

字符串大小写转换：

— 大小写转换
SELECT
UPPER(‘hello world’) AS upper_result,
LOWER(‘HELLO WORLD’) AS lower_result,
INITCAP(‘hello world’) AS initcap_result;

执行结果：

upper_result | lower_result | initcap_result
————–+————–+—————-
HELLO WORLD | hello world | Hello World
(1 row)

字符串填充和修剪：

— 填充和修剪
SELECT
LPAD(‘123’, 10, ‘0’) AS lpad_result,
RPAD(‘123’, 10, ‘*’) AS rpad_result,
TRIM(‘ hello ‘) AS trim_result,
LTRIM(‘ hello ‘) AS ltrim_result,
RTRIM(‘ hello ‘) AS rtrim_result;

执行结果：

字符串替换：

— 字符串替换
SELECT
REPLACE(‘Hello World’, ‘World’, ‘PostgreSQL’) AS replace_result,
TRANSLATE(‘12345’, ‘123’, ‘ABC’) AS translate_result,
OVERLAY(‘PostgreSQL’ PLACING ‘SQL’ FROM 8) AS overlay_result;

执行结果：

replace_result | translate_result | overlay_result
———————–+——————+—————-
Hello PostgreSQL | ABC45 | PostgreSQL
(1 row)

字符串分割：

— 字符串分割
SELECT
SPLIT_PART(‘A,B,C,D’, ‘,’, 2) AS split_result,
STRING_TO_ARRAY(‘A,B,C,D’, ‘,’) AS array_result,
ARRAY_TO_STRING(ARRAY[‘A’, ‘B’, ‘C’], ‘-‘) AS join_result;

执行结果：

split_result | array_result | join_result
————–+————–+————-
B | {A,B,C,D} | A-B-C
(1 row)

7. 字符串模式匹配

LIKE模式匹配：

— LIKE模式匹配
SELECT
‘PostgreSQL’ LIKE ‘Post%’ AS like_start,
‘PostgreSQL’ LIKE ‘%SQL’ AS like_end,
‘PostgreSQL’ LIKE ‘%gre%’ AS like_middle,
‘PostgreSQL’ ILIKE ‘postgresql’ AS ilike_case;

执行结果：

正则表达式匹配：

— 正则表达式匹配
SELECT
‘PostgreSQL’ ~ ‘^Post’ AS regex_start,
‘PostgreSQL’ ~ ‘SQL$’ AS regex_end,
‘PostgreSQL’ ~* ‘postgresql’ AS regex_case,
‘PostgreSQL’ !~ ‘MySQL’ AS regex_not_match;

执行结果：

更多视频教程www.fgedu.net.cn

正则表达式替换：

— 正则表达式替换
SELECT
REGEXP_REPLACE(‘Hello 123 World 456’, ‘\d+’, ‘NUM’, ‘g’) AS regexp_replace,
REGEXP_MATCHES(‘PostgreSQL 123’, ‘\d+’) AS regexp_matches,
REGEXP_SPLIT_TO_ARRAY(‘A,B,C’, ‘,’) AS regexp_split;

执行结果：

regexp_replace | regexp_matches | regexp_split
———————–+—————-+————–
Hello NUM World NUM | {123} | {A,B,C}
(1 row)

8. 实战案例：用户信息管理

创建用户信息管理系统：

— 创建用户表
CREATE TABLE fgedu_fgedus (
fgedu_id SERIAL PRIMARY KEY,
fgeduname VARCHAR(50) NOT NULL UNIQUE,
password_hash CHAR(64) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone CHAR(11),
real_name VARCHAR(50),
id_card CHAR(18),
address VARCHAR(200),
bio TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 创建用户日志表
CREATE TABLE fgedu_fgedu_logs (
log_id SERIAL PRIMARY KEY,
fgedu_id INTEGER,
action VARCHAR(50),
detail TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

— 插入用户数据
INSERT INTO fgedu_fgedus (fgeduname, password_hash, email, phone, real_name, id_card, address, bio) VALUES
(‘zhangsan’, ‘e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855’,
‘zhangsan@fgedu.net’, ‘13800138001’, ‘风哥1号’, ‘110101199001011234’,
‘北京市海淀区中关村大街1号’, ‘PostgreSQL数据库管理员，10年经验’),
(‘lisi’, ‘e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855’,
‘lisi@fgedu.net’, ‘13800138002’, ‘风哥2号’, ‘310101198512125678’,
‘上海市浦东新区陆家嘴金融中心’, ‘全栈开发工程师，精通Java和Python’),
(‘wangwu’, ‘e3b0c44298fc1c149afbf4c8996fb92427ae41e4649b934ca495991b7852b855’,
‘wangwu@fgedu.net’, ‘13800138003’, ‘王五’, ‘440101200001018765’,
‘广州市天河区珠江新城’, ‘运维工程师，专注于自动化运维’);

执行结果：

INSERT 0 3

用户查询功能：

— 按用户名模糊查询
SELECT fgedu_id, fgeduname, email, real_name
FROM fgedu_fgedus
WHERE fgeduname LIKE ‘%san%’;

执行结果：

邮箱验证：

— 验证邮箱格式
SELECT
fgeduname,
email,
email ~ ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$’ AS is_valid_email
FROM fgedu_fgedus;

执行结果：

手机号脱敏：

更多学习教程公众号风哥教程itpux_com

— 手机号脱敏显示
SELECT
fgeduname,
real_name,
OVERLAY(phone PLACING ‘****’ FROM 4 FOR 4) AS masked_phone
FROM fgedu_fgedus;

执行结果：

身份证号解析：

— 从身份证号提取信息
SELECT
fgeduname,
real_name,
id_card,
SUBSTRING(id_card, 7, 8) AS birth_date,
CASE WHEN SUBSTRING(id_card, 17, 1)::INTEGER % 2 = 1 THEN ‘男’ ELSE ‘女’ END AS gender
FROM fgedu_fgedus;

执行结果：

fgeduname | real_name | id_card | birth_date | gender
———-+———–+———————-+————+——–
zhangsan | 风哥1号 | 110101199001011234 | 19900101 | 男
lisi | 风哥2号 | 310101198512125678 | 19851212 | 男
wangwu | 王五 | 440101200001018765 | 20000101 | 男
(3 rows)

用户日志记录：

— 记录用户登录日志
INSERT INTO fgedu_fgedu_logs (fgedu_id, action, detail)
SELECT fgedu_id, ‘login’, ‘用户登录成功，IP: 192.168.1.100’
FROM fgedu_fgedus WHERE fgeduname = ‘zhangsan’;

— 查询用户日志
SELECT u.fgeduname, ul.action, ul.detail, ul.created_at
FROM fgedu_fgedu_logs ul
JOIN fgedu_fgedus u ON ul.fgedu_id = u.fgedu_id
ORDER BY ul.created_at DESC;

执行结果：

9. 清理环境

清理测试表：

— 删除所有测试表
DROP TABLE IF EXISTS fgedu_char_test;
DROP TABLE IF EXISTS fgedu_varchar_test;
DROP TABLE IF EXISTS fgedu_varchar_unlimited;
DROP TABLE IF EXISTS fgedu_text_test;
DROP TABLE IF EXISTS fgedu_perf_char;
DROP TABLE IF EXISTS fgedu_perf_varchar;
DROP TABLE IF EXISTS fgedu_perf_text;
DROP TABLE IF EXISTS fgedu_fgedus;
DROP TABLE IF EXISTS fgedu_fgedu_logs;

执行结果：

DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE

风哥教程风哥教程风哥教程总结：PostgreSQL提供了三种主要字符类型：CHAR(n)定长类型适合固定长度编码，VARCHAR(n)变长类型适合有长度限制的字段，TEXT类型适合长文本内容。在实际应用中，TEXT类型通常是最佳选择，因为它没有长度限制且性能与VARCHAR相当。合理使用字符串函数可以大大提高开发效率。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG042-PG字符类型：CHAR/VARCHAR/TEXT详解与性能对比

1. 字符类型概述

2. CHAR定长字符类型

3. VARCHAR变长字符类型

4. TEXT无限制文本类型

5. 字符类型性能对比

6. 字符串函数详解

7. 字符串模式匹配

8. 实战案例：用户信息管理

9. 清理环境

相关推荐

联系我们