What is the difference between MySQL's REGEXP and LIKE?

1. The difference in matching content
LIKE requires the entire data to match. With Like, all the contents of this field must meet the conditions;

REGEXP only requires part Matching is enough, as long as any one fragment is satisfied.

2. Differences in matching positions

LIKE matches the entire column. If the matched text appears in the column value, LIKE will not find it. Correspondingly Rows will not be returned (unless wildcards are used);

REGEXP matches within the column value. If the matched text appears in the column value, REGEXP will find it and the corresponding row will be returned. And REGEXP can match the entire column value (same effect as LIKE).

3. The difference between the data returned by the SQL statement

LIKE matching: the SQL statement will not return data;

REGEXP matching: the SQL statement will return One row of data;

4. Speed ​​difference

There is a table with more than 100,000 pieces of data. An example of a certain column of data is as follows:

100000-200000-300001
100000-200000-300002
100000-200001-300003
100000-200001-300004
100000-200002-300005
当查询数据中含有 200001 时，
用LIKE 时sql：colName LIKE'%200001 %' ，用时4秒左右
用正则时sql：colName REGEXP '^.*200001 -.*$'，用时2秒左右

The above is the detailed content of What is the difference between MySQL's REGEXP and LIKE?. For more information, please follow other related articles on the PHP Chinese website!