Storing special characters (Internationalization) in MySQL

I have been unable to store this text in a MySQL database:

𝒜wesome Iñtërnâtiônàlizætiøn​:snowman_with_snow::poop:

I tried having my field use the “utf8_general_ci” collation and also “utf8mb4_general_ci”, but neither worked. I get this error:

Warning: #1366 Incorrect string value: '\xF0\x9D\x92\x9Cwe...' for column 'title' at row 1

Any suggestions?

If the datatype is varchar, try nvarchar. I think the special characters are double-bit.

I’m using phpMyAdmin with MySQL 5.5.37 and I don’t see that as a field type.

Your problem is to do with character encoding. Either your table, database connection, or source code file (or a combination of them) are not using the utf-8 encoding scheme. Could you check and verify whether any of them aren’t using utf-8?

I’m actually just using phpMyAdmin for the time being. When I first go to phpMyAdmin, it says this in the “MySQL” section:

MySQL charset: UTF-8 Unicode (utf8)

The database’s collation is “utf8_general_ci” and so is the field’s.

How about the web page? I use Spanish (which, as you know, makes use of special characters) and I never had a problem except when there were differences between the charset used in the web page, the database, the table or the field itself

I don’t have a webpage for this database yet. Just setting things up. I’m trying to insert rows into the database via phpMyAdmin.

phpMyAdmin uses a web page to interact with the database. Look at the charset used in the page where you will hit the button to submit the new rows. Right clic and then choose “view code” and see what charset is set

I confess that it is rare but it is better to be sure

You mean “View Source”? This is there:

<meta http-equiv="Content-Type"
    content="text/html; charset=utf-8" />

And the response headers have this:

Content-Type: text/html; charset=utf-8

I believe the answer is to use utf8mb4 everywhere:

https://mathiasbynens.be/notes/mysql-utf8mb4

Did you try? Has it worked?

No, I haven’t gotten to it. I’m actually looking to switch to PostgreSQL for other reasons.

I still find it strange that you had a problem with this. I confess I never had a problem when the table and the web page that I was using to do the job had the same character set.

So the only thing I could think of that maybe you’ve got hidden characters that you’re not aware of, characters that do not belong to UTF-8

This would make sense depending the origin of the text

But of course, if you’re moving to PostgreSQL, you may do a better use of your time

This is the text I was testing with: 𝒜wesome Iñtërnâtiônàlizætiøn​:snowman_with_snow::poop:

I think the difficulties usually occur with the special “A” (U+1D49C) in “Awesome”, the snowman (U+2603), and/or the pile of poo (U+1F4A9).