Changing the Data Type of a MySQL Table Column

Last week I needed to change the data type of a MySQL Table Column from a double to a string with array-like brackets around the previous double.

Steps to accomplish this were:

1. Create a new column in the table with the new data type.
2. Move the data over to the new column with some string manipulation.
3. Drop the old column.

In an example here, our table’s name is “mesa” with a column “numbers” with a data type of double.

Step 1, create a new column:

ALTER TABLE mesa ADD COLUMN number_arrays VARCHAR;

To string-ify the doubles, cast them as chars and concat them with the desired string modifications and put them in the new column.

Step 2, move data over and string-ify it.

UPDATE mesa
SET number_arrays = CONCAT(‘[‘ , CAST(numbers AS CHAR)+ , ‘ ]’);

Now the numbers_array column has data that looks like “[2]” or “[54].”

Step 3, remove the old column.

ALTER TABLE mesa DROP COLUMN numbers;

Now our data that was stored as doubles are stored as strings, having kept their values with some string manipulation.