假设我有一张 table :

当前表:

title_id        title_name               title_qty
   1       A.I. Artificial Intelligence      2
   2       Batman Begins                    40
   3       2012                              7
   4       101 Dalmatians                   23
   5       Act of Valor                      1
   6       Batman                           50
   7       20 Million Miles to Earth       340

我想要一个输出:

期望的输出:

   title_id        title_name               title_qty  title_char
       4          101 Dalmatians                23          #
       7          20 Million Miles to Earth     340         #
       3                2012                    7           #
       1          A.I. Artificial Intelligence  2           A
       5          Act of Valor                  1           A
       6          Batman                        50          B
       2          Batman Begins                 40          B

根据我在 Stack Overflow 中阅读的内容，检查字符是否为数字的最快方法是使用 LIKE '[0-9]%' 所以我想到了

查询:

SELECT *, CASE WHEN LEFT(title_name,1) LIKE '[0-9]%' THEN "#" ELSE LEFT(title_name,1) END as title_char FROM title ORDER BY title_name

输出:

title_id        title_name               title_qty  title_char
   4          101 Dalmatians                23          1
   7          20 Million Miles to Earth     340         2
   3                2012                    7           2
   1          A.I. Artificial Intelligence  2           A
   5          Act of Valor                  1           A
   6          Batman                        50          B
   2          Batman Begins                 40          B

如上所示，根本不起作用。但是，如果我将 LIKE 更改为匹配单个数字，它就会起作用:

SELECT *, CASE WHEN LEFT(title_name,1) LIKE '1%' THEN "#" ELSE LEFT(title_name,1) END as title_char FROM title ORDER BY title_name

title_id        title_name               title_qty  title_char
   4          101 Dalmatians                23          #
   7          20 Million Miles to Earth     340         2
   3                2012                    7           2
   1          A.I. Artificial Intelligence  2           A
   5          Act of Valor                  1           A
   6          Batman                        50          B
   2          Batman Begins                 40          B

我最初认为它不起作用，因为我的查询有点复杂，所以我做了一个简单的 SELECT WHERE。 LIKE 添加在 WHERE 之后。

没有输出(应该是三个的时候):

SELECT * FROM title WHERE title_name LIKE '[0-9]%' ORDER BY title_name 

按预期工作:

SELECT * FROM title WHERE title_name LIKE '1%' ORDER BY title_name 

SELECT * FROM title WHERE title_name LIKE '2%' ORDER BY title_name

根据 SQL 文档，

A character class “[...]” matches any character within the brackets. For example, “[abc]” matches “a”, “b”, or “c”. To name a range of characters, use a dash. “[a-z]” matches any letter, whereas “[0-9]” matches any digit.

任何人都知道如果您使用字符类 [0-9] 为什么它不起作用？作为初学者，如有错误请指正。但我很好奇为什么如果我使用 LIKE '1%' 或任何数字它会起作用，而 LIKE '[0-9]%' 不会给出任何结果？文档说它应该匹配任何数字吗？如果将其转换为整数是一项要求，那么它不应该适用于我的所有测试，对吗？因为我在做 LIKE 比较之前没有投它。

SQL fiddle : http://sqlfiddle.com/#!2/492906/7

最佳答案

The documentation有点困惑。我希望仔细截图可以使它更清楚:

MySQL provides standard SQL pattern matching as well as a form of pattern matching

[...]

SQL pattern matching enables you to use “_” to match any single character and “%” to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. You do not use = or <> when you use SQL patterns; use the LIKE or NOT LIKE comparison operators instead.

[...]

The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

[...]

A character class “[...]” matches any character within the brackets. For example, “[abc]” matches “a”, “b”, or “c”. To name a range of characters, use a dash. “[a-z]” matches any letter, whereas “[0-9]” matches any digit.

由于最后一句在“其他类型的模式匹配”下，它仅适用于 RLIKE你已经找到了，而不是 LIKE .

关于mysql - SQL:如果使用字符类 [0-9]，为什么 LIKE 不起作用？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/20562123/