mysql - sql search for substring using LIKE vs locate() -

i'm trying write query find string contains substring. what's difference between following 2 methods?

1. select * table names '%bob%'
2. select * table locate('bob', names)

i first learned use like, having "illegal mix of collations.." error saying i'm comparing english (latin) , chinese (big5). while googling methods fix collation problem came across locate(), didn't complain collations when tried it. wondering if can keep using locate() or if there's downsides it.

to large extent, matter of taste.

the important consideration like ansi standard sql , supported (if not all) databases. locate specific mysql, not portable other databases.

some databases optimize expression x 'y%' use index. mysql doesn't, not consideration you.

another advantage like supports initial matches ('y%') , final matches ('%y') intermediate matches. 1 method three. equivalent statements using locate more complex.

my preference use like, because of portability. however, not typically confronted dealing different character sets. may better off learning collations in mysql in trying find work-arounds.