Re: Change default collation for charset?

[Niko Huffman]

Alternately, is there a way to ensure that the database default collation is applied when a table is created and only the default character set is specified?

In the following script, table1, table3, and table4 are created with the correct character set and collation, but table2 ends up with a default collation of utf8_general_ci, presumably because of the default collation for the utf8 character set being defined in INFORMATION_SCHEMA as utf8_general_ci, and the engine is using that default rather than the database default.


CREATE DATABASE `db1`
DEFAULT CHARACTER SET `utf8`
DEFAULT COLLATE `utf8_unicode_ci`;

USE db1;

DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
`col1` char(1) NOT NULL
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `table2`;
CREATE TABLE `table2` (
`col1` char(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`utf8`;

DROP TABLE IF EXISTS `table3`;
CREATE TABLE `table3` (
`col1` char(1) NOT NULL
) ENGINE=InnoDB DEFAULT COLLATE `utf8_unicode_ci`;

DROP TABLE IF EXISTS `table4`;
CREATE TABLE `table4` (
`col1` char(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`utf8` DEFAULT COLLATE `utf8_unicode_ci`;