If you have double-encoded UTF8 characters (various smart quotes, dashes, apostrophe â€TM, quotation mark “, etc), in mysql you can dump the data, then read it back in to fix the broken encoding.
Like this:
mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
--skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql
mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
--default-character-set=utf8 DB_NAME < DB_NAME-dump.sql
This was a 100% fix for my double encoded UTF-8.
Source: http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/
If you have double-encoded UTF8 characters (various smart quotes, dashes, apostrophe â€TM, quotation mark “, etc), in mysql you can dump the data, then read it back in.
Like this:
mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
--skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql
mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
--default-character-set=utf8 DB_NAME < DB_NAME-dump.sql
This was a 100% fix for my double encoded UTF-8.
Source: http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/
If you have double-encoded UTF8 characters (various smart quotes, dashes, apostrophe â€TM, quotation mark “, etc), in mysql you can dump the data, then read it back in to fix the broken encoding.
Like this:
mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
--skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql
mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
--default-character-set=utf8 DB_NAME < DB_NAME-dump.sql
This was a 100% fix for my double encoded UTF-8.
Source: http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/
- 2.7k
- 27
- 32
I realize this is an old question, but it kept coming up in Google searches when I was trying to resolve a similar problem -- and the actual solution that ended up working for me wasn't posted here. So I figured I'd post an answer here in case some other hapless developer stumbles upon this thread and it works for them...
Anyhow, my problem was that I hadIf you have double-encoded UTF8 characters that snuck in during a migration, and were not discovered until it was too late. I needed to find a solution that allowed me to fix the broken encoding directly in the mysql DB (in other words I didn't have the option of re-exporting the data from the original).
My problem was mostlyvarious smart quotes, dashes and that sort of thing. I had characters such as â€TM instead of an, apostrophe or “ instead of aâ€TM, quotation mark “, etc), in mysql you can dump the data, then read it back in.
The solution -- God bless the developer who posted it to his blog -- was far easier than any I have seen posted elsewhere. Here it isLike this:
mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
--skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql
mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
--default-character-set=utf8 DB_NAME < DB_NAME-dump.sql
That's it. Dump the data out, then import it back in. This resolved the problem for mewas a 100% fix for my double encoded UTF-8.
Source: http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/
I realize this is an old question, but it kept coming up in Google searches when I was trying to resolve a similar problem -- and the actual solution that ended up working for me wasn't posted here. So I figured I'd post an answer here in case some other hapless developer stumbles upon this thread and it works for them...
Anyhow, my problem was that I had double-encoded UTF8 characters that snuck in during a migration, and were not discovered until it was too late. I needed to find a solution that allowed me to fix the broken encoding directly in the mysql DB (in other words I didn't have the option of re-exporting the data from the original).
My problem was mostly smart quotes, dashes and that sort of thing. I had characters such as â€TM instead of an apostrophe or “ instead of a quotation mark.
The solution -- God bless the developer who posted it to his blog -- was far easier than any I have seen posted elsewhere. Here it is:
mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
--skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql
mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
--default-character-set=utf8 DB_NAME < DB_NAME-dump.sql
That's it. Dump the data out, then import it back in. This resolved the problem for me 100%.
Source: http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/
If you have double-encoded UTF8 characters (various smart quotes, dashes, apostrophe â€TM, quotation mark “, etc), in mysql you can dump the data, then read it back in.
Like this:
mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
--skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql
mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
--default-character-set=utf8 DB_NAME < DB_NAME-dump.sql
This was a 100% fix for my double encoded UTF-8.
Source: http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/
I realize this is an old question, but it kept coming up in Google searches when I was trying to resolve a similar problem -- and the actual solution that ended up working for me wasn't posted here. So I figured I'd post an answer here in case some other hapless developer stumbles upon this thread and it works for them...
Anyhow, my problem was that I had double-encoded UTF8 characters that snuck in during a migration, and were not discovered until it was too late. I needed to find a solution that allowed me to fix the broken encoding directly in the mysql DB (in other words I didn't have the option of re-exporting the data from the original).
My problem was mostly smart quotes, dashes and that sort of thing. I had characters such as â€TM instead of an apostrophe or “ instead of a quotation mark.
The solution -- God bless the developer who posted it to his blog -- was far easier than any I have seen posted elsewhere. Here it is:
mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
--skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql
mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
--default-character-set=utf8 DB_NAME < DB_NAME-dump.sql
That's it. Dump the data out, then import it back in. This resolved the problem for me 100%.
Source: http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/