如何在使用mysql搜索时忽略大小写的差异
发布于 2010-06-30 23:35:52
这样做:
SELECT user
FROM users
WHERE UPPER( user ) = UPPER( 'moustafa' );基本上,您将结果转换为一个大小写,并与同样转换为大写的搜索词进行比较,实际上忽略了大小写。
发布于 2010-07-01 00:11:09
可以使用UPPER和LOWER函数,但也可以通过选择适当的排序规则和/或列类型来影响区分大小写。
例如,对于VARCHAR和VARBINARY,latin1_general_cs都区分大小写
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)而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)因此,您应该选择最适合您需要的排序规则和列类型。您可以在此处找到更多信息:
字符串搜索中区分大小写
http://dev.mysql.com/doc/refman/5.1/en/case-sensitivity.html
MySQL中的字符集和归类
http://dev.mysql.com/doc/refman/5.1/en/charset-mysql.html
MySQL支持的字符集和归类
http://dev.mysql.com/doc/refman/5.1/en/charset-charsets.html
发布于 2010-06-30 23:36:43
通常,您应该使用WHERE UPPER(COLUMNNAME) = UPPER('valuetocompare')。
或者,如果要搜索子字符串,则可以使用WHERE UPPER(COLUMNNAME) like UPPER(‘%valuetocompare
https://stackoverflow.com/questions/3150798
复制相似问题