MySQL Datatype

[Anon]

I need to change one of my records datatypes, is there a way that I can do that without deleting the DB and starting over?

[[deleted]]

RT(F)M about the ALTER TABLE statement.

A note: this is very very dangerous, it can easily whipe your database, don't do it.

Make it a habit to do what the big-boys do, make a backup, drop the database/table, re-create the database with the new datatype, and load the data back into the table.

!!! Always make a backup of the complete database before you change anything !!!

[Anon]

Vincent's quite right. As an addendum though, the drop, change & re-create is fine providing:

1. You can store the datafile local to the database machine.
2. The table is of a relatively small size and not heavily indexed. (Less than 50 - 100Meg in size)

These basically allow for size of data transfer across any WAN style connection and the time required to recreate all the necessary indicies.

If however you are working over a WAN / you have a heavily indexed table, the alter table command to either directly change the column, or to add a dummy column, populate, drop original and rename is also a relatively effective technique.

In all cases though, as the man says, do a full backup before making any changes of this nature.