代码之家  ›  专栏  ›  技术社区  ›  Sandeepan Nath

MySQL中LIKE-inside-SELECT查询的切换

  •  24
  • Sandeepan Nath  · 技术社区  · 15 年前

    CREATE TABLE IF NOT EXISTS `Tags` (
       `id_tag` int(10) unsigned NOT NULL auto_increment,
       `tag` varchar(255) default NULL,
       PRIMARY KEY  (`id_tag`),
       UNIQUE KEY `tag` (`tag`),
       KEY `id_tag` (`id_tag`),
       KEY `tag_2` (`tag`),
       KEY `tag_3` (`tag`),
       KEY `tag_4` (`tag`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2937 ;
    
    INSERT INTO `Tags` (`id_tag`, `tag`) VALUES
       (1816, '(class'),
       (2642, 'class\r\n\r\nâ?¬35'),
       (1906, 'class\r\nif'),
       (1398, 'class'),
       (2436, 'class)'),
       (1973, 'class:\n1.'),
       (2791, 'classes'),
       (1325, 'New'),
       (2185, 'pack'),
       (1905, 'packed'),
       (1389, 'WebClass');
    

    我要获取标记与关键字匹配的所有记录 class pack new

    查询1

    select id_tag,
    case tag 
       when tag LIKE "%class%" then "class" 
       when tag LIKE "%new%" then "new"
       when tag LIKE "%pack%" then "pack"
    end as matching_tag 
    from Tags 
    where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%"
    

    查询2

    select id_tag,
    case tag 
       when "class" then "class" 
       when "new" then "new"
       when "pack" then "pack"
    end as matching_tag 
    from Tags 
    where tag = "class" OR tag = "new" OR tag = "pack"
    

    查询1有什么问题。请帮助。

    2 回复  |  直到 10 年前
        1
  •  45
  •   Unreason    15 年前

    Mysql支持case的两个变体,在query2中使用的那个变体灵活性较差,但只支持单个变量上的相等。另一个版本在case和then条件之后不指定变量,条件不必仅相等:

    select id_tag,
    case  
       when tag LIKE "%class%" then "class" 
       when tag LIKE "%new%" then "new"
       when tag LIKE "%pack%" then "pack"
    end as matching_tag 
    from Tags 
    where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%"
    

    documentation 更多细节

    编辑: 下面对查询#1返回结果的原因进行更多解释:

    case tag
       when tag LIKE "%class%" then "class" 
       when tag LIKE "%new%" then "new"
       when tag LIKE "%pack%" then "pack"
    end as matching_tag
    

    期望得到一个文本值,以便在 when ... then 在上述情况下,表达式 tag LIKE "%class%" , tag LIKE "%new%" tag LIKE "%pack%" 都是在实际案例比较之前进行评估的。 但是(!),所发生的是,它们变为0或1,当与tag的值比较时,它是0的第一个值,它将匹配任何char(char将被转换为0)-这与第一个查询的结果一致。

    select id_tag, tag LIKE "%class%", tag LIKE "%new%", tag = 0, case tag     when tag LIKE "%class%" then "class"     when tag LIKE "%new%" then "new"    when tag LIKE "%pack%" then "pack" end as matching_tag  from Tags  where tag LIKE "%class%" OR tag LIKE "%new%" OR tag LIKE "%pack%";
    

    这就是为什么你会得到意想不到的结果;无声的演员阵容是这里的标准陷阱。

        2
  •  16
  •   cn007b Dheerendra Kulkarni    10 年前

    只是想提醒一下,关于else条款:

    case  
       when tag LIKE "%class%" then "class" 
       when tag LIKE "%new%" then "new"
       when tag LIKE "%pack%" then "pack"
       else "no one"
    end as matching_tag