MySQL selecting string with special characters

问题:

I'm having a problem selecting strings from database. The problem is if you have McDonald's in row and if you are searching with a string mcdonalds it wouldn't find any results. Any suggestions?

I forgot to mention that I'm using LIKE in WHERE sentence.

回答1:

If your search requirements are to ignore certain characters, you can remove them during a search by replaceing them with a blank.

This answer solves your problem:

SELECT *
FROM restaurants
WHERE replace(name, '''', '') like '%mcdonalds%'; -- This will match "McDonald's"

FYI, a single quote literal (') is written as a doubled single quote (''), so to specify a single quote as a parameter to replace you need four quotes in a row ('''') - two at each end and the doubled quote in the middle for the actual quote.

回答2:

Escape your special characters.

SELECT *
FROM restaurants
WHERE name='McDonald\'s';

回答3:

I believe you are looking for the LIKE operator:

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

EDIT:

I tried this a lot of different ways and my only recommendation is to use whatever code you might have that is sending the query to first add '\' escape to the appropriate characters. PHP does this well:

http://www.php.net/manual/en/function.mysql-real-escape-string.php

...but that is just guessing at the full context of the situation.