Editing UTF-8 data

Hey!

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!

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:

E282AC

Typo: it should return

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?

Typo: UTF-8 (not with the smiley)

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(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
//PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', <-- Missing
));

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.

Ah, I wasn't aware that "SET NAMES utf8" was needed. Thank you, the data is now consistent!