How to ALTER COLUMN TYPE in MySQL

-- Using MODIFY
ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type;

-- Using CHANGE (allows renaming the column)
ALTER TABLE table_name
CHANGE COLUMN old_column_name new_column_name new_data_type;

-- Change an integer column to bigint
ALTER TABLE orders
MODIFY COLUMN order_id BIGINT;

-- Change a varchar column to text
ALTER TABLE customers
MODIFY COLUMN notes TEXT;

-- Change a float column to decimal with precision
ALTER TABLE products
MODIFY COLUMN price DECIMAL(10,2);

-- VARCHAR to TEXT (no data loss)
ALTER TABLE messages
MODIFY COLUMN content TEXT;

-- VARCHAR to CHAR (fixed length)
ALTER TABLE countries
MODIFY COLUMN country_code CHAR(2);

-- TEXT to VARCHAR with potential truncation
ALTER TABLE products
MODIFY COLUMN description VARCHAR(255);

-- Integer to BIGINT (safe, no data loss)
ALTER TABLE measurements
MODIFY COLUMN value BIGINT;

-- DECIMAL to INTEGER (truncation of fractional part)
ALTER TABLE products
MODIFY COLUMN price INT;

-- FLOAT to DECIMAL (fixed precision)
ALTER TABLE financial
MODIFY COLUMN amount DECIMAL(15,2);

-- DATETIME to DATE (drops time portion)
ALTER TABLE events
MODIFY COLUMN event_datetime DATE;

-- DATE to DATETIME
ALTER TABLE appointments
MODIFY COLUMN appointment_date DATETIME;

-- TIMESTAMP to DATETIME
ALTER TABLE logs
MODIFY COLUMN log_time DATETIME;

-- Converting string to date/time
ALTER TABLE imported_events
MODIFY COLUMN event_date DATE;

-- Converting VARCHAR to ENUM
ALTER TABLE tickets
MODIFY COLUMN status ENUM('open', 'in_progress', 'closed', 'cancelled');

-- Expanding an existing ENUM
ALTER TABLE products
MODIFY COLUMN size ENUM('small', 'medium', 'large', 'extra-large');

-- Converting to SET type
ALTER TABLE users
MODIFY COLUMN roles SET('admin', 'editor', 'viewer');

-- Converting TEXT to JSON
ALTER TABLE configurations
MODIFY COLUMN config JSON;

-- Converting VARCHAR to JSON
ALTER TABLE api_responses
MODIFY COLUMN response JSON;

-- Set default value during conversion
ALTER TABLE users
MODIFY COLUMN status VARCHAR(20) DEFAULT 'active';

-- Update NULL values before conversion to NOT NULL
UPDATE users SET last_login = '1970-01-01' WHERE last_login IS NULL;
ALTER TABLE users
MODIFY COLUMN last_login DATETIME NOT NULL;

-- Before: INT NOT NULL AUTO_INCREMENT
-- After: BIGINT NOT NULL AUTO_INCREMENT
ALTER TABLE customers
MODIFY COLUMN id BIGINT NOT NULL AUTO_INCREMENT;

-- Check potential data truncation before conversion
SELECT COUNT(*) FROM products WHERE LENGTH(description) > 100;

-- Perform the conversion (potential data loss)
ALTER TABLE products
MODIFY COLUMN description VARCHAR(100);

-- Specify algorithm and lock for better performance
ALTER TABLE large_table
MODIFY COLUMN data TEXT,
ALGORITHM=INPLACE, LOCK=NONE;

-- Not all modifications support INPLACE algorithm
-- Check if your modification is supported:
-- https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

-- 1. Add a new column
ALTER TABLE large_table ADD COLUMN new_column new_type;

-- 2. Update data in the new column
UPDATE large_table SET new_column = old_column;

-- 3. (Optional) Add any constraints
ALTER TABLE large_table MODIFY COLUMN new_column new_type NOT NULL;

-- 4. Drop the old column when ready
ALTER TABLE large_table DROP COLUMN old_column;

-- 5. Rename the new column to the old name
ALTER TABLE large_table CHANGE COLUMN new_column old_column new_type;

-- This happens when data doesn't fit the new type
-- Identify problematic rows
SELECT * FROM table_name
WHERE LENGTH(column_name) > new_length;

-- Fix the data first, then alter the column
UPDATE table_name
SET column_name = SUBSTRING(column_name, 1, new_length)
WHERE LENGTH(column_name) > new_length;

-- When converting string to numeric and non-numeric values exist
-- Find problematic rows
SELECT * FROM table_name
WHERE column_name REGEXP '[^0-9]';

-- Clean data before conversion
UPDATE table_name SET column_name = 0
WHERE column_name REGEXP '[^0-9]';

-- When converting to DATE/DATETIME with invalid format
-- Find invalid dates
SELECT * FROM table_name
WHERE STR_TO_DATE(column_name, '%Y-%m-%d') IS NULL;

-- Fix dates before conversion
UPDATE table_name
SET column_name = '2000-01-01'
WHERE STR_TO_DATE(column_name, '%Y-%m-%d') IS NULL;

-- Happens when the new column size would make row exceed max row size
-- Consider using vertical partitioning or TEXT/BLOB columns
ALTER TABLE large_table
MODIFY COLUMN large_column TEXT;