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!
Found this useful! You can actually save yourself a few lines if you drop the column before creating the new one.
ReplyDeleteSomething like this
CREATE TABLE backup AS (SELECT * FROM original):
ALTER TABLE original DROP textfield, ADD textfield VARCHAR(32);
UPDATE original SET textfield = (SELECT * FROM backup WHERE backup.id = original.id)
DROP TABLE backup
This arrangement is colossally well known with in excess of 100 million duplicates of the product downloaded over the life of MySQL. Change Site URL Using MySQL
ReplyDelete