Changing a table collumn attributes.

Aybabtu

I have mad a database and I have had the need to put more info into a collumn then I had expected. How do I update the collumn to hold more data?

Sxooter

Many different databases have "back door" methods for changing the length of varchars and such, but the SQL way to do it is to drop/recreate the table.

I.e. something like:

create newtable (newfield varchar(88), morefields go here);
insert into newtable (select * from oldtable);
drop table oldtable ;
alter table newtable rename to oldtable;

Aybabtu

Do you knoe the "back door" method for MySQL. It is a production database with data already in it and servral PHP page refrencing that table name.

Sxooter

Sorry, I don't even know if there IS one for MySQL, though I suspect there is.

You might wanna google search whatever mailing lists mysql has to see if this subject has come up there, as it's a pretty common question on the pgsql mailing lists, and there IS a backdoor for that database to change varchar() length.

FoTo50

why looking for a backdoor when the front entry is so close? 😉

alter table tablename change column fieldname fieldname varchar(length) null (or NOT NULL)

This works even with production DB's. I changed like this a table with more than 40.000 rows within a second

Sxooter

sweet! I didn't know MySQL did that.

What does it do with the data if you make it smaller, say from a 50 to a 30? Does the data still exist in the table but is invisible? Or is it chopped off?

FoTo50

Interesting question ... let me try this out ... 😃

... chopped off it is :rolleyes:

Sxooter

I tested and answered my own question, it does chop them off in store, so if you make it smaller then bigger the extra chars are gone.

Aybabtu

Thank you for all of your help, I fixed the issue.