CHANGE DATA TYPE

Updated on 2024-12-11 GMT+08:00

View PDF

This command is used to convert INT to BIGINT or increase decimal precision.

ALTER TABLE [db_name.]table_name CHANGE col_name col_name changed_column_type;

**Table 1** CHANGE DATA TYPE parameters
Parameter	Description
db_name	Database name. If this parameter is not specified, the current database is selected.
table_name	Table name.
col_name	Name of a column in a table. A column name consists of letters, digits, and underscores (_).
changed_column_type	Target data type.

The decimal precision conversion is available only when no data is lost. The decimal precision is represented in the (precision, scale) format.
Example:
- Unavailable: Decimal precision cannot be changed from (10,2) to (10,5) because only the scale increases, but the total number of digits remains unchanged.
- Available: Decimal precision can be changed from (10,2) to (12,3). The total number of digits increases by 2 but the scale increases only by 1, which does not cause any data loss.

Convert the data type of column a1 from INT to BIGINT.
ALTER TABLE test_db.carbon CHANGE a1 a1 BIGINT;
Change the precision of column a1 from 10 to 18.
ALTER TABLE test_db.carbon CHANGE a1 a1 DECIMAL(18,2);