Search code examples
mysqlsqldatabasealter-tablealter

How to change column data type without loosing data in Mysql

I have a city_id column which has varchar(255) and null values.I want to change it to big integer.

I will use:

ALTER TABLE Customer
ALTER COLUMN CITY_ID BIGINT

Do I loose or corrupt any data ?

Solution

  • You could try adding a new big integer column as an intermediate step:

    -- add new column
ALTER TABLE Customer ADD COLUMN CITY_ID_NEW BIGINT;

-- populate new column
UPDATE Customer
SET CITY_ID_NEW = CAST(CITY_ID AS UNSIGNED);

-- drop old column and rename new column
ALTER TABLE Customer DROP COLUMN CITY_ID;
ALTER TABLE Customer CHANGE COLUMN CITY_ID_NEW CITY_ID BIGINT NULL DEFAULT NULL;

    I assume above that your integer values are all positive, i.e. they are not signed. If you want signed integers, then change the cast in the update to SIGNED.