Search code examples
mysqlcollate

Is there any point of using COLLATE per column when creating tables?

I just did an export of a MySQL database in order to duplicate it on another server. Looking at the sql script I see the following:

CREATE TABLE `X` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `name_index` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8366 
  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Do I need to use this per column when collate is set for the table?

I think the reason for why each column has this, was because I wanted to make sure I could store locale values such as æ, ø å.

Solution

  • Since your column-level collations are all the same, setting the collation at the table level will have the same effect.

    Note that collation has no effect on what you can store; rather, it affects how the results are sorted. The only reason you would want a different collation per column would be if your table contains columns with data for different locales requiring a different sort order.