在行ID中搜索大型Mysql数据库的确切日期

Question

I have a large mysql database which is about 10gb large. One of the tables in the database is called

clients

In that table there is a colum named

case

The date this client is created is mixed into the number within this column.

Here is an example of an entry in case

011706-0001

The 06 part means this client was created in 2006. I need to pull all the clients that were created in 2015 and 2016. So I need to query for anything that case has a 15 or 16 before the dash.

For example, 000015-0000 or 000016-0000

Is there a way to do this with only mysql? My thought process was I would have to query the whole column then use php to preg_match()

I am worried that based on the size of the database this would cause problems.

Answer 1

To locate rows that have a case column value that contains '06-' (the characters 0 and 6 followed by a dash ...

One option is to use a LIKE comparison operator:

 SELECT ...
   FROM clients t
  WHERE t.case LIKE '%06-%'
  ORDER BY ...

The percent sign characters are wildcards in the LIKE comparison, which match any number of characters (zero, one or more.)

MySQL will need to evaluate that condition for every row in the table. MySQL can't make use of an index range scan operation with that.

 SELECT ...
   FROM clients t
  WHERE t.case LIKE '%15-%'
     OR t.case LIKE '%16-%'
  ORDER BY ...

That will evaluate to true for any values that include the sequence of three characters '15-' or '16-'.

If there's a more standard format for the values in the case column, where the value always starts with exactly six characters representing date 'mmddyy-nnnnn' and you only want to match the 5th thru 7th characters, you could use the underscore wildcard character which matches any one character (in the LIKE comparison) for example... using four underscores

  t.case LIKE '____16-%'

Or you could use a SUBSTR function to extract the three characters from the case value, and perform an equality comparison...

  SUBSTR(t.case,5,3) = '15-'

  SUBSTR(t.case,5,3) IN ('15-','16-')

It's also possible to make use of a REGEXP comparison in place of the LIKE comparison.

---

In terms of performance, all of the above approaches are going to need to crank through every row in the table, to evaluate the comparison condition.

If that date value was stored as a separate column, as a DATE datatype, and there was an index with that as the leading column, then MySQL could make effective use of a range scan operation, for a query like this...

  WHERE t.casedate >= '2015-01-01'
    AND t.casedate <  '2017-01-01'