A script that performs the conversion of `latin1` encoded strings to `utf8mb4` for all columns in all tables of a specific mariadb database. Make sure to back up your data before running scripts that modify it.

convert_latin1_to_utf8mb4.sql

DELIMITER //

-- Drop the procedure if it already exists
DROP PROCEDURE IF EXISTS convert_chars_to_utf8mb4 //

-- Create the procedure to convert character set of columns
CREATE PROCEDURE convert_chars_to_utf8mb4(IN db_name VARCHAR(255))
BEGIN
    -- Declare variables for cursor and loop control
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(255);
    DECLARE col_name VARCHAR(255);
    DECLARE col_type LONGTEXT; -- To store the full column type definition
    DECLARE alter_sql TEXT;

    -- Declare a cursor to select character-based columns currently using latin1
    -- We fetch table name, column name, and the full column type definition
    DECLARE cur CURSOR FOR
        SELECT
            table_name,
            column_name,
            column_type
        FROM information_schema.columns
        WHERE
            table_schema = db_name
            -- Filter for character data types that support character sets
            AND data_type IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext', 'enum', 'set')
            -- Filter for columns currently using the latin1 character set
            AND character_set_name = 'latin1';

    -- Declare a handler for the end of the cursor result set
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- Open the cursor
    OPEN cur;

    -- Start loop to fetch and process each column
    read_loop: LOOP
        -- Fetch the next row from the cursor
        FETCH cur INTO tbl_name, col_name, col_type;

        -- Exit the loop if no more rows are found
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- Construct the ALTER TABLE statement to modify the column
        -- We use the fetched column_type to preserve original details (like size, enum values)
        -- and add the new CHARACTER SET and COLLATE clauses
        SET alter_sql = CONCAT(
            'ALTER TABLE `', tbl_name, '` MODIFY COLUMN `', col_name, '` ', col_type,
            ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;'
        );

        -- Execute the dynamically generated ALTER TABLE statement
        PREPARE stmt FROM alter_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;

    -- Close the cursor
    CLOSE cur;
END //

DELIMITER ;

-- To use the procedure, set your database name and call it:
-- SET @my_db = 'your_database_name';
-- CALL convert_chars_to_utf8mb4(@my_db);

-- Optionally, drop the procedure after execution if it's a one-time task:
-- DROP PROCEDURE IF EXISTS convert_chars_to_utf8mb4;