mysql like 卡死,具有范围的MySQL LIKE无法正常工作

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.

解决方案

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.