MySQL LIKE with range doesn't work

问题:

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)

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.

回答1:

You write:

It seems that there's something wrong with range syntax here

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

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

回答2:

I believe LIKE is just for searching for parts of a string, but it sounds like you want to implement a regular expression to search for a range.

In that case, use REGEXP instead. For example (simplified):

SELECT * FROM mytable WHERE name REGEXP "[a-z]"

Your current query is looking for a string of literally "[a-z]".

Updated:

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

回答3:

I believe you want to use WHERE REGEXP '^[a-z]$' instead of LIKE.

回答4:

You have regex in your LIKE statement, which doesn't work. You need to use RLIKE or REGEXP.

SELECT CAST(t.date AS DATE) AS date,
    COUNT(*) AS total
FROM mytable AS t
WHERE t.name REGEXP '%[a-zA-Z]%' 
GROUP BY CAST(t.date AS DATE)
HAVING SUM(LENGTH(LTRIM(RTRIM(t.name))) > 4

Also just FYI, MySQL is terrible with strings, so you really should trim before you insert into the database. That way you don't get all that crazy overhead everytime you want to select.