2

I'm using the Encoding UTF-8 Unicode (utf8mb4) and Collation utf8mb4_unicode_520_ci for both tables and fields in my MySQL database.

When I export the database from Sequel Pro and open the exported .sql file in a text editor my test character appears correctly, but when I import the file back into Sequel Pro it appears as ???? both in Sequel Pro and in my PHP/MySQL app.

In the import window I've tried Autodetect and Unicode (UTF-8) without success. Any ideas?

Also, is there any newer encoding out there that I should use instead () and is there any benefit of using utf8mb4_unicode_520_ci instead of just utf8mb4_unicode_ci?

Edit / Here's a picture of what I'm trying to do. It seems like my "odd" character is on track all the way until I'm trying to import the .sql file back into Sequel PRO.

enter image description here

SeaBass
  • 1,584
  • 4
  • 20
  • 46

1 Answers1

2

The COLLATION does not matter except for ordering. The CHARACTER SET does matter, since this is a 4-byte code.

Somehow CHARACTER SET utf8 got involved, in spite of what you say. See "question marks" in Trouble with utf8 characters; what I see is not what I stored for the likely causes.

Do SELECT HEX(...) ... to verify that that character was actually stored as hex F09D8C86.

Provide SHOW CREATE TABLE so we can verify that the column is utf8mb4.

And, let's see the connection parameters.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thanks Rick! I checked and the hex returns `F09D8C86` before I export it out of Sequel PRO, but when I import it again it returns `3F3F3F3F` (?????). `SHOW CREATE TABLE` returns `utf8mb4` before export and after import. Have I missed any settings in Sequel PRO? – SeaBass Nov 25 '17 at 19:02
  • What about the character set setting? `SET NAMES utf8mb4` (or equivalent) is needed to announce that the client has 4-byte UTF-8 characters. – Rick James Nov 26 '17 at 00:46
  • do you know how I do that in Sequel PRO? Everything is working in my php/mysql web app, and in Sequel PRO... until I make a backup/export in S PRO and import it again. I get the ???? – SeaBass Nov 26 '17 at 16:51
  • Check there various items in "Best practice" in my link. I don't know Sequel PRO. I your image, I see nothing about the _connection_ during the Import. – Rick James Nov 26 '17 at 17:12
  • It seems to be a Sequel PRO thing. I've asked in their forum. Thank you! – SeaBass Nov 26 '17 at 20:30
  • 1
    Found out that Sequel PRO can not export utf8mb4 yet, but will be able to in v1.2! – SeaBass Dec 07 '17 at 21:36
  • (``I spend too much time chasing deficiencies in 3rd-party packages that get in the way of using MySQL.``) – Rick James Dec 07 '17 at 22:14