MySQL 用mysql搜索时如何忽略大小写的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3150798/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I ignore the difference between upper and lower case when searching with mysql
提问by Mostafa Elkady
How can I ignore the difference between upper and lower case when searching with mysql
用mysql搜索时如何忽略大小写的区别
回答by JasCav
Do something like this:
做这样的事情：
SELECT user
FROM users
WHERE UPPER( user ) = UPPER( 'moustafa' );
Basically you're converting your result to one case and comparing against the search term which is also converted to upper case, effectively ignoring case.
基本上，您将结果转换为一种情况，并与也转换为大写的搜索词进行比较，从而有效地忽略了大小写。
回答by Mike
The UPPERand LOWERfunctions can be used, but you can also affect the case-sensitivity by selecting the appropriate collation and/or column type.
的UPPER和LOWER功能都可以使用，但也可以通过选择适当的整理和/或塔型影响区分大小写。
For example, latin1_general_csis case-sensitive with both VARCHARand VARBINARY:
例如，latin1_general_cs区分大小写与两个VARCHAR和VARBINARY：
DROP TABLE IF EXISTS `case_sensitive`;
CREATE TABLE `case_sensitive` (
`id` INT NOT NULL AUTO_INCREMENT,
`nonbinary` VARCHAR(255),
`binary` VARBINARY(255),
PRIMARY KEY (`id`)
) ENGINE=InnoDB COLLATE latin1_general_cs;
INSERT INTO `case_sensitive` (`nonbinary`, `binary`) VALUES ('A', 'A');
SELECT * FROM `case_sensitive` WHERE `nonbinary` = 'A';
+----+-----------+--------+
| id | nonbinary | binary |
+----+-----------+--------+
| 1 | A | A |
+----+-----------+--------+
1 row in set (0.00 sec)
SELECT * FROM `case_sensitive` WHERE `binary` = 'A';
+----+-----------+--------+
| id | nonbinary | binary |
+----+-----------+--------+
| 1 | A | A |
+----+-----------+--------+
1 row in set (0.00 sec)
SELECT * FROM `case_sensitive` WHERE `nonbinary` = 'a';
Empty set (0.00 sec)
SELECT * FROM `case_sensitive` WHERE `binary` = 'a';
Empty set (0.00 sec)
Whereas latin1_general_ciis case-insensitive with VARCHAR, and case-sensitive with VARBINARY:
虽然latin1_general_ci是不区分大小写用VARCHAR，并区分大小写有VARBINARY：
DROP TABLE IF EXISTS `case_insensitive`;
CREATE TABLE `case_insensitive` (
`id` INT NOT NULL AUTO_INCREMENT,
`nonbinary` VARCHAR(255),
`binary` VARBINARY(255),
PRIMARY KEY (`id`)
) ENGINE=InnoDB COLLATE latin1_general_ci;
INSERT INTO `case_insensitive` (`nonbinary`, `binary`) VALUES ('A', 'A');
SELECT * FROM `case_insensitive` WHERE `nonbinary` = 'A';
+----+-----------+--------+
| id | nonbinary | binary |
+----+-----------+--------+
| 1 | A | A |
+----+-----------+--------+
1 row in set (0.00 sec)
SELECT * FROM `case_insensitive` WHERE `binary` = 'A';
+----+-----------+--------+
| id | nonbinary | binary |
+----+-----------+--------+
| 1 | A | A |
+----+-----------+--------+
SELECT * FROM `case_insensitive` WHERE `nonbinary` = 'a';
+----+-----------+--------+
| id | nonbinary | binary |
+----+-----------+--------+
| 1 | A | A |
+----+-----------+--------+
SELECT * FROM `case_insensitive` WHERE `binary` = 'a';
Empty set (0.00 sec)
You should therefore pick a collation and column type that is most suited to your needs. You can find more information here:
因此，您应该选择最适合您需要的归类和列类型。您可以在这里找到更多信息：
Case Sensitivity in String Searches
http://dev.mysql.com/doc/refman/5.1/en/case-sensitivity.html
字符串搜索中的区分大小写
http://dev.mysql.com/doc/refman/5.1/en/case-sensitive.html
Character Sets and Collations in MySQL
http://dev.mysql.com/doc/refman/5.1/en/charset-mysql.html
MySQL 中的字符集和排序规则
http://dev.mysql.com/doc/refman/5.1/en/charset-mysql.html
Character Sets and Collations That MySQL Supports
http://dev.mysql.com/doc/refman/5.1/en/charset-charsets.html
MySQL 支持的字符集和排序规则
http://dev.mysql.com/doc/refman/5.1/en/charset-charsets.html
回答by Rodrigo Gama
Generally, you should use WHERE UPPER(COLUMNNAME) = UPPER('valuetocompare').
通常，您应该使用 WHERE UPPER(COLUMNNAME) = UPPER('valuetocompare')。
Alternatively, you culd use WHERE UPPER(COLUMNNAME) like UPPER('%valuetocompare%') if you want to do a substring search
或者，如果你想进行子字符串搜索，你可以像 UPPER('%valuetocompare%') 一样使用 WHERE UPPER(COLUMNNAME)
回答by Mostafa Elkady
i use utf8_unicode_(CI)-case-insensitive collate on the column your searching....and it works
我在您搜索的列上使用 utf8_unicode_(CI)-case-insensitive collate ....并且它有效
回答by sikas
you can store the data in the mySQL in lower case and just perform query on it.
您可以将数据以小写形式存储在 mySQL 中，然后对其执行查询。
回答by Necko
SELECT * FROM table_name WHERE column_name1 LIKE '%A%' OR column_name1 LIKE '%a%';
