MySQL Where like


MySQL LIKE syntax

The LIKE operator is used in a WHERE expression to search for the specified content in a matching field, with the following syntax:

 WHERE column LIKE pattern
 WHERE column NOT LIKE pattern

LIKE in front of the operation with the NOT operator, that means the opposite with the meaning of LIKE, select the column does not contain pattern data records.

LIKE is usually used with the wildcard%,%that does not appear in the wildcard pattern. Without the wildcard%LIKE syntax, which means exact match, the actual effect is equivalent to the = operator.


Examples

uid username
1John
2Jack
3Marry
4David
5Jan
 SELECT * FROM user WHERE username LIKE 'ja%'

The results are as follows:

uid username
2Jack
5Jan

The example is to find out all the username to "ja" at the beginning of the record

The following example, will query out all the fields anywhere in the username contains a character record:

 SELECT * FROM user WHERE username LIKE '%a%'

MySQL LIKE case

MySQL LIKE matching characters, the default is not case-sensitive, if you need to match the case-sensitive, you can use the BINARY operator:
 SELECT * FROM user WHERE username LIKE BINARY '%azz%'
 SELECT * FROM user WHERE username LIKE BINARY '%aZZ%'

The BINARY operator represents a binary comparison, so the operator can be strictly case-sensitive, so the contents of the two SQL queries above are different.


The efficiency of the LIKE operator

LIKE operator to field data to scan one by one match, the actual implementation of the efficiency is poor, even if the field has been built index(a%this approach will use the index). When the amount of data is large, as far as possible to reduce the use of LIKE operator, there is not much room for optimization.




Copyright 2014-2017 by DeveloperTut.com. All Rights Reserved.
TwitterGoogle+Facebook