PostgreSQL技术问答14 - String字符串

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

《PostgreSQL技术问答00 - Why Postgres》

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文讨论的内容是PostgreSQL中一个非常重要而常用的功能集合： String字符串。

什么是String

String的原意是“绳子”，也有串的意思。在信息技术的语境中，就借助了这个形态，将一系列序列化的字符连接起来的样子，称为字符串。在早期的计算机技术情景中，它主要针对英文，就是一系列字符的序列，但后来的技术发展需要支持更广泛的语言和文字，所以对于一些表意语言比如中文而言，它就不仅仅是字符了，也可以是一个文字的序列，所以其实更应当被称为“字串”。 当然，在一般情况下，我们还是尊重传统，称其为“字符串”。

String操作一般有那些方式

在一般应用开发的日常中，我们可以总结抽象出String操作的一般方法和范式，这些形式可能在任何语言和系统中，都会需要，比如：

• 字符串合并和连接(Contact)，将两个或者多个字符串连接在一起

• 获得子字符串，或者拆分成字符串数组

• 字符串数组合并(Join)，将一个数组中的字符串连接起来，可选连接符如逗号

• 在字符串中特定位置中插入内容或者覆盖内容

• 字符串修整(Trim)， 去除字符串头和尾的空字符或者指定字符

• 字符串填充(Pad)， 在字符串前面或者后面添加内存，将字符串扩展到固定的长度，编码常用

• 转换成大小写

• 获取字符串的长度

• 比较两个字符串的内容

• 在字符串中检索，如找到匹配的子字符串和位置

• 正则表达式操作

• 字符串内容和类型转换，如字符串转数字、JSON、Array等

在PG中，对于上述内容，都提供了对应的操作符和函数，来实现这些操作和处理。还需要注意，除了常规的字符串处理之外，在数据库中，可能还涉及到字符串和记录和记录集之间的操作(如记录集转字符串或者逆向转换)。

下面，我们就来看看Postgres中是如何对字符串类型的数据进行操作和处理的。

Postgres中，如何处理和操作字符串

按照字符串类型数据处理的一般方法论，Postgres提供了一套相关的数据类型、操作符和函数，来实现在数据库中，存储、修改和查询字符串数据的操作。这些内容和特性大致如下。

字段类型

在数据库中存储和操作字符串，首先需要在创建数据库表时，声明库表字段的数据类型。示例如下：

-- 创建字符串类型列的表
create table tuser (
id serial primary key,
name varchar(30),
descp text
);
CREATE TABLE

-- 检查类型
defaultdb=> \d tuser;
                                   Table "public.tuser"
 Column |         Type          | Collation | Nullable |              Default              
--------+-----------------------+-----------+----------+-----------------------------------
 id     | integer               |           | not null | nextval('tuser_id_seq'::regclass)
 name   | character varying(30) |           |          | 
 descp  | text                  |           |          | 
Indexes:
    "tuser_pkey" PRIMARY KEY, btree (id)

可以看到，示例中，使用了最常见的两种字符串数据类型，varchar需要指定最大长度，而text直接声明即可。

字符串构建、合并、切片、和修剪

下面是一些简单的示例：

// 构建、连接、切片修剪
with T(id, t1, t2) as (values 
(1, 'Hello', 'World'),
(2, 'Tom', 'Jerry')
) select id, t1 || t2, substring(t1,1,3), substring(t2,length(t2)-2, length(t2)) from T;
 id |  ?column?  | substring | substring 
----+------------+-----------+-----------
  1 | HelloWorld | Hel       | rld
  2 | TomJerry   | Tom       | rry
(2 rows)

// 从头或者尾截取
select left(str1,4), right(str1,3)  
from (values ('hello World')) as D (str1);
 left | right 
------+-------
 hell | rld
(1 row)

// conact函数
select concat(str1,str2,str3),concat_ws(',',str1,str2,str3)  
from (values ('hello World', 'welcome','bye')) as D (str1,str2,str3);
        concat         |        concat_ws        
-----------------------+-------------------------
 hello Worldwelcomebye | hello World,welcome,bye
(1 row)

-- 字符串重复
select repeat('World ', 4);
          repeat          
--------------------------
 World World World World 
(1 row)

这里的要点包括：

• 使用单引号表示和构建字符串
• 可以使用 || 操作符，或者concat方法
• 使用合并操作符时，支持字符串和其他标量的混合类型操作
• concat/concat_ws是可变参数列表，和字符串数组有差异

修剪、修补

主要使用trim, pad等相关函数。

-- 各种修剪
defaultdb=> with D(str1,str2) as (values  (' 中国 ', '人民'))
select 'N' || str1 || str2 from D
union all
select 'A' || trim(str1) || str2 from D
union all
select 'L' || ltrim(str1) || str2 from D
union all
select 'R' || rtrim(str1) || str2 from D;
  ?column?   
-------------
 N 中国 人民
 A中国人民
 L中国 人民
 R 中国人民
(4 rows)

-- 补足
defaultdb=> with D(v) as (values  (12), (456))
select lpad(v::text,10, '0'), rpad(v::text,10, '0') from D;
    lpad    |    rpad    
------------+------------
 0000000012 | 1200000000
 0000000456 | 4560000000
(2 rows)

修剪操作，通常用于去除字符串头和尾的空格和换行等不可见字符，一般是一种数据清理操作，减少数据内容和格式问题带来的隐患。补足操作，一般用于标准化编码，保持编码的长度一致和固定。

Postgres提供的trim和pad函数功能更加强大，它可以指定修剪和补足使用的字符串，而且支持多个字符。

字符串搜索和前缀检查

在字符串中，搜索给定的内容，是一个常见的需求和操作。还有一种情况，就是判断字符串是以某种形式开头，这种在进行编码判断的时候，非常有效。

-- 搜索位置
select strpos(str1, 'World'), strpos(str1, 'world'), 
starts_with(str1,'hello'), starts_with(str1,'Hello') 
from (values ('hello World')) as D (str1);
 strpos | strpos | starts_with | starts_with 
--------+--------+-------------+-------------
      7 |      0 | t           | f
(1 row)

需要注意的是，和一般编程语言不同，字符串检索位置是从1开始的，如果找不到，会返回0(一般是-1)。而且，Postgres没有提供“后缀检查”，就是类似ends_with的函数。

转换大小写，首字母大写、字符串反序

对于主要使用中文的应用环境中，这种操作的机会其实不太多。

-- 大小写，首字母大写
select lower(str1), upper(str1), initcap(str1) from (values ('hello World')) as D (str1);
    lower    |    upper    |   initcap   
-------------+-------------+-------------
 hello world | HELLO WORLD | Hello World
(1 row)

-- 反序回文
 select  reverse(str1)    
from (values ('hello World')) as D (str1);
   reverse   
-------------
 dlroW olleh
(1 row)

内容修改

字符串的内容修改，包括插入、搜索替换和位置覆盖等常见操作。下面是一些简单的示例：

-- 插入字符串，没有原生函数
 select substr(str1,1,5) || ' beatiful ' || substr(str1,6) from (values ('hello World')) as D (str1);
       ?column?        
-----------------------
 hello beatiful  World
(1 row)


-- 基于搜索替换
select replace(str1, 'World', 'China') from (values ('hello World')) as D (str1);
   replace   
-------------
 hello China
(1 row)


-- 基于位置覆盖
select  overlay(str1 placing 'China' from 2 for 4)    
from (values ('hello World')) as D (str1);
   overlay    
--------------
 hChina World
(1 row)

-- 字符映射转换
select translate('12345', '143', 'ax');
 translate 
-----------
 a2x5
(1 row)

比较字符串

是的，字符串也是可以比较的。比较的依据，如果是ASCII码字符，则依次比较字符编码顺序，如果是UTF8，则转换为字节数组后，依次比较数组元素的值。字符串比较的功能也是非常重要和常用的，比如经常用于过滤和匹配条件检查。

defaultdb=> select 'AAA'='AAA', 'AAA'='aaa', 'AAA' > 'BBB', '中国' > '中华';
 ?column? | ?column? | ?column? | ?column? 
----------+----------+----------+----------
 t        | f        | f        | t
(1 row)

字符串格式化

字符串格式化，笔者理解其实就跟C语言中的打印字符串(sprintf)一样，就是使用一个模板字符串，其中定义了可替换的类型变量，然后使用参数来填充和替换这些变量，就得到了最后格式化后的字符串。

SELECT format('Testing %s, %s, %s, %%', 'one', 'two', 'three');
Result: Testing one, two, three, %

SELECT format('|%10s|', 'foo');
Result: |       foo|

SELECT format('|%-10s|', 'foo');
Result: |foo       |

和C语言，format的核心是一种叫做格式化字符串的规范，其中的形式和细节比较多，这里就只展示最常用的方式，不再展开了。

字符串和其他类型的转换

字符串可以方便的和Number、Array、Record等进行相互转换。

-- 和数字的相互转换
select concat(str1,i1), i1+i2::int  from (values ('hello World', 123, '256')) as D (str1,i1,i2);
     concat     | ?column? 
----------------+----------
 hello World123 |      379
(1 row)

-- 日期格式化
SELECT to_char(current_date, 'YYYY-MM-DD');
  to_char   
------------
 2024-07-12
(1 row)

-- ASCII字符和编码
select ascii ('B'), chr(68); 
 ascii | chr 
-------+-----
    66 | D
(1 row)

-- 数组转换，
select string_to_array('A,B,C',','), 
split_part('D,E,F,G',',',-2), array_to_string('{1,2,3}'::text[],';');
 string_to_array | split_part | array_to_string 
-----------------+------------+-----------------
 {A,B,C}         | F          | 1;2;3
(1 row)


-- 分割部分
 select split_part('abc,def,ghi,jkl', ',', -2);
 split_part 
------------
 ghi
(1 row)

-- record to string

select string_agg(D.c::text, ',') from lateral (values (1),(2),(3) ) as D(c);
string_agg 
------------
 1,2,3
(1 row)

-- string to record
 SELECT * FROM UNNEST(STRING_TO_ARRAY('apple,banana,orange', ',')) with ordinality as T(name,id);
  name  | id 
--------+----
 apple  |  1
 banana |  2
 orange |  3
(3 rows)

-- 简化方式
SELECT * FROM string_to_table('apple,banana,orange', ',')  with ordinality as T(name,id);
  name  | id 
--------+----
 apple  |  1
 banana |  2
 orange |  3
(3 rows)

在使用字符串的转换的时候，要特别注意处理之前，需要合适的数据类型。所幸，Postgres提供了::这种同样的类型转换操作符来简化操作，虽然看起来有点奇怪，但它的功能和适用性是很强的。

作为开发者，我们也知道，关于字符串模式检查，最强大的功能集就是正则表达式，这个方面PG也有所支持，但由于这些部分内容较多，笔者准备在另行撰文专门讨论。

总结一下常用的字符串函数？

这里将日常比较常用的字符串相关的操作符和处理函数总结一下：

• text || text → text / text || anynonarray 字符串合并操作符
• text ^@ text → boolean 判断字符串起始于字符串
• text IS [NOT] [form] NORMALIZED → boolean，检查字符串是否是特定的Unicode格式
• concat / concat_ws 连接字符串，简单连接和使用分隔符
• trim / btrim / ltrim / rtrim 各种修整字符串的方式
• length / bit_length / octet_length / char_length 字符串长度，位长度，字节长度，字符长度
• lower / upper/ initcap 转换为小写，大写，首字符大写
• lpad / rpad 左补位，右补位
• left / right 按照长度截取开始和结束的子字符串
• position / strpos 检索子串在字符串中的位置
• starts_with 判断字符串起始于，好像没有ends_with?
• substring 获取子字符串，支持其止方式，模式匹配方式也可以使用模式字符串
• replace 替换字符串
• reverse 反序字符串
• repeat 重复字符串
• ascii / chr ascii字符数字码相互转换
• string_to_array / string_to_table 将字符串分解转换为数组和记录集
• split_part 分解为数组后，获取其中一个元素
• to_hex / to_ascii 编码转换
• translate 将字符串依次按照模式字符串中的字符替换
• overlay 字符串覆盖
• md5 计算字符串的MD5值
• unistr/ normalize / unicode编码字符串的解码
• pg_client_encoding 获取当前客户端字符集
• parse_ident 解析标识
• quote... 引述相关函数，转为字面量，笔者理解就是增加
• regexp... 正则表达式计算相关函数群
• format 格式化字符串

这其中的regexp正则表达式相关操作，和format格式化相关操作，内容比较多，也自成体系，笔者后续会撰文详细探讨。

PG中有那些字符串类型

一些有Postgres使用经验的开发者，可能会注意到，在Postgres中，对于字符串的支持，提供了好几种数据类型，包括char(character)、varchar(character varying)、bpchar和text几种。 它们可能适用不同的需求和场景，作为开发者有必要了解其中的差异，才能避免由于适用不当造成的性能问题。

• text

text就是文本，在PG中它是可变长度的字符串类型。在技术上，PG支持1GB的text字符串数据，但是在应用中，text类型没有长度限制，也不应当设置。

• varchar

varchar就是可变长度的字符串。但是在使用(创建表)的时候，需要指定一个最大的长度。如varchar(255)表示最多可以存储255个字符的字符串。如果向这个数据列中，插入超过这个最大长度的字符串，则会触发操作错误，并收到错误消息。

• char

和varchar相对，char就是一种定长的字符串。在很多场景中，我们都可以看到定长字符串的使用，比如中国省市县三级行政区划编码、中国手机电话号码长度、居民身份证号码长度、ICAO机场编码等等。由于是确定的长度，数据库系统在存储和处理这些信息的时候，是非常确定和高效的。如果能够确认这些业务特点，则应当优先选择这种类型。

• bpchar

bpchar的意思是(Blob Char，Binary Large Object 二进制大对象字符串)。它是一个定长的二进制形式的字符串类型，最大可选长度为8K字节。它在形式上是一个字符串，但实际上是二进制数据(字节数组)。保留字符串形式的目的是，它可以以此支持一些标准的字符串操作方式，方便开发和使用。当然Postgres也提供存储的Blob数据格式，给任意形式的编码化的信息使用。

在这几种类型中，varchar和text的使用是比较普遍的，它们都用于存储不能确定长度的字符串数据。但需要注意，它们的使用还是略有差异。如果确实能够确认字符串的长度，不会超过一个限制，则建议使用varchar，比如电子邮件地址、电话号码、人名、证件编号、简短的描述信息等等的；而对于可能比较大的篇幅的文本，则可以使用text，比如博客文章、产品信息和手册文本等等。

在技术细节方面，Postgres对于varchar和text的存储和处理也稍有不同。相对而言，varchar的处理更类似于标准数据库字段，系统的存储和处理效率更高；而相对的text则更像一种可动态挂接的外部数据块，相对处理性能更低，但灵活性更高。如果对性能比较敏感，需要认真评估数据类型选择对实际业务应用的影响，最后做一些实验和测试工作。

字符串的长度，是以什么作为依据的

在Postgres系统的日常使用中，有经验的开发者会发现，数据库中，字符串长度的计算，好像在不同的情况下，是不一样的。这个问题，需要在一些具体的情况下，予以关注，否则容易造成应用开发和使用中超限或者出错的情况。

下面是一个简单的例子：

defaultdb=> elect length('Hello'::varchar), length('Hello'::bytea), length('中国'::varchar), length('中国'::bytea);
 length | length | length | length 
--------+--------+--------+--------
      5 |      5 |      2 |      6
(1 row)

可以看到，PG对于字符串长度的计算，有其内部规则，并不是简单的字节占用的大小。笔者简单的理解，就是如果字符串是UTF-8编码，则使用UTF-8的字符长度；如果是纯ASCII编码，就是使用ASCII字符长度。要检查字符串作为字节数组(二进制形式)的长度，需要进行类型的转换。

小结

本文探讨了PostgreSQL中，一个非常基础和核心的数据类型和特性：String，即字符串。具体包括了字符串操作的一般方法论和模型，常见的PG字符串操作，字符串数据类型等相关的问题。