It happens often that I forget to change the default character set of a database to utf8 and so find out late in the development cycle that many of my fields are based on non utf8 character sets (mostly latin1). Then I go in and modify each field in turn using ALTER TABLE [table] MODIFY [field name] [field type] CHARACTER SET [charset]. After some digging I found the ALTER TABLE $TABLE CONVERT TO CHARSET [charset] syntax which converted all fields in a table to a certain character set. I looked for a similar syntax to convert the entire database and found ALTER DATABASE which, unfortunately, only changes the default character set and collation but does not affect the existing tables, fields or data.
So here is a script that repeats ALTER TABLE / CONVERT TO on each table in your database:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | #!/bin/bash # parameters... USER='[your db user name]' PASS='[your db password]' DB='[your db]' CHARSET='[character set (utf8?)]' COLLATION='[collation (utf8_unicode_ci?)]' # here we go... QUERY="SELECT table_name FROM information_schema.TABLES WHERE table_schema = '$DB';" TABLES=$(mysql -u $USER --password=$PASS $DB --batch --skip-column-names --execute="$QUERY") for TABLE in $TABLES; do echo "ALTER TABLE $TABLE ......" mysql -u $USER --password=$PASS $DB -e "ALTER TABLE $TABLE CONVERT TO CHARSET $CHARSET" #mysql -u $USER --password=$PASS $DB -e "ALTER TABLE $TABLE CONVERT TO CHARSET $CHARSET COLLATE $COLLATION" done |
Hi Mark. You should check my openark-kit toolkit, which has a similar yet more thorough solution to changing character sets.
Link | September 6th, 2010 at 5:36 pm
Shlomi,
I will check it out, but first let me congratulate you on being the first comment on my entire desolate blog!
Please, please, comment as often as you want…
Link | September 6th, 2010 at 8:27 pm
May I suggest you get your blog aggregated somewhere?
For example, if you’re writing on MySQL, make a “MySQL” category on WordPress, assign posts to this category, then go to planet.mysql.com and enlist your “MySQL” category RSS feed. (or you can do the same with using tags).
This means any further post you have about MySQL is then published on planet.mysql.com.
Find other “planet”s, or other aggregation sites; enlist there as well.
Link | September 7th, 2010 at 8:58 am
e.g. use:
http://veltzer.net/blog/blog/tag/mysql/feed/rss/
Link | September 7th, 2010 at 8:59 am
Hi Shlomi,
SEO is not really high on my priority list right now so I will probably do that at some later date…
Link | September 12th, 2010 at 1:31 am