Converting a TEXT column to a VARCHAR column in MySQL

I ran in to the issue of having to convert a column in a table from a TEXT type to a VARCHAR type. Before you do this, make sure that the length of your VARCHAR is long enough to hold the longest string that you have... otherwise you're going to end up truncating your strings! In our case, the strings were all pretty short, so this wasn't an issue. Due to an existing (and known) MySQL bug, we can't cast directly from a TEXT blob to a VARCHAR, but have to cast it to a CHAR first. As this ended up being several steps, I thought I'd share the steps I took. I am sure there are ways to do this with fewer steps, but this method allows you to perform the steps one by one, checking to make sure your data is correct as you go.

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!