Converting CHAR to BIT I have a column with char(16) data type and it was mistakenly set to char(16) actually it should be bit(1) because it contains only three distinct values as follows:


16 characters long string
10 characters long string
blank value


Now, I want to convert blank and 10 characters long string to 0 and 16 characters string to 1. But table is quite large so I think I should avoid any experiment and pursue the best approach.

First, I thought to create and index then update the column and then change data type.
Can you be more specific on "quite large"?  In general you're right, you shouldn't just 'do it live' in production with any change.  Do you have any kind of QA or test environment you can do a run on first?

This isn't the most sexy ninja one line approach but you could


Add a new bit column
Issue updates setting the new column = if( length(old_column)=16,1,0)
Alter table to drop old column and rename the new column