Force Truncation on VARCHAR modification?

问题:

I would like to cap VARCHAR to 255 on a few columns so that I can add an index.

alter table striker modify contacts varchar(255)

When I try to run the above command I get

Error Code: 1265. Data truncated for column 'contacts' at row 331   38.969 sec

Is there a way to force truncation of the column so that this command successfully shortens the VARCHAR? I don't care about shortening the data in the column.

回答1:

You can manually truncate the column at 255 characters:

UPDATE striker
SET    contacts = SUBSTR (contacts, 1, 255);

Now, that you know that no value is longer than 255 characters, you perform the alter table statement you have in the OP safely.

回答2:

Why bother truncating the column? Just create the index with a length specifier for the column. Here is an example:

create index idx_striker_contacts on striker(contacts(255))

You can do this for multiple columns.