acidzombie24 acidzombie24 - 5 months ago 19
SQL Question

Indexing a MySql TEXT column?

I ran this using MySql and it appears to not like

TEXT
. With SQL server I use
nvarchar(max)
What should I use in MySql? In other tables some fields will be descriptions and may be long so at the moment I am thinking that fixed length is bad.

create table if not exists
misc_info (
id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
key TEXT UNIQUE NOT NULL,
value TEXT NOT NULL
)ENGINE=INNODB;

Answer

You can't have a UNIQUE index on a text column in MySQL.

If you want to index on a TEXT or a BLOB field, you must specify a fixed length to do that.

From MySQL documentation:

BLOB and TEXT columns also can be indexed, but a prefix length must be given.

Example:

CREATE UNIQUE INDEX index_name ON misc_info (key(10));