How do I set default collation for all tables that are created in a database?

Question

I have a database dump taken from a broken database that I need to import into a clean installation. However, the original database seem to have been set to use utf8_unicode_ci.

When importing this dump into clean database, all databases are created with the default utf8_general_ci which gives me duplicate entries for words including ß, because general makes 'ß' == 's', while as utf8_unicode_ci supposedly have 'ß' == 'ss'.

But, when importing mysql always seems to select the default utf8_general_ci when creating the table, even though I set the database (schema) default to utf8_unicode_ci.

Is there any way to force it to create tables with utf8_unicode_ci without having to inject alter table statements in my dump? It is several GB in size and is gonna be a pain to modify manually.

Configuring MySQL system wide is fine.

I tried setting:

collation-server=utf8_unicode_ci

in my.cnf but that doesn't seem to set the default collation for table creation.

Answer 1

I was working on this same issue this morning and I was able to get the table collation set the following way.

DROP TABLE IF EXISTS `agtAgentTypes`;
CREATE TABLE `agtAgentTypes` (
  `agentTypeID` int(11) NOT NULL,
  `agentType` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`agentTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_unicode_ci;

For some reason it seems that engine and charset need an = but collation trips up if it has one. I tried it out because I noticed the MySQL Charset Examples were also not using the =.

This was tested against MySQL Community Server 5.5.32-cll-lve.

Answer 2

Apparently there is no way of forcing collation on newly created tables if you specify charset in your create statements, meaning that if you have:

CREATE TABLE foo
...
CHARSET=utf8;

It will implicitly set it to utf8_general_ci which is the default collation for that charset. This is regardless of database settings, system settings and connection settings.

I ended up invoking this:

 cat dump.sql|sed s/CHARSET=utf8/CHARSET=utf8\ COLLATE=utf8_unicode_ci/ > dump_replaced.sql

and just waited.