H2数据库中IN子句未对短字符进行右填充问题

问题场景 #

创建含CHAR类型列的表并插入带右填充空格的数据：

create table mytable (col_fixed_width char(4));
insert into mytable (col_fixed_width) values ('01  ');
insert into mytable (col_fixed_width) values ('02  ');

查询时出现以下差异：

• 单值比较或单值IN查询可正常匹配（H2自动执行右填充）：

  SELECT * from mytable where col_fixed_width='01'; -- 返回结果
  SELECT * from mytable where col_fixed_width IN ('01'); -- 返回结果
  

• 多值IN或多个单值IN用OR连接时无结果返回（自动右填充失效）：

  SELECT * from mytable where col_fixed_width IN ('01', '02');  -- 无结果
  SELECT * from mytable where col_fixed_width IN ('01') OR col_fixed_width IN ('02');  -- 无结果
  

• 手动给IN列表值添加对应空格后可正常匹配：

  SELECT * from mytable where col_fixed_width IN ('01  ', '02  '); -- 返回结果
  SELECT * from mytable where col_fixed_width IN ('01  ') OR col_fixed_width IN ('02  '); -- 返回结果
  

使用H2版本：v2.1.214

原因分析 #

H2对CHAR类型列的隐式右填充逻辑在单值和多值场景下存在差异：

• 单值比较（=或单值IN）时，H2会自动将右侧字符串填充到CHAR列定义的长度，再与存储值比较；
• 多值IN（或多个单值IN用OR连接）时，H2会将IN列表中的字符串视为VARCHAR类型处理，不会执行自动右填充，导致存储的带空格CHAR值与未填充的字符串无法匹配。

解决方案 #

1. 手动填充空格：给IN列表中的每个值添加对应长度的空格，匹配CHAR列的定义宽度；
2. 显式转换类型：将CHAR列值转换为VARCHAR后再比较，或去除空格后匹配：

   -- 去除列值空格后匹配
   SELECT * from mytable where TRIM(col_fixed_width) IN ('01', '02');
   -- 转换为VARCHAR后匹配
   SELECT * from mytable where CAST(col_fixed_width AS VARCHAR(4)) IN ('01', '02');
   

3. 替换字段类型：如果业务场景允许，将CHAR类型替换为VARCHAR类型，避免固定长度填充带来的匹配问题。

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