Search code examples
sqlmysqlindexingfull-text-searchinnodb

Can't create FULLTEXT index on VARCHAR column in MySQL using InnoDB

I am using a MySQL 8 and I have the following table:

CREATE TABLE `media` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `media_code` VARCHAR(50) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `systematic` VARCHAR(20) COLLATE utf16_general_ci NOT NULL,
  `title` VARCHAR(190) COLLATE utf16_general_ci DEFAULT NULL,
  `authors` VARCHAR(200) COLLATE utf16_general_ci DEFAULT NULL,
  `media_type` ENUM('book','cd','dvd') COLLATE utf16_general_ci DEFAULT NULL,
  `physical_format` VARCHAR(50) COLLATE utf16_general_ci DEFAULT NULL,
  `topic` VARCHAR(100) COLLATE utf16_general_ci DEFAULT NULL,
  `languages` VARCHAR(20) COLLATE utf16_general_ci DEFAULT NULL,
  `count_pages` INTEGER DEFAULT NULL,
  `publisher` VARCHAR(100) COLLATE utf16_general_ci DEFAULT NULL,
  `publisher_loc` VARCHAR(100) COLLATE utf16_general_ci DEFAULT NULL,
  `publication_year` SMALLINT DEFAULT NULL,
  `publication_edition` SMALLINT DEFAULT NULL,
  `isbn_old` VARCHAR(40) COLLATE utf16_general_ci DEFAULT NULL,
  `isbn` VARCHAR(20) COLLATE utf16_general_ci DEFAULT NULL,
  `imported` SMALLINT NOT NULL DEFAULT 0,
  `is_borrowed` TINYINT NOT NULL DEFAULT 0,
  `is_active` TINYINT NOT NULL DEFAULT 1,
  `external_source` VARCHAR(20) COLLATE utf16_general_ci DEFAULT NULL,
  `external_id` VARCHAR(20) COLLATE utf16_general_ci DEFAULT NULL,
  PRIMARY KEY USING BTREE (`id`)
) ENGINE=InnoDB
AUTO_INCREMENT=4513 ROW_FORMAT=DYNAMIC CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci';

I want to create a FULLTEXT index on the column `title`, but I'm always getting the following error:

ERROR 1283 (HY000): Column 'title' cannot be part of FULLTEXT index

First I looked up what I could have done wrong. I found these possible problem reasons:

  1. Data type is wrong

  2. Column length is too long

  3. The storage engine doesn't support FULLTEXT indexes

  4. The word `title` is a reserved word of my MySQL

  5. I have a syntax error

Now, I can say a few things about these possible reasons. The data type is correct, I looked it up and it says I can use TEXT, CHAR and VARCHAR data types. The column length should also be ok, I am using InnoDB and for that, the max column length for a FULLTEXT index should be 767 bytes. A VARCHAR of 190 characters can't have more than 767 bytes. I am told that InnoDB supports FULLTEXT indexes, so that shouldn't be a problem. And the last two reasons just can't be, I tested it but it seems absurd to me.

I tried creating the index with my SQL Manager and also by statement, but none of this worked:

ALTER TABLE media ADD FULLTEXT INDEX MEDIA_TITLE_FULLTEXT (title);

I'm officially out of ideas, I already spent about 10 hours searching the internet for a solution

Solution

  • Abandon utf16; use utf8mb4.

    ALTER TABLE media CONVERT TO CHARACTER SET utf8mb4
                             COLLATE       utf8mb4_0900_ai_ci;

    If your client(s) are using utf16, then be sure to use utf16 in connection parameters or invoke SET NAMES utf16; immediately after connecting.

    FULLTEXT supports any length VARCHAR or TEXT/MEDIUMTEXT/etc. The 197, etc, are not relevant here.