PostgreSQL中SQL字符串相等与LIKE运算符结果差异及用法对比

我有一个存储登录名的users表，登录名仅包含[a-zA-Z0-9]字符，类型为varchar(50)。示例表结构及数据如下：

CREATE TABLE users( login character varying(50) );
INSERT INTO users VALUES ('user1');
INSERT INTO users VALUES ('user2');

SELECT *
FROM users
WHERE ...;

请问login LIKE 'exactMatch'、login LIKE 'exactMatch%'与login = 'exactMatch'三者有何区别？我预期在上述两行数据的库中，以下四个条件返回相同结果：

login LIKE '%user1'
login LIKE 'user1%'
login = 'user1'
login LIKE 'user1'

但在部分复杂登录名场景下，后两个条件会失效，这是为什么？

一、三个查询条件的核心区别 #

• login = 'exactMatch'：执行严格的全字符串匹配，要求目标字符串和查询值的每一个字符、长度完全一致，匹配规则遵循数据库的字符串比较配置（比如是否区分大小写、是否忽略末尾空格等）。
• login LIKE 'exactMatch'：当LIKE语句中没有通配符时，行为和=高度相似，但存在关键差异：部分数据库（如MySQL）中，LIKE会自动忽略字符串末尾的空格，而=会严格校验长度。
• login LIKE 'exactMatch%'：%是通配符，代表任意长度（含0）的任意字符，因此该条件会匹配所有以exactMatch开头的字符串，不管后面跟着什么内容。

二、复杂登录名场景下后两个条件失效的原因 #

你提到的login = 'user1'和login LIKE 'user1'失效，通常是以下几种情况导致：

1. 隐藏的不可见字符：登录名中可能混入了控制字符（如制表符\t、换行符\n）或全角空格，这些字符不在[a-zA-Z0-9]范围内但被误存入。比如实际登录名是'user1\t'，严格匹配时会因额外字符失败，但带通配符的LIKE '%user1'或'user1%'会跳过这些字符的匹配。
2. 末尾空格的影响：如果登录名实际存储时带有末尾空格（如'user1 '），login = 'user1'会因长度不一致匹配失败；部分数据库中LIKE 'user1'会忽略末尾空格能查到，但如果是PostgreSQL这类严格遵循标准的数据库，LIKE也会匹配失败，而'user1%'会匹配带空格的版本。
3. 字符编码或大小写差异：若数据库启用了区分大小写的排序规则，存储的'User1'和查询的'user1'会匹配失败；或是存在外观相同但编码不同的字符（如全角'１'和半角'1'），这些都会导致严格匹配失效，而带通配符的LIKE可能刚好避开差异位置。
4. 字符串长度溢出：如果登录名实际长度超过了varchar(50)的定义，部分数据库会截断存储但保留原长度标记，导致严格匹配时因长度不符失败，而带通配符的LIKE不受此影响。

内容的提问来源于stack exchange，提问作者János Balázs