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

Question

如何在mysql中选择仅包含大写字符的字段或不包含任何小写字符的字段？

Answer 1

您可能需要使用区分大小写的排序规则。我相信默认值不区分大小写。例：

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)

如果您不想在整个表中使用区分大小写的排序规则，则也可以使用其他答案中建议的@kchauCOLLATE子句。

让我们尝试使用不区分大小写的排序规则的表：

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)

但是我们可以使用COLLATE子句将名称字段整理为区分大小写的整理：

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