logo标签列表

MySQL - 查询首字母为小写或大写的记录

mysqlsqlmariadbuppercaselowercase
7

抱歉如果这是之前提出的问题，但我找不到答案。 如何创建一个MySQL选择语句来选择数据库中所有首字母为小写或大写的字符串。

通常我会使用类似于以下语句从英语单词数据库中选择长度超过8个字符的10个随机单词：

SELECT word FROM en_dictionary WHERE CHAR_LENGTH(word)>8 ORDER BY RAND() LIMIT 10

很不幸，这也会选择我不想在结果中出现的大写字母单词，因为它们通常是城市名称、物种等术语：enter image description here。因此，我的问题是：是否有一种方法只选择以小写字母字符（或大写字母）开头的单词？ 我可以做类似于这样的事情并且会起作用，但这是一个非常丑陋的语句：
AND word LIKE BINARY 'a%' AND word LIKE BINARY 'b%' AND word LIKE BINARY 'c%'...
“word”列定义的字符集和排序规则是什么？ - Madhur Bhaiya
3
我的设置：COLLATE='utf8_general_ci'。 - jjj
我的设置：COLLATE='utf8_general_ci'。尝试按照我的答案建议将列更新为utf8_bin（区分大小写）。 - Raymond Nijland
3个回答
10
你可以使用 Ascii() 函数。它返回输入字符串左侧字符的数字值。
对于首字母小写：ASCII码中 a 的值为97，z 的值为122。因此，第一个字符的 ASCII 码应该在 97 到 122 之间。
SELECT word 
FROM en_dictionary 
WHERE CHAR_LENGTH(word) > 8 
  AND ASCII(word) BETWEEN 97 AND 122
ORDER BY RAND() LIMIT 10

对于大写首字母：ASCII码中A的值为65，而Z的值为90。因此，字符串的第一个字符的ASCII码值应该在65和90之间。

SELECT word 
FROM en_dictionary 
WHERE CHAR_LENGTH(word) > 8 
  AND ASCII(word) BETWEEN 65 AND 90
ORDER BY RAND() LIMIT 10
3

虽然不是完整的答案，但这个回复太长了，无法作为评论，所以我把它做成了一个回答。

我建议您使用区分大小写的 utf8 校对规则，例如 utf8_bin。
然后您就不需要使用 BINARY 或其他 MySQL “技巧” 来获取您需要的结果，您可以简单地使用 LIKE/BETWEEN 和区分大小写而没有问题。

创建表格

CREATE TABLE test (

    utf8_general_ci_word VARCHAR(255) COLLATE utf8_general_ci
  , utf8_bin_word VARCHAR(255) COLLATE utf8_bin
);

INSERT INTO test (utf8_general_ci_word, utf8_bin_word) VALUES('a', 'a');
INSERT INTO test (utf8_general_ci_word, utf8_bin_word) VALUES('A', 'A');

INSERT INTO test (utf8_general_ci_word, utf8_bin_word) VALUES('b', 'b');
INSERT INTO test (utf8_general_ci_word, utf8_bin_word) VALUES('B', 'B');

Results

Query #1

    SELECT 
     utf8_general_ci_word
    FROM
     test 
    WHERE
     utf8_general_ci_word LIKE 'a%'

    ;

| utf8_general_ci_word |
| -------------------- |
| a                    |
| A                    |

Query #2

    SELECT 
     utf8_general_ci_word
    FROM
     test 
    WHERE
     utf8_general_ci_word LIKE BINARY 'a%'

    ;

| utf8_general_ci_word |
| -------------------- |
| a                    |

Query #3

    SELECT 
     utf8_bin_word
    FROM
     test 
    WHERE
     utf8_bin_word LIKE 'a%';

| utf8_bin_word |
| ------------- |
| a             |

演示

因此，您的查询可能类似于

查询

SELECT
  word
FROM
  en_dictionary
WHERE
    CHAR_LENGTH(word) > 8
  AND
    word BETWEEN 'a' AND 'z'
ORDER BY
 RAND()
LIMIT 10

测试数据演示

编辑于 2019年08月11日

这会错过像 'zahn' 这样的单词，因为它不是 <= 'z'，请使用 'zzzzzzz' 代替

感谢 dnoeth 的评论和在 2019年11月07日晚些时候的一次很晚的协商会议，对于某些情况，上述SQL确实存在一个错误..

由于 word BETWEEN 'a' AND 'z' 是语法糖，表示 word >= 'a' AND word <= 'z'，因此这确实不匹配大于 z 的单词，如 zahn 或 zzzzzzzzz。 针对上面的查询，有效的修补方法是将 REPEAT('<char>', <number_of_size_of_table_column_datatype>) 用作最大范围，即在本例中使用 word BETWEEN 'a' AND REPEAT('z', 255)

这是修补程序的 演示..

难道不应该是 LEFT(word,1) BETWEEN 'a' AND 'z' 吗？ - Madhur Bhaiya
“难道不应该是LEFT(word,1) BETWEEN 'a'和'z'吗？” 不，我利用MySQL的字符串转换来获得优势@MadhurBhaiya，请查看此链接https://www.db-fiddle.com/f/tCUgdpcwH7E78gviia6yqv/4 .. 此外，如果word被索引，我将失去最佳索引使用方式，使用LEFT(word,1) https://www.db-fiddle.com/f/tCUgdpcwH7E78gviia6yqv/6 - Raymond Nijland
有趣的 +1；做了更多的实验：https://www.db-fiddle.com/f/tCUgdpcwH7E78gviia6yqv/7 - Madhur Bhaiya
感谢@dnoeth的这次深夜对话，答案已经更新了一个补丁，现在返回正确。 - Raymond Nijland
1
“LEFT(word,1) Between 'a' and 'z'” 应该不是这样的，@MadhurBhaiya 请查看更新后的答案，确实需要一个补丁来让它在所有情况下都能正确工作，这个问题很久以前就被忽略了 :-) ... 此外，索引使用加号仍然是好的。 - Raymond Nijland
2
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接