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:
- Create a new column in the table with the new data type.
- Move the data over to the new column with some string manipulation.
- 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.
SET number_arrays = CONCAT(‘[‘ , CAST(numbers AS CHAR)+ , ‘ ]’);
Now the numbers_array column has data that looks like “” or “.”
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.