How to fix ERROR 1170: BLOB/TEXT used in key without length in MySQL

If you need to index a BLOB or TEXT column, specify a prefix length in parentheses. The length is in characters (bytes) and indicates how much of the column should be indexed.

For example:

CREATE TABLE articles (
  id INT PRIMARY KEY,
  title TEXT,
  content TEXT,
  KEY idx_title (title(255))
);

Or with ALTER TABLE:

ALTER TABLE articles ADD INDEX idx_content (content(255));

For utf8mb4 collation, MySQL supports a maximum of 3072 bytes per index key, which means up to 768 characters (3072 / 4 bytes per character).

If the column needs to be a PRIMARY KEY, convert it to VARCHAR with a reasonable length limit instead of using TEXT.

-- Instead of:
-- CREATE TABLE users (name TEXT PRIMARY KEY);

-- Use:
CREATE TABLE users (
  name VARCHAR(191) PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);

VARCHAR(191) is the safe default for utf8mb4 collation (allowing utf8mb4 to index the full column without issues).

If you need to search within BLOB or TEXT content, use FULLTEXT indexes instead of regular indexes. FULLTEXT indexes handle variable-length text properly without requiring a prefix length.

CREATE TABLE articles (
  id INT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content LONGTEXT NOT NULL,
  FULLTEXT INDEX ft_search (title, content)
);

-- Search across the full text:
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('mysql error');

If you need a UNIQUE constraint on a large TEXT field (like URLs or content hashes), create a separate hash column and make that unique.

CREATE TABLE documents (
  id INT PRIMARY KEY AUTO_INCREMENT,
  content LONGTEXT NOT NULL,
  content_hash CHAR(32) NOT NULL UNIQUE,
  INDEX idx_content_hash (content_hash)
);

-- When inserting, compute MD5:
INSERT INTO documents (content, content_hash)
VALUES ('some long content...', MD5('some long content...'));

This approach provides both a UNIQUE constraint and fast lookups without the length restriction.

If you are using an ORM (Doctrine, Eloquent, Django), verify that VARCHAR columns with lengths >= 256 are not being auto-converted to TEXT.

For example, in Eloquent (Laravel):

// Avoid this - may convert to TEXT internally:
Schema::create('posts', function (Blueprint $table) {
  $table->string('slug', 512)->unique(); // Too long!
});

// Do this instead:
Schema::create('posts', function (Blueprint $table) {
  $table->string('slug', 191)->unique(); // Stays as VARCHAR
});

Always keep VARCHAR lengths <= 191 for utf8mb4 to avoid implicit conversion.