11
Alter table merry_parents change mobile mobile char(10).

When I do the above I'm getting error:

#1265 - Data truncated for column 'mobile' at row 2

How can I truncate my mobile field to char(10)? Currently it is char(12).

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
vaanipala
  • 1,261
  • 7
  • 36
  • 63

4 Answers4

34

The error is telling you that there is data 12 characters long in row 2 (and probably others) so it's stopped the alter command to avoid losing data.

Try updating your table using SUBSTRING() to shorten the column. It's unclear why you want to do this as you'll lose data, but this will truncate the data to 10 characters long:

UPDATE merry_parents SET mobile=SUBSTRING(mobile, 1, 10)

Then run your alter command:

ALTER TABLE merry_parents CHANGE mobile mobile char(10).
Bojangles
  • 99,427
  • 50
  • 170
  • 208
  • thank you. That worked! :) I'm working with test data so it is alright if i'm losing data. :) – vaanipala Mar 08 '12 at 01:28
  • See My table Having a field with gender char(1) , I want to make it gender enum('m','f','o') what should i do ? Its showing truncating error while alter directly from char to enum – dinesh kandpal Sep 15 '17 at 15:54
4

If you are willing to just have the data truncated, you can do it in one step by using the IGNORE option on the ALTER:

ALTER IGNORE TABLE merry_parents MODIFY mobile char(10);
Dwight
  • 131
  • 1
  • 3
  • 2
    If you're not using an ancient version MySQL this won't work. IGNORE was deprecated in 5.6 and removed in 5.7. see: https://dev.mysql.com/worklog/task/?id=7395 – philarmour Jul 08 '20 at 21:30
4

If you are ok with truncating the data at 10 characters - you can update the column first, then resize it

UPDATE <tablename> set mobile = left(mobile, 10);

Then run your alter statement.

Jody
  • 8,021
  • 4
  • 26
  • 29
  • See My table Having a field with gender char(1) , I want to make it gender enum('m','f','o') what should i do ? Its showing truncating error while alter directly from char to enum – dinesh kandpal Sep 15 '17 at 15:56
0

You have data that has more characters than the length of the column that you are trying to alter it into.

Or you have a null value in the specified field.

http://bugs.mysql.com/bug.php?id=14742

Oh Chin Boon
  • 23,028
  • 51
  • 143
  • 215