选择仅包含大写字母的所有字段

您可能需要使用区分大小写的 collation . 我认为默认值不区分大小写。例子:

CREATE TABLE my_table (
   id int,
   name varchar(50)
) CHARACTER SET latin1 COLLATE latin1_general_cs;

INSERT INTO my_table VALUES (1, 'SomeThing');
INSERT INTO my_table VALUES (2, 'something');
INSERT INTO my_table VALUES (3, 'SOMETHING');
INSERT INTO my_table VALUES (4, 'SOME4THING');

然后:

SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$';
+------+-----------+
| id   | name      |
+------+-----------+
|    3 | SOMETHING |
+------+-----------+
1 row in set (0.00 sec)

如果不想对整个表使用区分大小写的排序规则,也可以使用 COLLATE 条款as @kchau suggested in the other answer .

CREATE TABLE my_table (
   id int,
   name varchar(50)
) CHARACTER SET latin1 COLLATE latin1_general_ci;

INSERT INTO my_table VALUES (1, 'SomeThing');
INSERT INTO my_table VALUES (2, 'something');
INSERT INTO my_table VALUES (3, 'SOMETHING');
INSERT INTO my_table VALUES (4, 'SOME4THING');

这样做效果不太好:

SELECT * FROM my_table WHERE name REGEXP '^[A-Z]+$';
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | SomeThing |
|    2 | something |
|    3 | SOMETHING |
+------+-----------+
3 rows in set (0.00 sec)

但是我们可以用 子句将名称字段整理为区分大小写的排序规则:

SELECT * FROM my_table WHERE (name COLLATE latin1_general_cs) REGEXP '^[A-Z]+$';
+------+-----------+
| id   | name      |
+------+-----------+
|    3 | SOMETHING |
+------+-----------+
1 row in set (0.00 sec)

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

SELECT * FROM table1 WHERE (columnname COLLATE latin1_bin )=UPPER(depart);

通过使用 REGEXP : http://www.tech-recipes.com/rx/484/use-regular-expressions-in-mysql-select-statements/

[:upper:] 大写字母。

SELECT * FROM table WHERE field REGEXP '^[[:upper:]+]$'

SELECT column_name FROM table WHERE column_name REGEXP BINARY '^[A-Z]+$'

eg .

SELECT * FROM foo WHERE bar REGEXP '[A-Z]';

试试这个-

SELECT * FROM <mytable> WHERE UPPER(<columnname>) = <columnname>

SELECT * FROM mytable WHERE BINARY mycolumn = BINARY UPPER(mycolumn);

问题在于 WHERE UPPER(mycolumn) = mycolumn 是排序规则,它取决于您的表,您可以在那里使用什么。