Arbeitsnotizen zu: MySQL fulltext search hinzufügen und collation

TL;DR: Hinzufügen eines MySQL Fulltextindex bei bestehenden Daten ignoriert die collation. Optimize table muss danach ausgeführt werden.

MySQL Fulltext search sollte bekannt sein. Ebenso folgender Hinweis:

By default, the search is performed in case-insensitive fashion. To perform a case-sensitive full-text search, use a case-sensitive or binary collation for the indexed columns. For example, a column that uses the utf8mb4 character set of can be assigned a collation of utf8mb4_0900_as_cs or utf8mb4_bin to make it case-sensitive for full-text searches.

Nun bin ich auch ein Verhalten gestoßen das mich verwunderte.

Nach einem nachträglichen hinzufügen eines Fulltextindex, Daten sind schon vorhanden, lieferte ein Query nicht das was ich erwartete.

CREATE TABLE `title_basics` (
`tconst` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`titleType` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`primaryTitle` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`originalTitle` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`isAdult` tinyint(1) NOT NULL,
`startYear` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`endYear` char(4) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`runtimeMinutes` int NOT NULL,
`genres` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
UNIQUE KEY `tconst` (`tconst`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

Hinzufügen des Fulltextindex:

ALTER TABLE title_basics ADD FULLTEXT (primaryTitle);

Nun sollte man mit der erstellen Tabelle einen case sensitive Suchvorgang erwarten. Leider war dies nicht der Fall. Erst nach dem ich die Spalte auf dem der Index basiert wie folgt angepasst habe:

ALTER TABLE title_basics MODIFY primaryTitle VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

Wie man erkennen kann hat sich da nichts geändert. Die Optionen sind die selben wie bei der Erstellung der Tabelle.

Alternativ geht auch ein OPTIMIZE TABLE title_basics; anstelle des ALTER TABLE. fulltext-rebuild-innodb-indexes

Warum auch immer wird bei der nachträglichen Erstellung des Fulltextindex das COLLATE ignoriert und der Fallback case-insensitive genommen.