1

In our MySQL database we have set several collations. e.g.

  • Connection collation utf8_unicode_ci
  • Server configured default collation utf8_unicode_ci
  • Database default collation utf8_general_ci
  • Table default collation utf8_unicode_ci, utf8_general_ci
  • Column collation utf8_spanish_ci
  • Trigger collation utf8_general_ci

This leads to collation mismatch errors in some occasions. Here my question:

Question 1

Is the assumption correct that the collation of a cell is inherited?

e.g. trigger / connection collation > column collation > table default collation > database default collation > server default collation

Question 2

How do I streamline the collation settings and set all collations to utf8_unicode_ci but exclude cells set to utf8_bin?

The best result would be a database default character set and collation, and no explicit settings in tables or columns

e.g.

Database

CREATE DATABASE `search` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Original Table

CREATE TABLE `searchIndex` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(50) CHARACTER SET utf8 COLLATE utf8_spanish_ci NOT NULL DEFAULT ''
  `keywordCI` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''
  PRIMARY KEY (`id`),
  KEY `eintragIndex_idx_keyword` (`keyword`),
) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Desired resulting table

CREATE TABLE `searchIndex` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(50) NOT NULL DEFAULT ''
  `keywordCI` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT ''
  PRIMARY KEY (`id`),
  KEY `eintragIndex_idx_keyword` (`keyword`),
)

Trying to achieve this with

-- set connection collation
SET NAMES utf8 COLLATE utf8_unicode_ci;
-- set default database collation
ALTER DATABASE search DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
-- convert table, explicit setting of the utf8_bin collation
ALTER TABLE `searchIndex` CONVERT TO CHARACTER SET DEFAULT, MODIFY keywordCI varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

But this results in the following table definition (wrong collation for keywordCI, table charset and collation still set explicit)

CREATE TABLE `searchIndex` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `keywordCI` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `eintragIndex_idx_keyword` (`keyword`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

With the following queries I get the desired result, but I don't understand why - I am expecting to see the collation setting since the connection and default collation of the database is different

-- Convert to utf8 with utf8_GENERAL_ci collation
ALTER TABLE eintragIndex CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
-- In a separate query, modify the keywordCI collation
ALTER TABLE eintragIndex MODIFY keywordCI varchar(50) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';

I am currently working on MySQL 5.6.10

mysql> SELECT VERSION();
+------------+
| VERSION()  |
+------------+
| 5.6.10-log |
+------------+

mysql> show variables like '%collation%';                                                                                                                                                                                                      +----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_unicode_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.04 sec)
  • A guess: When mixing CONVERT TO and MODIFY COLUMN in the same ALTER statement, things are done in the wrong order. Suggest filing a bug at bugs.mysql.com . (You obviously have a workaround.) – Rick James Apr 23 '16 at 20:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.