Changing the data type or size of a column

Last modified: March 28, 2025

When you change the data type or size of the column, the data can be truncated during the deployment.

Solution: To avoid deployment errors and ensure data integrity, you need to create a migration script:

  • Generate a migration script to update values
  • Modify the column data type

Note

The comparison engine of the tools doesn’t handle all cases automatically, which is why the cases mentioned above must be resolved manually.

Scripts

The table provides source and target table script examples to illustrate the case:

Source table script example Target table script example 
CREATE TABLE Product (
  ProductNumber INT PRIMARY KEY,
  ProductPrice DECIMAL(5,2) NULL,
  ProductName VARCHAR(25) NULL
);

INSERT INTO Product (ProductNumber, ProductPrice, ProductName)
  VALUES (1125, 234.56, 'screwdriver');
INSERT INTO Product (ProductNumber, ProductPrice, ProductName)
  VALUES (1126, 89.71, 'hummer');

CREATE TABLE Product (
  ProductNumber INT PRIMARY KEY,
  ProductPrice VARCHAR(10) NULL,
  ProductName VARCHAR(25) NULL
);
INSERT INTO Product (ProductNumber, ProductPrice, ProductName)
  VALUES (1125, '234,56', 'screwdriver');
INSERT INTO Product (ProductNumber, ProductPrice, ProductName)
  VALUES (1126, '89,71', 'hummer');

The table provides examples of scripts generated by the Schema Comparison engine and those that should be generated manually for errorless data migration to illustrate the case:

Script generated by the Schema Comparison engine Script that should be generated manually for errorless data migration 
ALTER TABLE product
  CHANGE COLUMN ProductPrice ProductPrice DECIMAL(5, 2) DEFAULT NULL;

-- migration script:

UPDATE product
SET Product.ProductPrice = REPLACE(ProductPrice,',','.');

-- migration script.

ALTER TABLE product
  CHANGE COLUMN ProductPrice ProductPrice DECIMAL(5, 2) DEFAULT NULL;

Want to find out more?

Overview

Overview

Take a quick tour to learn all about the key benefits delivered by Schema Compare for MySQL.

Request a demo

Request a demo

If you consider employing this tool for your business, request a demo to see it in action.

Schema Compare for MySQL logo

Ready to start using  

Schema Compare for MySQL?

Download a FREE 30-day trial