如何检查MySQL中的大写字母？

I want to check, if a string consits only of uppercase letters. I know that RLIKE/REGEXP are not case sensitive in MySQL. So I tried to use the :upper: character class:

我想检查，如果一个字符串只包含大写字母。我知道RLIKE / REGEXP在MySQL中不区分大小写。所以我尝试使用：upper：character class：

SELECT 'z' REGEXP '^[[:upper:]]+$';

This gives true, although the z is in lower case,... why?

这是真的，虽然z是小写的，...为什么？

3 个解决方案

#1

REGEXP is not case sensitive, except when used with binary strings.

REGEXP不区分大小写，除非与二进制字符串一起使用。

http://dev.mysql.com/doc/refman/5.7/en/regexp.html

http://dev.mysql.com/doc/refman/5.7/en/regexp.html

So with that in mind, just do something like this:

所以考虑到这一点，只需做这样的事情：

SELECT * FROM `users` WHERE `email` REGEXP BINARY '[A-Z]';

Using the above example, you'd get a list of emails that contain one or more uppercase letters.

使用上面的示例，您将获得包含一个或多个大写字母的电子邮件列表。

#2

change to case sensitive collation, eg.

更改为区分大小写的排序规则，例如。

CHARACTER SET latin1 COLLATE latin1_general_cs

then try this query,

然后尝试这个查询，

SELECT 'z' REGEXP '^[A-Z]+$'

SQLFiddle Demo
SQLFiddle演示

#3

For me this works and is not using a regexp. It basically compares the field with itself uppercased by mysql itself.

对我来说，这是有效的，并没有使用正则表达式。它基本上将字段与mysql本身大写的字段进行比较。

-- will detect all names that are not in uppercase
SELECT 
    name, UPPER(name) 
FROM table 
WHERE 
    BINARY name <> BINARY UPPER(name)
;

#1