PostgreSQL中Punycode格式域名的LIKE匹配问题求助

核心原因 #

Punycode是对完整域名标签（比如примерен这个部分）进行编码的，不是按片段或字符单独编码。所以单独转片段примере得到的Punycode，和完整标签примерен编码后的中间部分完全不匹配，这就是直接用片段Punycode做LIKE查询失败的原因。

方案1：PostgreSQL自定义Punycode解码函数 #

PostgreSQL本身没有内置Punycode处理函数，我们可以用PL/Python或PL/Perl编写自定义函数，将存储的Punycode域名解码为原始Unicode，再在解码后的内容上做LIKE匹配。

用PL/Python实现解码函数

首先确保PostgreSQL安装了plpython3u扩展，且服务器上安装了Python的idna库（pip install idna）：

-- 启用PL/Python扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS plpython3u;

-- 创建解码函数
CREATE OR REPLACE FUNCTION punycode_decode(punycode text) RETURNS text AS $$
import idna
try:
    return idna.decode(punycode)
except:
    return punycode  # 解码失败时返回原字符串
$$ LANGUAGE plpython3u;

之后就可以直接在查询中使用这个函数：

SELECT * FROM domains WHERE punycode_decode(domain) LIKE '%примере%';

用PL/Perl实现解码函数

如果服务器支持Perl，可使用Net::IDN::Encode模块：

-- 启用PL/Perl扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS plperlu;

-- 创建解码函数
CREATE OR REPLACE FUNCTION punycode_decode(punycode text) RETURNS text AS $$
use Net::IDN::Encode qw(domain_to_unicode);
eval { return domain_to_unicode($punycode); };
return $punycode if $@;
$$ LANGUAGE plperlu;

方案2：优化查询性能（生成列+索引） #

如果数据量较大，直接用函数查询会有性能问题，可以创建生成列自动维护解码后的Unicode域名，并添加模糊查询索引：

-- 添加生成列（自动从Punycode解码）
ALTER TABLE domains ADD COLUMN domain_unicode text GENERATED ALWAYS AS (punycode_decode(domain)) STORED;

-- 创建支持模糊查询的GIN索引
CREATE INDEX idx_domains_unicode_trgm ON domains USING gin (domain_unicode gin_trgm_ops);

之后查询可以直接使用生成列，性能大幅提升：

SELECT * FROM domains WHERE domain_unicode LIKE '%примере%';

Yii2中的实现方式 #

在Yii2中，可以通过Expression调用自定义函数或直接查询生成列：

调用自定义解码函数的示例

use yii\db\Expression;
use app\models\Domain;

$searchTerm = 'примере';
$domains = Domain::find()
    ->where(new Expression('punycode_decode(domain) LIKE :term', [':term' => "%{$searchTerm}%"]))
    ->all();

使用生成列的示例

use app\models\Domain;

$searchTerm = 'примере';
$domains = Domain::find()
    ->where(['like', 'domain_unicode', $searchTerm])
    ->all();

内容的提问来源于stack exchange，提问作者fabricius