Search code examples
mysqlsql-like

confusion about mysql like search and = search

I got this question when I use mysql search something. here is the detailed information.

say I got a table named test with a column named content. in a specific record, the content column holds: 

["
/^\w{2,}/","
/^[a-z][a-z0-9]+$/","
/^[a-z0-9]+$/","
/^[a-z]\d+$/"]

there is a linefeed character in the end of the lines(last line excluded) so when I used the like syntax to search this record, I wrote a SQL like this

select * from test where `content` like 
'[\"\n/^\\\\w{2,}/\",\"\n/^[a-z][a-z0-9]+$/\",\"\n/^[a-z0-9]+$/\",\"\n/^[a-z]\\\\d+$/\"]'

and it returned the right result. but when I changed the like to = and this SQL statement didn't work, after I tried several times, I got this SQL statement that worked:

select * from test where `content` = 
'[\"\n/^\\w{2,}/\",\"\n/^[a-z][a-z0-9]+$/\",\"\n/^[a-z0-9]+$/\",\"\n/^[a-z]\\d+$/\"]'

it worked. so here is the question: why on earth the like and = have different escape strategy? in the like statement I have to use \\\\w,\\\\d while in the = statement \\w,\\d just doing fine?

Solution

  • MySQL LIKE operator to select data based on patterns.

    The LIKE operator is commonly used to select data based on patterns. Using the LIKE operator in the right way is essential to increase the query performance.

    The LIKE operator allows you to select data from a table based on a specified pattern. Therefore, the LIKE operator is often used in the WHERE clause of the SELECT statement.

    MySQL provides two wildcard characters for using with the LIKE operator, the percentage % and underscore _.

    • The percentage (%) wildcard allows you to match any string of zero or more characters.
    • The underscore (_) wildcard allows you to match any single character.

    Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.

    The following relational comparison operators can be used to compare not only scalar operands, but row operands:

    = > < >= <= <> !=

    Note: = is Equal operator and LIKE for Simple pattern matching