Migrating SQL Server table to MySQL table with different encodings

Question

I have a table in SQL server that I want to move to a MySQL table. However, I want to change the default encoding from latin-1 to utf-8. I've tried running the SQL Server Import and Export Wizard, which exports a csv to a file. It fails when I select a utf-8 encoding for the "Code page". It is stored as latin-1.

Anyway, I was wondering how I could export this data and import it into MySQL as utf-8? I do not want to open a text-editor and save the exported file as UTF-8.

Which wizard? Does the export wizard create a file? What does the file look like? How is the import performed? — Rick James
– Rick James, Commented Sep 1, 2015 at 1:02
SHOW CREATE TABLE and SELECT col, HEX(col) FROM tbl WHERE ... for some small sample. That way, we can see if it is utf16 or something else. — Rick James
– Rick James, Commented Sep 1, 2015 at 1:03
The SQL Server Import and Export Wizard. So I right click a database and can select Tasks>Export data to start it up. I ran the SHOW CREATE TABLE and received a SQL_Latin1_General_CP1_CI_AS encoding. — Joyoyoyoyoyo
– Joyoyoyoyoyo, Commented Sep 1, 2015 at 16:00

Rick James · Accepted Answer · 2015-09-01 16:50:22Z

1

Plan A:

Continue to export as SQL_Latin1_General_CP1_CI_AS, but be sure to set up the import as CHARACTER SET utf8.

However, this may tricky if the CREATE TABLE statements are buried in the dump. So...

Plan B:

Export and Import as currently being done. Then, for each table, do

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8;

If you plan to handle Chinese, utf8mb4 would be better.
For many uses, the default COLLATE utf8_general_ci is fine, but you could consider utf8_unicode_ci.

answered Sep 1, 2015 at 16:50

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Migrating SQL Server table to MySQL table with different encodings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related