Recently I came across with issue where migrating website from one hosting to another resulted on text encoding issues. Site was very old and lot of time had passed, so on new server all texts coming from database had cryptic signs instead of Nordic characters.
All you can find below is from article on page https://confluence.atlassian.com/kb/how-to-fix-the-collation-and-character-set-of-a-mysql-database-744326173.html
Very often once found source is hard to find next time, so this blog servers also as bookmarking for me 🙂
So in short what helped me was:
Changing the Database Collation
Change yourDB to suit your database name:
|
1 |
[crayon-647bd8689f7ca989422476 inline="true" class=" language-sql"]<span class="token keyword">ALTER</span> <span class="token keyword">DATABASE</span> yourDB <span class="token keyword">CHARACTER SET</span> utf8 <span class="token keyword">COLLATE</span> utf8_bin |
Changing Table Collation
The following query will produce a series of ALTER TABLE statements, which you must then run against your database. Change yourDB to suit your database name:
|
1 2 3 4 5 6 7 8 9 10 |
[crayon-647bd8689f7da669793745 inline="true" class=" language-sql"]<span class="token keyword">SELECT</span> CONCAT<span class="token punctuation">(</span><span class="token string">'ALTER TABLE '</span><span class="token punctuation">,</span> table_name<span class="token punctuation">,</span> <span class="token string">' CHARACTER SET utf8 COLLATE utf8_bin;'</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span><span class="token keyword">TABLES</span> <span class="token keyword">AS</span> T<span class="token punctuation">,</span> information_schema<span class="token punctuation">.</span><span class="token punctuation">`</span>COLLATION_CHARACTER_SET_APPLICABILITY<span class="token punctuation">`</span> <span class="token keyword">AS</span> C <span class="token keyword">WHERE</span> C<span class="token punctuation">.</span>collation_name <span class="token operator">=</span> T<span class="token punctuation">.</span>table_collation <span class="token operator">AND</span> T<span class="token punctuation">.</span>table_schema <span class="token operator">=</span> <span class="token string">'yourDB'</span> <span class="token operator">AND</span> <span class="token punctuation">(</span> C<span class="token punctuation">.</span>CHARACTER_SET_NAME <span class="token operator">!=</span> <span class="token string">'utf8'</span> <span class="token operator">OR</span> C<span class="token punctuation">.</span>COLLATION_NAME <span class="token operator">!=</span> <span class="token string">'utf8_bin'</span> <span class="token punctuation">)</span><span class="token punctuation">;</span> |
Changing Column Collation
The following queries (one for varchar columns, and one for non- varchar columns) will produce a series of ALTER TABLE statements, which you must then run against your database. Change yourDB to suit your database name:
|
1 2 3 4 5 6 7 8 9 10 |
[crayon-647bd8689f7e9712468408 inline="true" class=" language-sql"]<span class="token keyword">SELECT</span> CONCAT<span class="token punctuation">(</span><span class="token string">'ALTER TABLE `'</span><span class="token punctuation">,</span> table_name<span class="token punctuation">,</span> <span class="token string">'` MODIFY `'</span><span class="token punctuation">,</span> column_name<span class="token punctuation">,</span> <span class="token string">'` '</span><span class="token punctuation">,</span> DATA_TYPE<span class="token punctuation">,</span> <span class="token string">'('</span><span class="token punctuation">,</span> CHARACTER_MAXIMUM_LENGTH<span class="token punctuation">,</span> <span class="token string">') CHARACTER SET UTF8 COLLATE utf8_bin'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword">CASE</span> <span class="token keyword">WHEN</span> IS_NULLABLE <span class="token operator">=</span> <span class="token string">'NO'</span> <span class="token keyword">THEN</span> <span class="token string">' NOT NULL'</span> <span class="token keyword">ELSE</span> <span class="token string">''</span> <span class="token keyword">END</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">';'</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span><span class="token keyword">COLUMNS</span> <span class="token keyword">WHERE</span> TABLE_SCHEMA <span class="token operator">=</span> <span class="token string">'yourDB'</span> <span class="token operator">AND</span> DATA_TYPE <span class="token operator">=</span> <span class="token string">'varchar'</span> <span class="token operator">AND</span> <span class="token punctuation">(</span> CHARACTER_SET_NAME <span class="token operator">!=</span> <span class="token string">'utf8'</span> <span class="token operator">OR</span> COLLATION_NAME <span class="token operator">!=</span> <span class="token string">'utf8_bin'</span> <span class="token punctuation">)</span><span class="token punctuation">;</span> |
|
1 2 3 4 5 6 7 8 9 10 |
[crayon-647bd8689f7ee725003535 inline="true" class=" language-sql"]<span class="token keyword">SELECT</span> CONCAT<span class="token punctuation">(</span><span class="token string">'ALTER TABLE `'</span><span class="token punctuation">,</span> table_name<span class="token punctuation">,</span> <span class="token string">'` MODIFY `'</span><span class="token punctuation">,</span> column_name<span class="token punctuation">,</span> <span class="token string">'` '</span><span class="token punctuation">,</span> DATA_TYPE<span class="token punctuation">,</span> <span class="token string">' CHARACTER SET UTF8 COLLATE utf8_bin'</span><span class="token punctuation">,</span> <span class="token punctuation">(</span><span class="token keyword">CASE</span> <span class="token keyword">WHEN</span> IS_NULLABLE <span class="token operator">=</span> <span class="token string">'NO'</span> <span class="token keyword">THEN</span> <span class="token string">' NOT NULL'</span> <span class="token keyword">ELSE</span> <span class="token string">''</span> <span class="token keyword">END</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token string">';'</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> information_schema<span class="token punctuation">.</span><span class="token keyword">COLUMNS</span> <span class="token keyword">WHERE</span> TABLE_SCHEMA <span class="token operator">=</span> <span class="token string">'yourDB'</span> <span class="token operator">AND</span> DATA_TYPE <span class="token operator">!=</span> <span class="token string">'varchar'</span> <span class="token operator">AND</span> <span class="token punctuation">(</span> CHARACTER_SET_NAME <span class="token operator">!=</span> <span class="token string">'utf8'</span> <span class="token operator">OR</span> COLLATION_NAME <span class="token operator">!=</span> <span class="token string">'utf8_bin'</span> <span class="token punctuation">)</span><span class="token punctuation">;</span> |
In addition (this was not needed in my case)
Dealing with Foreign Key Constraints
It may be necessary to ignore foreign key constraints when making changes to a large number of columns. You can use the SET FOREIGN_KEY_CHECKS command to ignore foreign key constraints while you update the database.
|
1 2 3 4 5 |
[crayon-647bd8689f7f6429065917 inline="true" class=" language-sql"]<span class="token keyword">SET</span> FOREIGN_KEY_CHECKS<span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span> <span class="token comment" spellcheck="true">-- Insert your other SQL Queries here...</span> <span class="token keyword">SET</span> FOREIGN_KEY_CHECKS<span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span> |
Once more big thanks to article author(s)
Next time we will have look what had to be done with text files what were also in wrong encoding and php scripts what had to be migrated from using mysql library to mysqli.
Pingback: Convert files encoding ASCII with special character to UTF-8 file - Software Blog