MySql TEXT column?

Question

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 ATM i am thinking 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 1

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));

Answer 2

I think it chokes on the key field name rather than the TEXT type (which should be perfectly fine).

Reserved Words in mySQL

(And as @Pablo already said, memo fields can't be unique.)

Answer 3

Two things: key is a reserved word. you have to specify a length for a UNIQUE(key) TEXT value.