具有范围的MySQL LIKE无法正常工作 [英] MySQL LIKE with range doesn't work

问题描述

我有一个数据库表 mytable ，其中的列 name 为Varchar格式，而列 date 则为Datetime值.我想使用按日期分组的某些参数来计算名称.这是我的工作:

I've got a database table mytable with a column name in Varchar format, and column date with Datetime values. I'd like to count names with certain parameters grouped by date. Here is what I do:

SELECT
    CAST(t.date AS DATE) AS 'date',
    COUNT(*) AS total,
    SUM(LENGTH(LTRIM(RTRIM(t.name))) > 4 
        AND (LOWER(t.name) LIKE '%[a-z]%')) AS 'n'
FROM
    mytable t
GROUP BY 
    CAST(t.date AS DATE)

这里的范围语法似乎有问题，如果我只是执行LIKE 'a%'的话，它确实可以正确计数以'a'开头的所有字段.但是，上面的查询为n返回0，尽管应该计算所有至少包含一个字母的字段.

It seems that there's something wrong with range syntax here, if I just do LIKE 'a%' it does count properly all the fields starting with 'a'. However, the query above returns 0 for n, although should count all the fields containing at least one letter.

推荐答案

您编写:

这里的范围语法似乎有问题

的确如此. MySQL的LIKE运算子(通常是SQL)不支持范围符号，只是简单的通配符.

Indeed so. MySQL's LIKE operator (and SQL generally) does not support range notation, merely simple wildcards.

尝试使用MySQL的非标准 RLIKE (又名REGEXP) ，用于更全面的模式匹配.

Try MySQL's nonstandard RLIKE (a.k.a. REGEXP), for fuller-featured pattern matching.

这篇关于具有范围的MySQL LIKE无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！