Editing UTF-8 data

Dragory posted 6 years ago in Feature discussion

I tried to edit a database I have in utf8_unicode_ci collation (and so are all of the tables and their columns), but the unicode characters I insert are not encoded in UTF-8 when sent to the server, and thus display incorrectly on a website later on. Is there a way to change the character set of HeidiSQL itself?

Unicode characters inserted as UTF-8 from other sources also display with some weird symbols when viewing with HeidiSQL.

Thanks in advance!
kalvaro posted 6 years ago
HeidiSQL works fine with UTF-8. I use it all the time.

Are you sure your existing data is correctly stored? There's a simple test you can do: save the euro symbol (€) from your other source and run a query with the HEX() function, e.g.:

SELECT HEX(column_name) FROM table_name WHERE record_id=123

If you return this:

kalvaro posted 6 years ago
Typo: it should return
Dragory posted 6 years ago
Thanks for the reply!

The one I inserted myself from HeidiSQL returned E282AC, but the one inserted via a query in a PHP file returned C3A2E2809AC2AC. The latter one works and displays correctly in browsers (site's charset is set to UTF8), but incorrectly in HeidiSQL (shown as "€" there).

Any idea what I might be doing wrong?
Dragory posted 6 years ago
Typo: UTF-8 (not with the smiley)
kalvaro posted 6 years ago
I'm afraid that your data is already corrupted. I've seen that situation several times: some PHP code is missing the "UTF-8" bit somewhere, typically in the connection settings. E.g. for PDO:

$conn = new PDO('mysql:host=localhost;dbname=database', 'user', 'pass', array(

Your browser receives from the user and sends € to MySQL, but the same process is done when retrieving data so nobody notices because data looks good from within the app.
Dragory posted 6 years ago
Ah, I wasn't aware that "SET NAMES utf8" was needed. Thank you, the data is now consistent!smile

Please login to leave a reply, or register at first.