SQL

latin1-to-utf8.py

import mysql.connector

def update_column_encoding(host, user, password, database):
    try:
        conn = mysql.connector.connect(host=host, user=user, password=password, database=database, buffered=True)
        cursor = conn.cursor()

        query = """
        SELECT TABLE_NAME, COLUMN_NAME
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = %s AND DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext')
        """
        cursor.execute(query, (database,))

        results = cursor.fetchall()
        if results:
            for table_name, column_name in results:
                print(f"Updating {table_name}.{column_name}")
                update_query = f"""
                UPDATE `{table_name}`
                SET `{column_name}` = CONVERT(CAST(CONVERT(`{column_name}` USING latin1) AS BINARY) USING utf8mb4)
                """
                try:
                    cursor.execute(update_query)
                    conn.commit()
                except mysql.connector.Error as e:
                    print(f"Error updating {table_name}.{column_name}: {e}")

        print("Update complete.")

    except mysql.connector.Error as e:
        print(f"Error: {e}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()

# Configure your database connection
host = 'localhost'
user = 'root'
password = ''
database = 'name_of_your_database' # <- Fill with your database name

update_column_encoding(host, user, password, database)

latin1-to-utf8.sql

DELIMITER //

CREATE PROCEDURE UpdateColumnEncoding(IN db_name VARCHAR(255))
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableName VARCHAR(255);
    DECLARE columnName VARCHAR(255);
    DECLARE cur CURSOR FOR 
        SELECT TABLE_NAME, COLUMN_NAME
        FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = db_name 
          AND DATA_TYPE IN ('char', 'varchar', 'text', 'tinytext', 'mediumtext', 'longtext');
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO tableName, columnName;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @s = CONCAT('UPDATE `', tableName, '` SET `', columnName, '` = CONVERT(CAST(CONVERT(`', columnName, '` USING latin1) AS BINARY) USING utf8mb4)');
        PREPARE stmt FROM @s;
        BEGIN
            DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
            BEGIN
                -- Handle the error or log it
            END;
            EXECUTE stmt;
        END;
        DEALLOCATE PREPARE stmt;
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;
CALL UpdateColumnEncoding('your_db_name');