Entry

Why can't I change varchar columns in a table to be type char?

How can I make my table use char columns instead of varchar?

Jul 1st, 2002 04:02
Boris Ivanov, Nathan Wallace, jcole

I have plenty of disk space and I'd like to speed up my queries on a 
particular table, so after reading about varchar (which I originally
used a lot of when I created my table) vs. char - I decided to change
all the varchar columns (I have 4) to char - in hopes of changing my
table-type to "static (fixed length)", which the performance chapter
says will be "Very Quick".

Unfortunately, I think I'm in a bit of a catch-22.

When I do an ALTER to change one of my varchars to char, it acts like 
it 
works but no change occurs (the columns stay as type varchar).  So, I
read the manual a bit more and found this section: "7.7.1 Silent 
column 
specification changes".

It talks about how if there is already a varchar column in a table, 
all 
char columns will be changed into varchar.  On the one hand, this 
makes 
sense given that any varchar will cause the table format to be dynamic 
instead of static, and varchar saves disk space - but, it's also
trapping me because I have 4 varchar columns that I want to be char, so
I can't alter them one by one to get to a static table as it never
allows any transition to char because I have other varchar columns.

So, I need to dump, edit the dump's "create table" info to use char
instead of varchar, then reload the table -- to accomplish what I want
to do.


---------------------------------------------

how to alter table varchar to char.

Indeed you should make all changes in one turn.
Like this:

ALTER TABLE xxx MODIFY col1 char(20) NOT NULL,
MODIFY col2 char(20) NOT NULL,
MODIFY col3 char(20) NOT NULL,
MODIFY col4 char(20) NOT NULL;

Very important that you should locate all varchar columns, because 
other way mysql doesn't do it.
About optimization of MySQL - 
http://www.mysql.com/doc/S/i/Silent_column_changes.html

Boris Ivanov aka SJ314