For simplicity, let's assume you have a table that looks something like this:
# Our original table
CREATE TABLE my_table (
id INT NOT NULL AUTO_INCREMENT,
my_col TEXT NOT NULL,
PRIMARY KEY (id)
);
What you want to do is convert the my_col
column into a VARCHAR column. The following steps should get you from TEXT to VARCHAR:
# Create a temp table
CREATE TABLE tmpvchar (
id INTEGER,
my_col_vchar VARCHAR(255)
);
# Copy the data over and cast it to a character
INSERT INTO tmpvchar (id, my_col_vchar)
SELECT id, CAST(my_col AS CHAR(255)) FROM my_table;
# Add a new column to the original table
ALTER TABLE my_table ADD my_col_vchar VARCHAR(255);
# Copy the data from the temp table
UPDATE my_table SET my_col_vchar = (SELECT my_col_vchar FROM tmpvchar WHERE my_table.id = tmpvchar.id);
# Remove the incorrect column
ALTER TABLE my_table DROP my_col;
# Rename the column back to the correct one
ALTER TABLE my_table CHANGE my_col_vchar my_col VARCHAR (255) NOT NULL;
# Drop the temporary table
DROP TABLE tmpvchar;
I hope this ends up saving someone some time!