aLpXS88S

1#
2# @author Huy Dinh <huy.dinh@effectivemeasure.com>
3#
4DELIMITER $$
5DROP PROCEDURE IF EXISTS procChangeCharSetandCollationForAllTables $$
6CREATE PROCEDURE procChangeCharSetandCollationForAllTables()
7	BEGIN
8		DECLARE em_table_name VARCHAR(255);
9		DECLARE em_end_of_tables INT DEFAULT 0;
10		DECLARE em_num_tables INT DEFAULT 0;
11		DECLARE cur CURSOR FOR 
12			SELECT t.table_name 
13			FROM information_schema.tables t 
14			WHERE t.table_schema = DATABASE() AND t.table_type='BASE TABLE';
15		DECLARE CONTINUE HANDLER FOR NOT FOUND SET em_end_of_tables = 1;
16		OPEN cur;
17		tables_loop: LOOP
18			FETCH cur INTO em_table_name;
19			
20			IF em_end_of_tables = 1 THEN
21				LEAVE tables_loop;
22			END IF;
23			SET em_num_tables = em_num_tables + 1;
24			# Set the default character set and collate for this table
25			# We do not use CONVERT TO CHARACTER SET to avoid data type changes of the type just described.
26			# CONVERT TO CHARACTER SET binary, the CHAR, VARCHAR, and TEXT columns are converted to their corresponding binary string types (BINARY, VARBINARY, BLOB). This means that the columns no longer will have a character set and a subsequent CONVERT TO operation will not apply to them.
27			SET @s = CONCAT('ALTER TABLE ' , em_table_name , ' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ');
28			PREPARE stmt FROM @s;
29			EXECUTE stmt;
30			
31			INNER_BLOCK: begin
32			# Now loop through the columns
33			DECLARE no_more_rows2 INT default 0;
34			DECLARE em_col_name VARCHAR(255);
35			DECLARE em_col_type VARCHAR(255);
36			DECLARE em_col_type_transformed VARCHAR(255);
37			DECLARE em_col_size INT default 255;
38			
39			DECLARE col_data CURSOR FOR
40  			SELECT c.column_name, c.character_maximum_length, c.data_type
41			FROM INFORMATION_SCHEMA.COLUMNS AS c
42			WHERE  c.table_schema = DATABASE() AND c.table_name = em_table_name
43			ORDER BY c.ordinal_position;
44			
45			DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rows2 = 1;
46			OPEN col_data;
47			col_data_loop: LOOP
48	   			IF no_more_rows2 = 1 THEN
49        			CLOSE col_data;
50        			LEAVE col_data_loop;
51    			END IF;
52
53	    		FETCH col_data INTO em_col_name, em_col_size, em_col_type;     
54     			# Convert the charset here, ignore everything except TEXT and VARCHAR
55     			IF em_col_type in ('text', 'longtext', 'mediumtext', 'tinytext') THEN
56     				IF em_col_type = 'text' THEN SET em_col_type_transformed = 'blob';
57     				ELSEIF em_col_type = 'longtext' THEN SET em_col_type_transformed = 'longblob';
58     				ELSEIF em_col_type = 'mediumtext' THEN SET em_col_type_transformed = 'mediumblob';
59     				ELSEIF em_col_type = 'tinytext' THEN SET em_col_type_transformed = 'tinyblob';
60					END IF;
61					
62					SET @s = CONCAT('ALTER TABLE ' , em_table_name , ' CHANGE ' , em_col_name , ' ' , em_col_name , ' ' , em_col_type_transformed);
63					PREPARE stmt FROM @s;
64					EXECUTE stmt;
65					     				
66					SET @s = CONCAT('ALTER TABLE ' , em_table_name , ' CHANGE ' , em_col_name , ' ' , em_col_name , ' ' , em_col_type , ' CHARACTER SET utf8 COLLATE utf8_general_ci ');
67					PREPARE stmt FROM @s;
68					EXECUTE stmt;
69					
70				ELSEIF em_col_type = 'varchar' THEN
71					SET @s = CONCAT('ALTER TABLE ' , em_table_name , ' CHANGE ' , em_col_name , ' ' , em_col_name , ' varbinary(' , em_col_size, ')');
72					PREPARE stmt FROM @s;
73					EXECUTE stmt;
74									
75					SET @s = CONCAT('ALTER TABLE ' , em_table_name , ' CHANGE ' , em_col_name , ' ' , em_col_name , ' varchar(' , em_col_size , ') CHARACTER SET utf8 COLLATE utf8_general_ci ');
76					PREPARE stmt FROM @s;
77					EXECUTE stmt;				
78				END IF;
79				
80			END LOOP col_data_loop;					
81			
82			end INNER_BLOCK;
83			
84		END LOOP;
85		CLOSE cur;
86	END $$
87DELIMITER ;
88CALL procChangeCharSetandCollationForAllTables();