MySql 不像正则表达式？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2579491/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySql Not Like Regexp?
提问by TwixxyKit
I'm trying to find rows where the first character is not a digit. I have this:
我正在尝试查找第一个字符不是数字的行。我有这个：
SELECT DISTINCT(action) FROM actions
WHERE qkey = 140 AND action NOT REGEXP '^[:digit:]$';
But, I'm not sure how to make sure it checks just the first character...
但是，我不确定如何确保它只检查第一个字符......
采纳答案by Chad Birch
Your current regex will match values consisting of exactly one digit, not the first character only. Just remove the $from the end of it, that means "end of value". It'll only check the first character unless you tell it to check more.
您当前的正则表达式将匹配仅由一个 digit组成的值，而不仅仅是第一个字符。只需$从它的末尾删除，这意味着“价值结束”。它只会检查第一个字符，除非你告诉它检查更多。
^[:digit:]will work, that means "start of the value, followed by one digit".
^[:digit:]会起作用，这意味着“值的开头，后跟一位数字”。
回答by Mark Byers
First there is a slight error in your query. It should be:
首先，您的查询中有一个小错误。它应该是：
NOT REGEXP '^[[:digit:]]'
Note the double square parentheses. You could also rewrite it as the following to avoid also matching the empty string:
注意双方括号。您还可以将其重写为以下内容，以避免同时匹配空字符串：
REGEXP '^[^[:digit:]]'
Also note that using REGEXP prevents an index from being used and will result in a table scan or index scan. If you want a more efficient query you should try to rewrite the query without using REGEXP if it is possible:
另请注意，使用 REGEXP 可防止使用索引，并将导致表扫描或索引扫描。如果您想要更有效的查询，您应该尝试在可能的情况下不使用 REGEXP 重写查询：
SELECT DISTINCT(action) FROM actions
WHERE qkey = 140 AND action < '0'
UNION ALL
SELECT DISTINCT(action) FROM actions
WHERE qkey = 140 AND action >= ':'
Then add an index on (qkey, action). It's not as pleasant to read, but it should give better performance. If you only have a small number of actions for each qkey then it probably won't give any noticable performance increase so you can stick with the simpler query.
然后在 (qkey, action) 上添加索引。读起来不太愉快，但它应该提供更好的性能。如果每个 qkey 只有少量操作，那么它可能不会显着提高性能，因此您可以坚持使用更简单的查询。
