Reggie Thomson’s Diary

I’ve just noticed that individual tables are set to the wrong collation in phpmyadmin. I will have to search through each table in every database and convert all the text and varchar settings.

I decided I had to change all the collations on the databases and tables.

Using phpMyAdmin, I ran the command

show databases

Then I copy the results to Textpad and use the following regular expressions:

# ^([^#^\n])(.*)\n => \1\2\n\n
# ^([^#^\n])(.*)\n => ALTER DATABASE \1\2 COLLATE utf8_general_ci ;\n

Similarly for each table in each database:

show tables

# ^([^#^\n])(.*)\n => \1\2\n\n
# ^([^#^\n])(.*)\n => ALTER TABLE \1\2 COLLATE utf8_general_ci ;\n