MySQL LIKE条件子查询及JOIN临时数据集问题排查

需要实现两组查询逻辑：

1. 先获取tree_node表中id=1的记录的tag_chain值
2. 再查询所有tag_chain以该值开头的tree_node记录

尝试了以下3种SQL语句，但均只返回id=1的结果：

1. select t1.id from tree_node as t1 join tree_node as t2 on (t1.tag_chain like (t2.tag_chain + '%') and t2.id = 1);
2. select * from tree_node where tag_chain like (select tag_chain from tree_node where id = 1 + "%");
3. select * from tree_node where tag_chain like (select tag_chain + "%" from tree_node where id = 1);

同时疑惑：使用JOIN操作是否会产生庞大的临时中间数据集？

后续发现问题可能出在字符串拼接的差异上，比如like (91 + "%")和like ("91%")的处理逻辑不同导致结果异常。

错误原因拆解 #

1. 第二条SQL的语法错误：
   id = 1 + "%"是将数字1与字符串"%"做加法，数据库会做隐式类型转换，把"%"转为0，最终变成id=1，子查询返回的是原始tag_chain值（没有拼接%），所以like相当于精确匹配，仅返回id=1的记录。

2. 第一、三条SQL的拼接问题：
   如果tag_chain是数字类型，执行tag_chain + "%"时，数据库会把字符串"%"转为0，最终拼接结果等于原tag_chain值，like同样变成精确匹配，自然只返回id=1的记录。即使tag_chain是字符串类型，部分数据库（如MySQL）的+号是算术运算符，无法完成字符串拼接，会导致转换错误。

正确SQL写法 #

不同数据库的字符串拼接方式不同，以下是主流数据库的正确写法：

MySQL/MariaDB #

• 子查询方式：

  select * from tree_node where tag_chain like CONCAT((select tag_chain from tree_node where id=1), '%');
  

• JOIN方式：

  select t1.* from tree_node t1 
  join tree_node t2 on t1.tag_chain like CONCAT(t2.tag_chain, '%') 
  where t2.id=1;
  

SQL Server #

• 子查询方式：

  select * from tree_node where tag_chain like (select CAST(tag_chain AS VARCHAR) + '%' from tree_node where id=1);
  

• JOIN方式：

  select t1.* from tree_node t1 
  join tree_node t2 on t1.tag_chain like CAST(t2.tag_chain AS VARCHAR) + '%' 
  where t2.id=1;
  

  注：若tag_chain本身是字符串类型，可去掉CAST转换。

PostgreSQL #

• 子查询方式：

  select * from tree_node where tag_chain like (select tag_chain || '%' from tree_node where id=1);
  

• JOIN方式：

  select t1.* from tree_node t1 
  join tree_node t2 on t1.tag_chain like t2.tag_chain || '%' 
  where t2.id=1;
  

JOIN的性能疑问解答 #

JOIN是否产生庞大临时数据集取决于以下两点：

1. 索引情况：如果tag_chain字段创建了前缀索引，数据库可以直接利用索引筛选匹配的记录，无需生成全量临时表。
2. 数据量级：若表中数据量极大，但匹配tag_chain前缀的记录较少，JOIN的临时数据集也会很小。相比之下，子查询方式先获取固定的tag_chain值，再做前缀匹配，逻辑上更直接，也更容易利用索引优化性能。

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