Migrate a MySQL Database Preserving Special Characters

TomSlick writes "Michael Chu's blog provides a good solution for people migrating their MySQL databases and finding that special characters (like smart quotes) get mangled. He presents two practical solutions to migrating the database properly."

Comments

Re:Unicode integration woes

[Anonymous Coward]

However they are almost always caused by poor programmers who mixes usage between the two

Yes, and in this case the poor programmers wrote the mysqldump program.

What's with the sudden influx of gnubies?

[rylin]

Not a single day seems to go by without a gnubie or two posting things that are really basic knowledge.
If you do insert unicode data into a latin1 table, you will get unexpected results.

What you do is make sure that your:
a) database(s) are set to utf8 by default
b) table(s) are set to utf8 by default
c) column(s) are set to utf8 by default
d) connection defaults to utf8
(provided, of course, that it's utf8 you're after)

That way, it'll "Just Work"(tm)

We've gone through upgrades from 3.23 -> 4.0 -> 4.1 -> 5.0 and never had a problem; and yes, our tables were all latin1 from the beginning.

Re:What's with the sudden influx of gnubies?

[cortana]

Not a single day seems to go by without a gnubie or two posting things that are really basic knowledge.
If you do insert unicode data into a latin1 table, you will get unexpected results. Ah, I love MySQL. They should fix it so that if you insert unicode data where latin1 data is expected, you get an error instead of silent data corruption.