Condtionally execute adjustment statements on MySQL
IF
statement won't work directly in standard SQL scripts outside of a stored procedure or function in MySQL. Thus, we will have to use a different approach using CREATE PROCEDURE
to encapsulate the logic. Here’s an example that you can reference to create a stored procedure to safely make the changes:
DELIMITER //
CREATE PROCEDURE AdjustFilesTable()
BEGIN
-- Change FileExt from char(4) to char(5) if it exists
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Files' AND COLUMN_NAME = 'FileExt') > 0 THEN
SET @sql = 'ALTER TABLE Files CHANGE COLUMN FileExt FileExt CHAR(5) NOT NULL';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
-- Rename CreateAt to CreatedAt if it exists
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Files' AND COLUMN_NAME = 'CreateAt') > 0 THEN
SET @sql = 'ALTER TABLE Files CHANGE COLUMN CreateAt CreatedAt BIGINT DEFAULT NULL';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
-- Rename UpdateAt to UpdatedAt if it exists
IF (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Files' AND COLUMN_NAME = 'UpdateAt') > 0 THEN
SET @sql = 'ALTER TABLE Files CHANGE COLUMN UpdateAt UpdatedAt BIGINT DEFAULT NULL';
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;
END //
DELIMITER ;
-- Call the procedure to adjust the table
CALL AdjustFilesTable();
-- Optionally, drop the procedure afterwards
DROP PROCEDURE IF EXISTS AdjustFilesTable;
Explanation:
- Stored Procedure Creation: We create a stored procedure named
AdjustFilesTable
that contains the logic to adjust the table. - Conditional Logic: Inside the procedure, we use
IF
statements to check for the existence of each column before attempting to alter it. - Dynamic SQL Execution: We prepare, execute, and deallocate the statements as needed.
- Calling the Procedure: After creating the procedure, we call it to perform the adjustments.
- Cleanup: Optionally, you can drop the procedure afterward to prevent clutter.
Note:
- Make sure you have the necessary permissions to create procedures in your MySQL environment.