CODEWITHSUNDEEP
mysql
BenMorel
BenMorel

Reputation: 36494

Is CHARACTER SET redundant when specifying COLLATION in MySQL?

I've always been surprised that MySQL and related tools tend to specify both CHARACTER SET and COLLATION in CREATE TABLE AND ALTER TABLE statements:

SHOW CREATE TABLE test;

CREATE TABLE ...
ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

When I select a collation for a column in MySQL Workbench, I get:

ALTER TABLE ...
CHARACTER SET 'latin1' COLLATE 'latin1_general_ci' ...;

I've always supposed that specifying the character set and the collation was redundant, as the collation implies the character set.

Am I wrong?

When I try to mix a collation with another charset, I get an error:

CREATE TABLE ... DEFAULT CHARACTER SET utf8 collate latin1_bin;

COLLATION 'latin1_bin' is not valid for CHARACTER SET 'utf8'

Is it safe to only ever specify the collation?

If so, why do all these tools systematically include the charset in the statement, too?

Upvotes: 2

Views: 960

Answers (1)

Ondřej Šotek
Ondřej Šotek

Reputation: 1812

You are not required to specify character set when creating table, it's automatically set on collate as it described in the MySQL Reference Manual:

MySQL chooses the table character set and collation in the following manner:

  • If both CHARACTER SET charset_name and COLLATE collation_name are specified, character set charset_name and collation collation_name are used.
  • If CHARACTER SET charset_name is specified without COLLATE, character set charset_name and its default collation are used. To see the default collation for each character set, use the SHOW CHARACTER SET statement or query the INFORMATION_SCHEMA CHARACTER_SETS table.
  • If COLLATE collation_name is specified without CHARACTER SET, the character set associated with collation_name and collation collation_name are used.
  • Otherwise (neither CHARACTER SET nor COLLATE is specified), the database character set and collation are used.

The table character set and collation are used as default values for column definitions if the column character set and collation are not specified in individual column definitions. The table character set and collation are MySQL extensions; there are no such things in standard SQL.


So you can create a table using this query:

CREATE TABLE `tabletest` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 COLLATE=latin1_general_ci;

This query will create a table with CHARSET=latin1 COLLATE=latin1_general_ci, so it's safe to specify COLLATE only.

As for why are there both CHARSET and COLLATE, please read the following:

Upvotes: 6

Related Questions