mysql column should support input length of 1024 character

Question

I have a column dn which used to support till varchar(290).
Now it should support input till 1024 characters.

As far I know we have a varchar limit till 767.

I tried changing the variable type to text but I am getting the below error:

mysql> ALTER TABLE `parent1` MODIFY column `dn` text;

ERROR 1170 (42000): BLOB/TEXT column 'dn' used in key specification without a key length

mysql> ALTER TABLE `parent1` MODIFY column `dn` text(1024);

ERROR 1170 (42000): BLOB/TEXT column 'dn' used in key specification without a key length

mysql> ALTER TABLE `parent1` MODIFY column `fdn` longtext(1024);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1024)' at line 1

Here are the indexes:

> SHOW keys from parent1; 
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Index_type | NULL | 
| parent1 | 0          | PRIMARY  | 1            | parent_id   | A | 0 | NULL | NULL | BTREE | | 
| parent1 | 0          | id_dn    | 1            | id          | A | 0 | NULL | NULL | BTREE | 
| parent1 | 0          | id_fdn   | 2            | dn          | A | 0 | NULL | NULL | BTREE

Answer 1

After version 5.0.3 (a long time ago) MySQL began supporting VARCHAR(65535). https://dev.mysql.com/doc/refman/5.6/en/char.html

(CHAR(255) for fixed-length character strings is indeed the limit.)

The 767 value you mention is the longest allowable index key prefix. That is, if you create an index on a column of type VARCHAR(768) or more, you must index only a prefix of the column. https://dev.mysql.com/doc/refman/5.6/en/char.html . The same is true of indexes on text (CLOB) columns.

So, when you define any index on such long columns, you need to define it as a prefix. So, if you have this column definition in your table.

  dn VARCHAR(8192) 

you'll need this index creation command or something like it.

ALTER TABLE parent1 ADD INDEX id_fdn (dn(767));

To alter your table to make that column wider, you first should drop any indexes mentioning that column, then alter the table, then recreate the indexes with the prefix.

There's no need to switch from VARCHAR() to TEXT, and indeed you should not do that because retrieval will be a little slower if you do.

Also, doing this schema change is enough of a pain in the neck that you might want to add extra characters to future proof the table.

So, something like this will do the trick for you.

ALTER TABLE parent1 DROP INDEX id_fdn;
ALTER TABLE parent1 MODIFY COLUMN dn VARCHAR(4096);
ALTER TABLE parent1 ADD INDEX id_fdn (dn(750));

What is the search consequence of a prefix index? Not much. The search processing will use as much of the index as it can, then compare the actual value if necessary.