Force Truncation on VARCHAR modification?

Question

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.

score 6 · Accepted Answer · answered Jan 07 '15 at 21:54

6

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.

answered Jan 07 '15 at 21:54

Mureinik

277,661
50
283
320

Just to enforce that a string here indeed begins at position 1, and not at zero (0) as in other languages such as substr in PHP (often coupled with MySQL) - which in this case would shorten the string to 254 – K. Kilian Lindberg May 20 '22 at 20:38

score 0 · Answer 2 · answered Jan 07 '15 at 22:12

0

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.

answered Jan 07 '15 at 22:12

Gordon Linoff

1,198,228
53
572
709

I wanted to add an index to improve query performance. Will a partial index do that? – Chris Hall Jan 07 '15 at 22:27
@ChrisHall . . . MySQL should use the index under most circumstances (for `=`, ` – Gordon Linoff Jan 07 '15 at 22:35

K. Kilian Lindberg · Answer 3 · 2022-05-20T23:15:58.857

Late to the party, although I found my way here in 2022 with this question and didn't find a satisfying solution. Assuming you are prototyping locally, which the question implies, since these queries may have profound effects on your data, as always - use with caution. Intentionally simplified and straight forward: this should work, assuming you're logged in with root privileges - which seldom should be the case in anything near production, right. This guy sets strict mode etc aside temporarily:

SET @tempsql_mode= @@global.sql_mode;
SET @@global.sql_mode= '';
alter table striker modify contacts varchar(255);
SET @@global.sql_mode= @tempsql_mode;

Force Truncation on VARCHAR modification?

3 Answers3

Linked