How to Fix MySQL Error 1170: BLOB/TEXT Column Used in Key Without Length

ERROR 1170 (42000): BLOB/TEXT column 'column_name' used in key specification without a key length is raised when you attempt to create an index on a BLOB, TEXT, or JSON column without specifying a prefix length for the key. The error symbol is ER_BLOB_KEY_WITHOUT_LENGTH.

Impact

MySQL requires a prefix length when indexing variable-length large-object columns (TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT, and JSON) because these types have no fixed upper bound within the row — MySQL cannot determine how many bytes to include in the index entry without an explicit limit.

This error is a hard DDL failure: the CREATE TABLE or ALTER TABLE statement is rejected and no table or index is created. Developers most often hit it when defining schemas by hand, when running ORM-generated migrations that map a model field to TEXT or JSON and also mark it as unique or indexed, or when porting a schema from PostgreSQL (which does allow unrestricted indexes on text).

Common Causes

Declaring a TEXT or BLOB column as a PRIMARY KEY or UNIQUE without a prefix length. MySQL cannot index the entire contents of an unbounded column.
Adding a plain index to a TEXT/BLOB column in a CREATE TABLE or ALTER TABLE without specifying (n). For example, INDEX idx (body) where body is TEXT.
ORM auto-migration adding a unique constraint to a TEXT or JSON field. Django, Hibernate, ActiveRecord, and similar frameworks translate a unique=True / @Column(unique=true) annotation directly into a UNIQUE KEY definition; if the underlying column type is TEXT or JSON the migration fails with 1170.
Indexing a JSON column directly. In MySQL 5.7+ the JSON type cannot be indexed directly at all — you must index a generated column extracted from the JSON.
Copying a schema from PostgreSQL or another database engine that allows full-text indexes on unlimited-length string columns without a prefix.

Troubleshooting and Resolution Steps

Identify the column and its type. Check the failing DDL statement or inspect an existing table:

SHOW COLUMNS FROM your_table;
-- or
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_KEY
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';

Option A — Add a key prefix length. This is the preferred fix when you genuinely need the column to remain TEXT or BLOB and only need an ordinary (non-unique) index. Specify how many leading bytes to index:
```
-- When creating the table
CREATE TABLE articles (
  id   INT PRIMARY KEY,
  body TEXT,
  INDEX idx_body (body(100))
);

-- On an existing table
ALTER TABLE articles ADD INDEX idx_body (body(100));
```
Choose the prefix length based on selectivity: a length between 20 and 255 bytes is common. Prefix indexes do not enforce uniqueness across the full value.
Option B — Change the column to VARCHAR. If the data is bounded and shorter than 768 bytes (InnoDB default row format) or up to 3072 bytes (with innodb_large_prefix / Barracuda row format), switch to VARCHAR so it can be indexed in full:
```
ALTER TABLE users MODIFY email VARCHAR(255);
ALTER TABLE users ADD UNIQUE INDEX uq_email (email);
```

Option C — Use a generated column for JSON. If you need to index a value inside a JSON document, create a virtual generated column and index that:

ALTER TABLE events
  ADD COLUMN event_type VARCHAR(64)
    GENERATED ALWAYS AS (JSON_UNQUOTE(payload->>'$.type')) VIRTUAL,
  ADD INDEX idx_event_type (event_type);

Option D — Use full-text search. For TEXT columns where you need keyword search rather than equality lookups, a FULLTEXT index works without a prefix:
```
ALTER TABLE articles ADD FULLTEXT INDEX ft_body (body);
-- Query with:
SELECT * FROM articles WHERE MATCH(body) AGAINST ('search term');
```
Fix ORM mappings. If the error comes from a framework migration, update the model field:
- Django: use CharField(max_length=255, unique=True) instead of TextField(unique=True).
- SQLAlchemy / Alembic: annotate the index with mysql_length={'column_name': 100} in the Index() definition.
- Hibernate: annotate with @Column(columnDefinition = "VARCHAR(255)") or add a @Index with a length hint via a dialect override.

Additional Information

The SQLSTATE code is 42000 (syntax error or access rule violation), consistent with other schema definition errors.
This restriction applies to all InnoDB, MyISAM, and NDB storage engines. InnoDB also imposes a maximum indexed prefix of 767 bytes in the COMPACT/REDUNDANT row formats and 3072 bytes in DYNAMIC/COMPRESSED (requires innodb_large_prefix=ON in MySQL 5.6; it is on by default in MySQL 5.7+).
MySQL 8.0 introduced functional key parts (e.g., INDEX ((CAST(col AS CHAR(100))))) which can sometimes be used as an alternative to prefix indexes.
A UNIQUE constraint on a TEXT/BLOB column is fundamentally ambiguous with a prefix index because two rows could share the same prefix while having different full values — MySQL does not allow this combination.
Related error: ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes — raised when a prefix length or VARCHAR size exceeds the row-format limit.

Frequently Asked Questions

Why can't MySQL just index the entire TEXT column like PostgreSQL does? MySQL's B-tree index entries must fit within a fixed page size. Because TEXT and BLOB values are stored off-page (in overflow pages) and can be up to 4 GB, including the full value in an index entry is not architecturally supported by the InnoDB/MyISAM B-tree. A prefix extracts a bounded, on-page slice for indexing purposes.

I only need uniqueness, not search — what's the best approach? If you need a true unique constraint, the most reliable solution is to store the value in a VARCHAR(n) column sized to the realistic maximum, or to add a hash column (e.g., SHA2(email, 256) stored as CHAR(64)) and place the unique index on the hash. Prefix-based UNIQUE indexes do not guarantee global uniqueness.

Does setting a prefix length affect query performance? A prefix index can speed up range scans and equality lookups significantly compared to a full table scan, but it cannot be used as a covering index for the full column value. MySQL may need to follow up with a row lookup to confirm a full-value match, which adds a small overhead versus an exact-length index.

Can I index a JSON column in MySQL 5.7? Not directly. MySQL 5.7 introduced the JSON type but does not allow it to be indexed. The standard workaround is to create a virtual or stored generated column that extracts the relevant scalar value from the document, then index the generated column — as shown in Option C above.