更新mysql表编码特殊字符

Question

I have a mysql table called "Movies". I just found that some movies contains special characters. For example, there are some movies that I crawled and inserted in my table such as 'WALL·E' or 'Mr. and Mrs. Smith', but they are inserted in DB as 'WALL & middot ; E' and 'Mr. & amp ; Mrs. Smith'. This caused a problem in my survey application, because for example I have an autocompletion field in my survey where user should insert his/her favorite movie name, so if user try to find WALL·E, he has to insert 'WALL & middot ; E'!!

Is there any way (preferably in mysql) that I can update my table and replace all movies that contains special characters with their encoded names?

Thanks,

Answer 1

It's not a MySQL issue.

The issue is the values you are storing to the database.

If you want to "decode" the HTML entities in a string into the characters they represent, you can use PHP html_entity_decode function on the value you've scraped from web pages, and then store that value in the database.

Reference: http://php.net/manual/en/function.html-entity-decode.php

---

If you want to store the values in the database as encoded HTML entities, then you could search for those by taking whatever value the user is searching for, and encoding that. (That probably wasn't clear).

If the user is searching for "Mr. & Mrs. Smith", you could encode that using the PHP htmlentities function, to return:

 Mr. & Mrs. Smith

And use that as the search string.

The downside of this approach is that there are multiple ways to encode the same character. And the way that htmlentities encodes a string may not match what was done on the webpage. It's possible that some characters may not be encoded at all, etc.