PHP小码农

Appearance

字符串操作 ​

本节系统讲解 PostgreSQL 的字符串函数与操作符。你将掌握长度计算、拼接、截取、查找替换、大小写转换、填充、正则匹配等常用操作的用法与陷阱，并能写出可读性良好的字符串处理 SQL。

前置知识

阅读本节前，建议先了解：定长与变长类型

基础概念 ​

PostgreSQL 的字符串处理能力分为四层，从下到上依次是：

层次内容典型成员遵循标准
操作符SQL 标准操作符`
SQL 标准函数SQL 标准定义的函数char_length、substring、upper、trim、position、overlaySQL 标准
PostgreSQL 扩展函数PostgreSQL 独有的便捷函数length、strpos、split_part、string_agg、concat、formatPostgreSQL 扩展
正则函数基于 POSIX 正则的匹配与替换regexp_match、regexp_replace、regexp_split_to_tablePostgreSQL 扩展

关键术语：

  • 字符（Character）：按编码解码后的可见单元，一个汉字算 1 个字符。
  • 字节（Byte）：存储单位，UTF-8 下一个汉字占 3 字节。
  • 字节序/字符序（Collation）：决定比较与排序规则，影响 LIKE 之外的排序行为。
  • POSIX 正则表达式（POSIX Regular Expression）：PostgreSQL ~ 系列操作符使用的正则方言，与 LIKE 的 %/_ 完全不同。

字符长度与字节长度是两件不同的事，这是字符串操作中最常见的混淆点：

sql
SELECT
  '中国'            AS val,
  length('中国')     AS char_len,      -- 字符数
  octet_length('中国') AS byte_len,    -- 字节数
  bit_length('中国')   AS bit_len;     -- 位数
text
  val | char_len | byte_len | bit_len
------+----------+----------+---------
 中国 |        2 |        6 |      48

在 UTF-8 编码下，一个汉字 3 字节，所以 char_len = 2 而 byte_len = 6。

语法与用法 ​

字符串函数数量众多，按用途分组记忆最有效：

text
-- 长度
length(str) / char_length(str) / octet_length(str) / bit_length(str)

-- 拼接
str1 || str2
concat(str1, str2, ...)
concat_ws(sep, str1, str2, ...)

-- 大小写
upper(str) / lower(str) / initcap(str)

-- 截取
substring(str FROM start FOR count) / substr(str, start, count)
left(str, n) / right(str, n)
split_part(str, delim, n)

-- 查找
position(sub IN str) / strpos(str, sub)
starts_with(str, prefix)

-- 修剪与填充
trim([LEADING|TRAILING|BOTH] [chars] FROM str)
ltrim(str[, chars]) / rtrim(str[, chars]) / btrim(str[, chars])
lpad(str, len[, fill]) / rpad(str, len[, fill])

-- 替换与变换
replace(str, from, to)
translate(str, from, to)
overlay(str PLACING newstr FROM start [FOR count])
reverse(str)
repeat(str, n)
format(fmt, args...)

-- 正则
str ~ pattern / str ~* pattern / str !~ pattern / str !~* pattern
regexp_replace(str, pattern, replacement [, flags])
regexp_match(str, pattern [, flags])
regexp_split_to_array(str, pattern [, flags])

最简可运行示例：

sql
SELECT
  'postgresql' || '18'                    AS concat_op,      -- 操作符拼接
  concat('Post', 'greSQL')                AS concat_fn,      -- 函数拼接
  concat_ws('-', '2026', '09', '22')      AS concat_ws_fn,   -- 带分隔符
  upper('demo')                           AS up,             -- 转大写
  length('PostgreSQL 18')                 AS len,            -- 字符长度
  substr('PostgreSQL', 1, 4)              AS sub;            -- 截取
text
  concat_op  | concat_fn | concat_ws_fn |  up  | len |  sub
-------------+-----------+--------------+------+-----+------
 postgresql18 | PostgreSQL | 2026-09-22  | DEMO |  15 | Post

详细说明 ​

长度函数：字符数还是字节数 ​

四个长度函数各司其职，选错会直接产生 bug：

函数返回值含义'中国' 结果常见用途
length(str)字符数（等价于 char_length）2业务长度校验
char_length(str)字符数（SQL 标准写法）2业务长度校验（可移植）
octet_length(str)字节数6估算存储、对接网络协议
bit_length(str)位数48极少使用
sql
SELECT
  length('中国')                          AS cn_char,
  octet_length('中国')                     AS cn_byte,
  char_length('中国')                      AS cn_char_std,
  octet_length('a')                        AS ascii_byte,
  length('')                               AS empty_char,
  length(NULL)                             AS null_len;
text
 cn_char | cn_byte | cn_char_std | ascii_byte | empty_char | null_len
---------+---------+-------------+------------+------------+----------
       2 |       6 |           2 |          1 |          0 |

两点注意：

  • length(NULL) 返回 NULL，不是 0。业务里做校验时要写 length(col) > 0 而非 <> ''，否则 NULL 行会漏掉。
  • VARCHAR(10) 的长度限制按字符计，不是字节。因此 VARCHAR(10) 可以存 10 个汉字（占 30 字节）。

拼接：|| 与 concat 的关键差异 ​

|| 是 SQL 标准操作符，concat 是 PostgreSQL 扩展函数。它们的空值处理完全不同：

sql
SELECT
  'a' || NULL              AS op_with_null,      -- NULL 传染
  concat('a', NULL)        AS fn_with_null,      -- NULL 被跳过
  concat('a', NULL, 'b')   AS fn_skip_null,
  concat_ws('-', 'a', NULL, 'b') AS ws_skip_null,
  '' || 'x'                AS empty_left;
text
 op_with_null | fn_with_null | fn_skip_null | ws_skip_null | empty_left
--------------+--------------+--------------+--------------+------------
              | a            | ab           | a-b          | x
表达式行为适用场景
a || b任一侧为 NULL 则结果为 NULL需要"数据缺失就整体缺失"的语义
concat(a, b)忽略 NULL，其余照常拼接拼接可选字段生成展示文本
concat_ws(sep, ...)忽略 NULL，且不给空位加分隔符拼接地址、全名等带分隔符的文本

concat_ws 的行为尤其重要——它不会产生 a--b 这类多余分隔符：

sql
-- 地址拼接：某些字段可能为 NULL
SELECT concat_ws(', ', '北京市', NULL, '朝阳区', NULL, '建国路 1 号') AS address;
text
           address
-----------------------------
 北京市, 朝阳区, 建国路 1 号

注意 concat / concat_ws 不接收数组作为可变参数（VARIADIC 除外），它们是 "any" 伪类型参数，会把所有入参转成文本：

sql
SELECT concat(1, 2, 3) AS num_concat, concat(true, 3.14) AS mixed_concat;
text
 num_concat | mixed_concat
------------+--------------
 123        | true3.14

如果需要显式格式化输出，用 format 函数更清晰：

sql
SELECT format('用户 %s 于 %s 注册，消费 %s 元', 'zhangsan', '2026-09-22', 1234.50) AS msg;
text
                          msg
--------------------------------------------------------
 用户 zhangsan 于 2026-09-22 注册，消费 1234.50 元

format 的占位符：%s（文本）、%I（标识符，会自动加双引号）、%L（字面量，会自动加单引号）、%%（百分号本身）。

截取与切分 ​

函数说明越界行为
substr(str, start, count)从 start 位置取 count 个字符自动截断到字符串末尾，不报错
substring(str FROM start FOR count)SQL 标准写法，等价同上
left(str, n)取左侧 n 个字符n 超过长度则返回整串
right(str, n)取右侧 n 个字符同上
split_part(str, delim, n)按分隔符取第 n 段（从 1 开始）段不存在返回空串 ''
sql
SELECT
  substr('PostgreSQL', 1, 4)                AS s1,
  substr('PostgreSQL', 6)                   AS s2,
  substr('PostgreSQL', -3)                  AS s3_negative,   -- 负数从末尾算
  left('PostgreSQL', 4)                     AS l,
  right('PostgreSQL', 4)                    AS r,
  split_part('a,b,c,d', ',', 2)             AS part2,
  split_part('a,b,c,d', ',', 9)             AS part_missing;
text
  s1  |     s2     | s3_negative |  l   |   r  | part2 | part_missing
------+------------+-------------+------+------+-------+--------------
 Post | greSQL     | SQL         | Post | eSQL | b     |

substr 的负数起始位置是按"从末尾倒数"解释的，这在解析文件扩展名等场景很有用：

sql
SELECT
  substr('report.2026.pdf', -3)  AS ext,     -- 取扩展名
  split_part('report.2026.pdf', '.', 2) AS second;
text
 ext | second
-----+--------
 pdf | 2026

string_to_array 与 array_to_string 是 split_part 的数组版本，适合段数不定的场景：

sql
SELECT
  string_to_array('a,b,c', ',')            AS arr,
  array_to_string(string_to_array('a,b,c', ','), '-') AS joined,
  unnest(string_to_array('a,b,c', ','))    AS each;
text
  arr   | joined | each
--------+--------+------
 {a,b,c} | a-b-c  | a
        |        | b
        |        | c

修剪与填充 ​

trim 系列函数既可以用 SQL 标准语法，也可以用 PostgreSQL 函数写法：

sql
SELECT
  trim('  hello  ')                        AS both_trim,
  trim(BOTH ' ' FROM '  hello  ')          AS both_std,
  trim(LEADING 'x' FROM 'xxhelloxx')       AS leading_trim,
  trim(TRAILING 'x' FROM 'xxhelloxx')      AS trailing_trim,
  btrim('xxhelloxx', 'x')                  AS btrim_fn,
  ltrim('xxhelloxx', 'x')                  AS ltrim_fn,
  rtrim('xxhelloxx', 'x')                  AS rtrim_fn;
text
 both_trim | both_std | leading_trim | trailing_trim | btrim_fn | ltrim_fn | rtrim_fn
-----------+----------+--------------+---------------+----------+----------+----------
 hello     | hello    | helloxx      | xxhello       | hello    | helloxx  | xxhello

默认只去除空格，不包含 \t、\n、\r。清理用户输入时要显式指定字符集：

sql
SELECT
  trim(E'\t hello \n')                     AS default_trim,   -- \n 未去除
  trim(E'\t hello \n' , E' \t\n\r')        AS full_trim;      -- 全部去除
text
 default_trim | full_trim
--------------+-----------
 	 hello     | hello

填充函数用于对齐输出或生成固定宽度编码：

sql
SELECT
  lpad('42', 5, '0')          AS zero_pad,       -- 左填充零
  rpad('ab', 5, '*')          AS right_pad,      -- 右填充星号
  lpad('abcdef', 3)           AS truncate_left,  -- 超长时从左截断
  rpad('abcdef', 3)           AS truncate_right, -- 超长时从左截断
  lpad('7', 4)                AS default_pad;    -- 默认填充空格
text
 zero_pad | right_pad | truncate_left | truncate_right | default_pad
----------+-----------+---------------+----------------+-------------
 00042    | ab***     | abc           | abc            |     7

注意 lpad / rpad 在目标长度小于原串长度时会截断（保留左侧字符），不会报错。这个"静默截断"在生成定长编号时容易埋下隐患。

大小写与比较 ​

sql
SELECT
  upper('postgresql')   AS up,
  lower('PostgreSQL')   AS lo,
  initcap('hello world') AS init;
text
      up      |    lo      |    init
--------------+------------+-------------
 POSTGRESQL   | postgresql | Hello World

upper / lower 的行为依赖 数据库的字符集编码（Character Set Encoding）与排序规则（Collation）。对于 ASCII 之外的字符，转换结果可能不符合预期：

sql
SELECT
  upper('a')      AS ascii_up,
  upper('é')      AS accent_up,     -- 依赖 locale，UTF-8 数据库下通常正确
  upper('ß')      AS sharp_s;       -- 德语 ß 的大写在老版本可能是 'ß' 或 'SS'
text
 ascii_up | accent_up | sharp_s
----------+-----------+---------
 A        | É         | ß

不要依赖 upper / lower 做大小写不敏感的查找，应改用 ILIKE 或 citext 扩展，或建函数索引：

sql
-- 反例：无法使用普通索引
SELECT * FROM users WHERE lower(email) = 'a@b.com';

-- 正例 1：建函数索引
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- 正例 2：使用 ILIKE（需配合 pg_trgm 或表达式索引才高效）
SELECT * FROM users WHERE email ILIKE 'a@b.com';

查找与定位 ​

表达式返回未找到时说明
position(sub IN str)字符位置（从 1 起）0SQL 标准
strpos(str, sub)字符位置（从 1 起）0PostgreSQL 扩展，参数顺序与 position 相反
starts_with(str, prefix)布尔false判断前缀
sql
SELECT
  position('SQL' IN 'PostgreSQL')   AS std_pos,
  strpos('PostgreSQL', 'SQL')       AS fn_pos,
  strpos('PostgreSQL', 'xyz')       AS not_found,
  starts_with('PostgreSQL', 'Post') AS sw,
  starts_with('PostgreSQL', 'post') AS sw_case;
text
 std_pos | fn_pos | not_found | sw  | sw_case
---------+--------+-----------+-----+---------
       9 |      9 |         0 | t   | f

参数顺序陷阱：position(x IN y) 是"在 y 里找 x"，而 strpos(y, x) 是"在 y 里找 x"——两者语义相同，但参数书写顺序相反，混用极易出错。

starts_with 是区分大小写的。判断后缀需要自己组合：

sql
SELECT
  right('report.pdf', 4) = '.pdf'          AS suffix_check,
  'report.pdf' LIKE '%.pdf'                AS suffix_like;
text
 suffix_check | suffix_like
--------------+-------------
 t            | t

替换与变换 ​

sql
SELECT
  replace('2026-09-22', '-', '/')                    AS rep,
  translate('abcdef', 'abc', 'xyz')                  AS trans,      -- 逐字符映射
  reverse('abcdef')                                  AS rev,
  repeat('ab', 3)                                    AS rep_n,
  overlay('PostgreSQL' PLACING 'gre' FROM 6)         AS ov;
text
    rep     | trans  |  rev   | rep_n |    ov
------------+--------+--------+-------+-----------
 2026/09/22 | xyzdef | fedcba | ababab | Postgre

replace 与 translate 的区别必须分清：

函数语义示例
replace(str, from, to)把子串 from 整体替换为 toreplace('aXbXc', 'X', '--') → a--b--c
translate(str, from, to)按字符位置一一映射translate('aXbXc', 'X', '-') → a-b-c

translate 的规则：from 中第 n 个字符被映射为 to 中第 n 个字符；若 to 比 from 短，多余字符被删除：

sql
SELECT
  translate('hello', 'el', 'ip')  AS mapped,    -- e→i, l→p
  translate('hello', 'l', '')     AS deleted;   -- l 被删除
text
 mapped | deleted
--------+---------
 hippo  | heo

overlay 用新串覆盖指定区间，长度由新串决定（除非显式给出 FOR count）：

sql
SELECT
  overlay('PostgreSQL' PLACING 'XYZ' FROM 6 FOR 3) AS same_len,
  overlay('PostgreSQL' PLACING 'XYZ' FROM 6)       AS grow;
text
 same_len |   grow
----------+-----------
 PostgrXYZL | PostgrXYZSQL

正则表达式操作 ​

PostgreSQL 的 ~ 系列操作符实现 POSIX 正则匹配，与 LIKE 的 % / _ 完全不同：

操作符大小写敏感说明
~敏感匹配
~*不敏感匹配
!~敏感不匹配
!~*不敏感不匹配
sql
SELECT
  'abc123' ~ '^[a-z]+[0-9]+$'          AS posix_match,
  'abc123' LIKE '%123'                  AS like_match,
  'abc123' !~ '^[0-9]+$'               AS neg_match,
  'ABC123' ~ '^[a-z]+'                 AS case_sensitive,
  'ABC123' ~* '^[a-z]+'                AS case_insensitive;
text
 posix_match | like_match | neg_match | case_sensitive | case_insensitive
-------------+------------+-----------+----------------+------------------
 t           | t          | t         | f              | t

常用 POSIX 字符类：

写法含义等价写法
[[:digit:]]数字[0-9]
[[:alpha:]]字母[a-zA-Z]
[[:alnum:]]字母或数字[a-zA-Z0-9]
[[:space:]]空白字符\s（需 E'' 转义）
[[:punct:]]标点符号—
[[:upper:]] / [[:lower:]]大写 / 小写字母—

三个正则函数覆盖了提取、替换、切分：

sql
SELECT
  regexp_match('订单 A-2026-0922 已发货', '([A-Z])-(\d{4})-(\d{4})') AS matched,
  regexp_replace('13812345678', '(\d{3})\d{4}(\d{4})', '\1****\2')   AS masked,
  regexp_split_to_array('a1b22c333', '[0-9]+')                        AS splitted;
text
     matched      |    masked    |   splitted
------------------+--------------+---------------
 {A,2026,0922}    | 138****5678  | {a,b,c}

regexp_match 只返回第一处匹配的分组数组；要提取全部匹配，需要用 regexp_matches 配合 g 标志：

sql
SELECT regexp_matches('a1 b2 c3', '[a-z]([0-9])', 'g') AS all_matches;
text
 all_matches
-------------
 {1}
 {2}
 {3}

替换字符串中的 \1、\2 表示捕获组。转义时注意标准符合字符串：反斜杠在 standard_conforming_strings=on（默认）时无需双写，但若写成 E'...' 则需写 \\1。

LIKE、SIMILAR TO 与正则的取舍 ​

三种模式匹配方式的比较：

特性LIKESIMILAR TO~（POSIX 正则）
通配符%（任意长度）、_（单字符）%、_ + 正则元字符完整正则语法
大小写不敏感用 ILIKE不支持用 ~*
能否用索引能（前缀锚定 + text_pattern_ops）通常不能通常不能（除非 pg_trgm）
语法来源SQL 标准SQL 标准POSIX
性能最好中等较慢

LIKE 是绝大多数场景的正确选择，因为它可以走索引：

sql
-- 能用 B-tree 索引（前缀匹配，无前导通配符）
SELECT * FROM users WHERE email LIKE 'zhang%';

-- 不能用普通 B-tree 索引（前导通配符）
SELECT * FROM users WHERE email LIKE '%@example.com';

为了支持大小写不敏感的 LIKE 及非 C 排序规则下的前缀匹配，需要显式指定操作符类：

sql
CREATE INDEX idx_users_email_pattern ON users (email text_pattern_ops);
CREATE INDEX idx_users_email_ilike   ON users (email varchar_pattern_ops);

ILIKE 在非 C locale 下默认无法用普通索引，需建 lower() 表达式索引配合：

sql
CREATE INDEX idx_users_lower_email ON users (lower(email));
-- 查询改写为：
SELECT * FROM users WHERE lower(email) LIKE 'zhang%';

字符串聚合 ​

string_agg 把多行值聚合成一个字符串，是报表、导出场景的核心函数：

sql
SELECT string_agg(name, ', ' ORDER BY name) AS all_names
FROM users;
text
         all_names
----------------------------
 lisi, wangwu, zhangsan

语法为 string_agg(expr, delimiter [ORDER BY ...])。必须显式写 ORDER BY，否则聚合顺序在并行计划下是不确定的。

两参数版本接收 text，也可以用 bytea 分隔符处理二进制。与数组聚合 array_agg 的对比：

函数返回类型适用场景
string_agg(col, ',')TEXT生成 CSV 风格文本
array_agg(col)数组需要保序、保类型，后续再用 unnest 展开
sql
SELECT
  string_agg(DISTINCT status, '/' ORDER BY status) AS statuses,
  array_agg(DISTINCT status ORDER BY status)       AS status_arr
FROM orders;
text
   statuses    |        status_arr
---------------+--------------------------
 cancelled/paid/pending | {cancelled,paid,pending}

quote_* 与哈希函数 ​

生成 SQL 文本、动态 SQL 时，这三个函数是安全防线：

函数作用示例
quote_literal(str)加单引号并转义内部单引号quote_literal('it''s')
quote_ident(str)加双引号并转义内部双引号quote_ident('order')
quote_nullable(str)同 quote_literal，但 NULL 返回 'NULL'—
sql
SELECT
  quote_literal(E'O''Brien')   AS lit,
  quote_ident('user table')    AS ident,
  quote_nullable(NULL)         AS null_lit;
text
    lit     |    ident     | null_lit
------------+--------------+----------
 'O''Brien' | "user table" | NULL

常用哈希函数：

sql
SELECT
  md5('hello')                                        AS md5_hash,
  encode(sha256('hello'::bytea), 'hex')               AS sha256_hex,
  encode(hmac('hello', 'secret', 'sha256'), 'hex')    AS hmac_hex;
text
             md5_hash             |                            sha256_hex
----------------------------------+----------------------------------------------------------------
 5d41402abc4b2a76b9719d911017c592 | 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824

md5 返回 32 位十六进制字符串，sha256 / hmac 返回 bytea，需要 encode(..., 'hex') 转可读形式。注意 md5 已不适合做密码存储，请使用 pgcrypto 的 crypt() 配合 gen_salt('bf')。

隐式类型转换与操作符解析 ​

字符串操作中大量出现 unknown 类型字面量，理解其解析规则可避免"操作符不存在"的报错：

sql
SELECT
  '2026-09-22'::date + 1        AS date_add,      -- 显式转换后运算
  pg_typeof('abc')              AS untyped,       -- unknown
  pg_typeof('abc'::text)        AS typed;
text
  date_add  | untyped | typed
------------+---------+-------
 2026-09-23 | unknown | text

字符串字面量的初始类型是 unknown，PostgreSQL 会根据上下文推断目标类型。这解释了两个常见现象：

sql
-- 现象 1：unknown 字面量可隐式转为任意类型
SELECT '123'::int, '2026-09-22'::timestamp;

-- 现象 2：两个 unknown 相加会因为无法推断类型而报错
SELECT '1' + '2';
text
ERROR:  operator is not unique: unknown + unknown
LINE 1: SELECT '1' + '2';
               ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

实战示例 ​

以下示例在 demo 库中按顺序执行。

第 1 步：建表并插入数据

sql
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username   VARCHAR(32)  NOT NULL,
  email      TEXT         NOT NULL,
  full_name  TEXT,
  phone      VARCHAR(20),
  bio        TEXT,
  created_at TIMESTAMPTZ  NOT NULL DEFAULT now()
);

INSERT INTO users (username, email, full_name, phone, bio) VALUES
  ('zhangsan', 'ZhangSan@Example.com', '张三',  '13812345678', '  热爱数据库  '),
  ('lisi',     'lisi@example.com',     '李四',  '13987654321', NULL),
  ('wangwu',   'WANGWU@example.com',   NULL,    NULL,          E'\t PostgreSQL 爱好者 \n');

第 2 步：规范化清洗——去空格 + 统一大小写

sql
SELECT
  id,
  '[' || bio || ']'                        AS raw_bio,
  '[' || btrim(bio, E' \t\n\r') || ']'     AS cleaned_bio
FROM users
ORDER BY id;
text
 id |      raw_bio       |    cleaned_bio
----+--------------------+--------------------
  1 | [  热爱数据库  ]   | [热爱数据库]
  2 |                    | 
  3 | [	 PostgreSQL 爱好者 
] | [PostgreSQL 爱好者]

第 3 步：邮箱标准化（小写 + 拆分用户名与域名）

sql
SELECT
  username,
  email                             AS raw_email,
  lower(email)                      AS norm_email,
  split_part(lower(email), '@', 1)  AS local_part,
  split_part(lower(email), '@', 2)  AS domain
FROM users
ORDER BY id;
text
 username |       raw_email        |       norm_email       | local_part |   domain
----------+------------------------+------------------------+------------+-------------
 zhangsan | ZhangSan@Example.com   | zhangsan@example.com   | zhangsan   | example.com
 lisi     | lisi@example.com       | lisi@example.com       | lisi       | example.com
 wangwu   | WANGWU@example.com     | wangwu@example.com     | wangwu     | example.com

第 4 步：手机号脱敏（正则替换捕获组）

sql
SELECT
  username,
  phone,
  COALESCE(
    regexp_replace(phone, '^(\d{3})\d{4}(\d{4})$', '\1****\2'),
    '未填写'
  ) AS masked_phone
FROM users
ORDER BY id;
text
 username |    phone    | masked_phone
----------+-------------+--------------
 zhangsan | 13812345678 | 138****5678
 lisi     | 13987654321 | 139****4321
 wangwu   |             | 未填写

第 5 步：生成展示名（concat_ws 处理缺失字段）

sql
SELECT
  username,
  CONCAT_WS(' / ', full_name, username, email) AS display_name,
  format('%s <%s>', COALESCE(full_name, username), lower(email)) AS mail_addr
FROM users
ORDER BY id;
text
 username |            display_name            |         mail_addr
----------+------------------------------------+---------------------------
 zhangsan | 张三 / zhangsan / ZhangSan@Example.com | 张三 <zhangsan@example.com>
 lisi     | 李四 / lisi / lisi@example.com     | 李四 <lisi@example.com>
 wangwu   | wangwu / WANGWU@example.com        | wangwu <wangwu@example.com>

注意 display_name 中 concat_ws 不会因为 full_name 为 NULL 而多出分隔符，这正是它优于 || 的地方。

第 6 步：按域名聚合用户清单

sql
SELECT
  split_part(lower(email), '@', 2)          AS domain,
  count(*)                                   AS user_count,
  string_agg(username, ', ' ORDER BY username) AS members
FROM users
GROUP BY split_part(lower(email), '@', 2)
ORDER BY user_count DESC, domain;
text
   domain    | user_count |         members
-------------+------------+--------------------------
 example.com |          3 | lisi, wangwu, zhangsan

第 7 步：校验用户名格式（正则 + 布尔判断）

sql
SELECT
  username,
  username ~ '^[a-z][a-z0-9_]{2,31}$' AS valid_format,
  length(username)                     AS char_len,
  octet_length(username)               AS byte_len
FROM users
ORDER BY id;
text
 username | valid_format | char_len | byte_len
----------+--------------+----------+----------
 zhangsan | t            |        8 |        8
 lisi     | t            |        4 |        4
 wangwu   | t            |        6 |        6

第 8 步：一次查询汇总所有字符串统计

sql
SELECT
  count(*)                                              AS total_rows,
  count(bio)                                            AS bio_not_null,
  count(*) FILTER (WHERE bio ~ '\S')                    AS bio_non_blank,
  sum(length(coalesce(full_name, '')))                  AS total_name_chars,
  max(length(btrim(bio, E' \t\n\r')))                   AS max_clean_bio_len
FROM users;
text
 total_rows | bio_not_null | bio_non_blank | total_name_chars | max_clean_bio_len
------------+--------------+---------------+------------------+-------------------
          3 |            2 |             2 |                4 |                15

注意事项 ​

1. || 遇到 NULL 会把整串变成 NULL

sql
SELECT '张三' || NULL || '的订单';   -- 返回 NULL
SELECT concat('张三', NULL, '的订单'); -- 返回 '张三的订单'

拼接可能为空的列时优先用 concat_ws，或先 coalesce(col, '')。

2. length 返回字符数，octet_length 返回字节数

业务长度校验通常用 length，但对接有字节长度限制的系统（如某些短信网关 70 字节）必须用 octet_length：

sql
-- 中文短信：70 字节上限下只能放 23 个汉字（69 字节）
SELECT
  octet_length('中') * 23 AS cn_23_bytes,
  octet_length('中') * 24 AS cn_24_bytes;
text
 cn_23_bytes | cn_24_bytes
-------------+-------------
          69 |          72

3. lpad / rpad 会静默截断

sql
SELECT lpad('20260922', 6, '0') AS truncated;   -- 结果为 '202609'

想要"超长就报错"的语义，需要自己加 length 校验：

sql
SELECT CASE WHEN length(v) > 6 THEN NULL ELSE lpad(v, 6, '0') END
FROM (VALUES ('20260922')) AS t(v);

4. substr 的负数与零起始位置

sql
SELECT
  substr('abcdef', 0, 3)   AS start_zero,     -- 等价于 substr('abcdef', 1, 2)
  substr('abcdef', -2, 3)  AS start_negative; -- 从倒数第 2 个开始取 3 个
text
 start_zero | start_negative
------------+----------------
 ab         | ef

起始位置小于 1 时，取出的长度会相应减少（因为实际从位置 1 开始，但仍要求"累计到 start+count-1 为止"）。这个规则很反直觉，解析定长报文时要格外小心。

5. replace 与 translate 不可互换

sql
SELECT
  replace('a-b-c', '-', '_')     AS rep,   -- 子串替换
  translate('a-b-c', '-', '_')   AS trans; -- 字符映射（这里结果恰好相同）

差异在于多字符场景：replace('abc', 'ab', 'x') 得 xc，而 translate('abc', 'ab', 'x') 得 x c 中的 a→x、b 被删除，结果为 xc——看似相同但机制完全不同。只有 replace 支持多字符子串。

6. LIKE 的前导通配符无法使用 B-tree 索引

sql
SELECT * FROM users WHERE email LIKE '%example.com';   -- 全表扫描

需要此类查询时改用 pg_trgm 扩展的 GIN 索引：

sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING gin (email gin_trgm_ops);

7. string_agg 不写 ORDER BY 顺序不确定

sql
-- 顺序不确定
SELECT string_agg(username, ',') FROM users;

-- 顺序确定
SELECT string_agg(username, ',' ORDER BY id) FROM users;

8. 正则表达式默认是"部分匹配"

'abc123' ~ 'abc' 返回 t，因为 ~ 只要求存在匹配子串。要整串匹配必须加锚点：

sql
SELECT
  'abc123' ~ 'abc'      AS partial,
  'abc123' ~ '^abc$'    AS anchored,
  'abc123' ~ '^abc'     AS prefix_anchored;
text
 partial | anchored | prefix_anchored
---------+----------+-----------------
 t       | f        | t

9. 多字节字符的 position 按字符计数

sql
SELECT
  position('好' IN '你好世界')   AS pos_char,   -- 2
  octet_length('你')             AS first_byte; -- 3
text
 pos_char | first_byte
----------+------------
        2 |          3

10. 字符串比较受排序规则影响

在 C 排序规则下 'A' < 'a'，在 en_US.UTF-8 下 'a' < 'A'。跨库迁移数据时要注意：

sql
SELECT
  'A' < 'a'                            AS default_cmp,
  'A' COLLATE "C" < 'a' COLLATE "C"    AS c_collation_cmp;
text
 default_cmp | c_collation_cmp
-------------+-----------------
 f           | t

最佳实践 ​

1. 用 concat_ws 代替 || 拼接可能为空的列

sql
-- 推荐
SELECT concat_ws(', ', province, city, district, street) AS address FROM customers;

-- 不推荐：任一段为 NULL 时整串为 NULL
SELECT province || ', ' || city || ', ' || district || ', ' || street FROM customers;

2. 长度校验统一用 length()，字节限制用 octet_length()

sql
ALTER TABLE users ADD CONSTRAINT chk_username_len
  CHECK (length(username) BETWEEN 3 AND 32);

3. 清洗用户输入时显式指定 trim 字符集

sql
UPDATE users
SET full_name = btrim(full_name, E' \t\n\r\u00a0')   -- 含不换行空格
WHERE full_name IS NOT NULL;

\u00a0 是不换行空格（NBSP），在网页复制粘贴场景中非常常见，普通 trim 无法去除。

4. 前缀匹配统一用 LIKE 'xxx%' 而非 strpos

sql
-- 可用索引
SELECT * FROM users WHERE email LIKE 'zhang%';

-- 不可用索引（把函数套在列上破坏了索引可用性）
SELECT * FROM users WHERE strpos(email, 'zhang') = 1;

5. 用 format 生成动态 SQL，用 quote_ident / quote_literal 防注入

sql
CREATE OR REPLACE FUNCTION count_rows(p_table TEXT)
RETURNS BIGINT
LANGUAGE plpgsql AS $$
DECLARE
  v_count BIGINT;
BEGIN
  EXECUTE format('SELECT count(*) FROM %I', p_table) INTO v_count;
  RETURN v_count;
END;
$$;

%I 会自动处理标识符引号与转义，避免拼接 SQL 带来的注入风险。

6. 正则查询量大时建 pg_trgm GIN 索引

sql
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_users_email_trgm ON users USING gin (email gin_trgm_ops);
-- 以下查询可走索引
SELECT * FROM users WHERE email ILIKE '%example%';

7. 提取固定格式数据优先用 split_part，比正则快得多

sql
-- 推荐：简单分隔场景
SELECT split_part(email, '@', 2) FROM users;

-- 不推荐：正则开销更大
SELECT (regexp_match(email, '@(.+)$'))[1] FROM users;

8. 哈希值统一 encode(..., 'hex') 输出，避免二进制乱码

sql
SELECT
  encode(sha256('data'::bytea), 'hex') AS sha256_hex,
  encode(sha256('data'::bytea), 'base64') AS sha256_b64;

9. 大批量字符串更新的性能提示

replace / regexp_replace 在长文本上开销较大。若目标列上有索引，整列更新会触发大量索引维护。分批处理：

sql
UPDATE users
SET bio = replace(bio, '旧关键词', '新关键词')
WHERE id IN (SELECT id FROM users WHERE bio LIKE '%旧关键词%' LIMIT 1000);

10. citext 扩展处理大小写不敏感的唯一约束

若"邮箱唯一且大小写不敏感"是硬需求，用扩展类型比反复 lower() 更干净：

sql
CREATE EXTENSION IF NOT EXISTS citext;
ALTER TABLE users ALTER COLUMN email TYPE citext;
-- 此时 UNIQUE 约束天然大小写不敏感

下一节

继续学习：字符集与编码

参考链接 ​