Last active
September 10, 2024 05:34
-
-
Save bouroo/4f994915f347f78002f43d3981740a82 to your computer and use it in GitHub Desktop.
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| -- Set the database name | |
| SET @db_name = :database_name; | |
| -- Prepare a cursor to iterate through each relevant column | |
| DROP PROCEDURE IF EXISTS convert_latin1_to_utf8mb4; | |
| DELIMITER // | |
| CREATE PROCEDURE convert_latin1_to_utf8mb4() | |
| BEGIN | |
| DECLARE done INT DEFAULT FALSE; | |
| DECLARE tbl_name VARCHAR(255); | |
| DECLARE col_name VARCHAR(255); | |
| DECLARE update_sql TEXT; | |
| -- Declare a cursor for fetching the table and column names | |
| DECLARE cur CURSOR FOR | |
| SELECT table_name, column_name | |
| FROM information_schema.columns | |
| WHERE table_schema = @db_name; | |
| -- Declare a handler for when the cursor is done | |
| DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; | |
| -- Open the cursor | |
| OPEN cur; | |
| read_loop: LOOP | |
| -- Fetch the next row | |
| FETCH cur INTO tbl_name, col_name; | |
| IF done THEN | |
| LEAVE read_loop; | |
| END IF; | |
| -- Prepare the SQL statement for updating the column | |
| SET update_sql = CONCAT('UPDATE `', tbl_name, '` SET `', col_name, '` = CONVERT(CAST(CONVERT(`', col_name, '` USING latin1) AS BINARY) USING utf8mb4);'); | |
| -- Execute the prepared SQL statement | |
| PREPARE stmt FROM update_sql; | |
| EXECUTE stmt; | |
| DEALLOCATE PREPARE stmt; | |
| END LOOP; | |
| -- Close the cursor | |
| CLOSE cur; | |
| END; | |
| // | |
| DELIMITER ; | |
| -- Call the procedure to perform the conversion | |
| CALL convert_latin1_to_utf8mb4(); | |
| -- Optionally, drop the procedure after use | |
| DROP PROCEDURE IF EXISTS convert_latin1_to_utf8mb4; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment