BLOB column used in key specification without key length
Production Risk
Low — DDL fails; no data loss.
ER_BLOB_USED_AS_KEY (1073, SQLSTATE HY000) is raised when a BLOB or TEXT column is used in an index without specifying a key prefix length. MySQL cannot index the full content of variable-length large object columns.
- 1INDEX or KEY defined on a BLOB/TEXT column without a prefix length
- 2Attempting to make a BLOB column part of a PRIMARY KEY
CREATE TABLE t (content BLOB, INDEX idx_content (content));
expected output
ERROR 1073 (HY000): BLOB column 'content' used in key specification without a key length
Fix
Specify a key prefix length for BLOB/TEXT indexes
WHEN An index on a BLOB/TEXT column is needed.
CREATE TABLE t (content BLOB, INDEX idx_content (content(255)));
Why this works
The prefix length tells MySQL how many bytes of the column to include in the index, making the index size bounded.
✕ Use full-text BLOB indexes for equality searches
Prefix indexes on BLOBs only work for prefix matches; use FULLTEXT indexes for content search.
MySQL 8.0 — 1073 ER_BLOB_USED_AS_KEY
Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev