如何使用LIKE通配符在列中搜索(不区分大小写)? [英] How can I search (case-insensitive) in a column using LIKE wildcard?

问题描述

我环顾四周，却找不到我想要的东西.

I looked around some and didn't find what I was after so here goes.

SELECT * FROM trees WHERE trees.`title` LIKE  '%elm%'

这很好，但是如果树被命名为Elm或ELM等则不行...

This works fine, but not if the tree is named Elm or ELM etc...

对于此通配符搜索，如何使SQL不区分大小写?

How do I make SQL case insensitive for this wild-card search?

我正在使用MySQL 5和Apache.

I'm using MySQL 5 and Apache.

推荐答案

SELECT  *
FROM    trees
WHERE   trees.`title` COLLATE UTF8_GENERAL_CI LIKE '%elm%'

实际上，如果将COLLATE UTF8_GENERAL_CI添加到列的定义中，则可以忽略所有这些技巧:它将自动运行.

Actually, if you add COLLATE UTF8_GENERAL_CI to your column's definition, you can just omit all these tricks: it will work automatically.

ALTER TABLE trees 
 MODIFY COLUMN title VARCHAR(…) CHARACTER 
 SET UTF8 COLLATE UTF8_GENERAL_CI. 

这还将重建该列上的所有索引，以便可以将其用于查询而无需前导'％'

This will also rebuild any indexes on this column so that they could be used for the queries without leading '%'

这篇关于如何使用LIKE通配符在列中搜索(不区分大小写)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！