php - 使Mysql查询不区分大小写

Question

是否可以使以下查询字符串不区分大小写？

SELECT SUBSTR(raw,INSTR(raw,'".$term."') - 50,350) as term,
(LENGTH(raw) - LENGTH(REPLACE(raw,'".$term."',''))) /LENGTH('".$term."') AS occur,name,title 
FROM ( 
     SELECT ( LENGTH(raw) - LENGTH(REPLACE(raw,'".$term."','')) ) / LENGTH('".$term."') AS occur, raw, consol.name,title 
     FROM consol 
     WHERE LENGTH(raw) - LENGTH(REPLACE(raw,'".$term."',''))>0 AND raw LIKE '%".$term."%' COLLATE utf8_general_ci ORDER BY occur DESC 
     ) t ";

该表的引擎是MyISAM，编码是UTF8。当我从命令行运行查询时，它可以工作，但是当我通过浏览器执行它时，它会抛出以下错误。

Notice: failed Query : COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1' in

该表采用 UTF-8 编码，排序规则为 utf8_general_ci。如何确保“small”查询与 Small 以及 Small 或 SMALL 匹配？

Answer 1

在将 $term 变量添加到查询之前，使用 strtolower() 或类似方法对其进行规范化。