MySql 不像正则表达式？

发布于 2024-08-28 06:13:05 字数 184 浏览 5 评论 0原文

我正在尝试查找第一个字符不是数字的行。我有这个：

SELECT DISTINCT(action) FROM actions 
WHERE qkey = 140 AND action NOT REGEXP '^[:digit:]$';

但是，我不确定如何确保它只检查第一个字符......

原文

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...  
;

But, I'm not sure how to make sure it checks just the first character...

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

许仙没带伞 2024-09-04 06:13:05

首先，您的查询中有一个小错误。它应该是：

NOT REGEXP '^[[:digit:]]'

注意双方括号。您还可以将其重写为以下形式，以避免也匹配空字符串：

REGEXP '^[^[:digit:]]'

另请注意，使用 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 >= ':'

然后在 (qkey, action) 上添加索引。虽然读起来不太愉快，但它应该能提供更好的性能。如果每个 qkey 只有少量操作，那么它可能不会带来任何显着的性能提升，因此您可以坚持使用更简单的查询。

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:

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.

回复收藏 0 原文