Help me understand 'LIKE' vs '=' in mysql statement.

And what did your 'like' query look like?

It's quite strange that it returns less records for LIKE and more for =. If you know what they do, I won't try and explain what they are but perhaps you are doing your SQL statement wrong when using LIKE that it returns less records. Are you adding the % anywhere in your query?

Something like this:

SELECT * FROM Customer WHERE name LIKE 'a%';

@Cronix

SELECT * FROM Customer WHERE id LIKE $id

@Baryla no, i just replace '=' with like.

like w%

All names starting with w

= white

All names that are white ie., John White, Linda White, etc

@larafam that's very strange. I never came across an SQL statement with LIKE that didn't have a percent sign. Maybe that's why it was messing up for you.

Beyond just the ability to use wildcards like _ and % another difference is that LIKE compares the strings character by character while = compares the entire string at once.

Most of the time this doesn't matter but can when the strings differ in collation.

This also makes LIKE slower as it does more work.

Also LIKE is specifically for strings so using it to compare non-string data requires the data to be casted to a string. If the column is a non-string column with an index mysql wont be able to use the index and the queries will be much slower.

For example if you have a table users with a primary key id that is an integer the query SELECT * FROM users WHERE id = 2 will use the primary index which is really fast but SELECT * FROM users WHERE id LIKE 2 will cast the column and value to strings and not be able to use the integer based index and thus be much slower.

If you are not using any wildcard characters like % or _ and especially if the columns you are checking are not string-based like varchar you should pretty much always use = rather than LIKE.

@spekkionu thank u for your explanation , much appreciated :)