If there is no more record, then we have to skip Start a loop to fetch each rows from the cursor This handler will set the value v_finished to 1 where the character set is not the oneĭECLARE alter_cursor CURSOR FOR SELECT DISTINCT TABLE_NAMEįROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()ĪND COLLATION_NAME NOT LIKE CONCAT(character_set, '_%') This will create a cursor that selects each table, or 'latin1' or any other valid character setĬREATE PROCEDURE changeCollation(IN character_set VARCHAR(255))ĭECLARE v_table_name varchar(255) DEFAULT "" ĭECLARE v_message varchar(4000) DEFAULT "No records" character_set parameter could be 'utf8' Here is a solution how to do it with a stored procedure: DELIMITER $$ĭROP PROCEDURE IF EXISTS changeCollation$$ I read it here, that you need to convert each table manually, it is not true. WHERE INFORMATION_SCHEMA =ĪND INFORMATION_NAME =ĪND DATA_TYPE IN ( 'char', 'varchar' ) /* include other types if necessary */ ), ' COLLATE utf8mb4_unicode_ci ') AS 'USE `DATABASE_NAME` 'ĪND (SELECT INFORMATION_TYPE OR DATA_TYPE LIKE 'longtext', '', CONCAT('(', CHARACTER_MAXIMUM_LENGTH, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ')ĮACH COLUMN SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME,'` ',ĭATA_TYPE, IF(CHARACTER_MAXIMUM_LENGTH IS NULL To use, just export results to delimited text (probably new line '\n')ĮACH TABLE SELECT CONCAT('ALTER TABLE `', TABLE_NAME, I have used this to some of my projects before and was able to solved most of my COLLATION problems. Generates query to update each table and column of each table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |