7a0WXKyQ

1DELIMITER ;;
2DROP PROCEDURE IF EXISTS character_fix;;
3CREATE DEFINER=`root`@`localhost` PROCEDURE `character_fix`(_table_name VARCHAR(50), _field_name VARCHAR(50))
4BEGIN
5
6	DROP TEMPORARY TABLE IF EXISTS temp_cursor_table;
7	SET @temp_table_query='CREATE TEMPORARY TABLE temp_cursor_table AS ';
8	SET @temp_table_query=concat( @temp_table_query, 'SELECT id, ', _field_name, ' FROM ', _table_name, ' WHERE ', _field_name, ' <> convert(', _field_name, ' using ascii) ORDER BY id');
9	PREPARE temp_statement from @temp_table_query;
10	EXECUTE temp_statement;
11	DEALLOCATE PREPARE temp_statement;
12
13	BEGIN
14
15		DECLARE _done TINYINT DEFAULT FALSE;
16		DECLARE _convert_error TINYINT DEFAULT FALSE;
17		DECLARE _cursor_id INT(11);
18		DECLARE _cursor_field VARCHAR(50);
19		DECLARE _str_converted TEXT CHARSET utf8;
20		
21		DECLARE _field_length INT DEFAULT 0;
22		DECLARE _field_position INT DEFAULT 0;
23		DECLARE _next_two_characters VARCHAR(2) DEFAULT NULL;
24		DECLARE _next_character VARCHAR(1) DEFAULT NULL;
25		
26		DECLARE _cursor_values CURSOR FOR SELECT * FROM temp_cursor_table;
27		DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
28		DECLARE CONTINUE HANDLER FOR 1300 SET _convert_error = TRUE;
29
30		OPEN _cursor_values;
31	
32		_read_loop: LOOP
33			FETCH _cursor_values INTO _cursor_id, _cursor_field;
34			IF _done THEN
35				LEAVE _read_loop;
36			END IF;
37
38			SET _field_length = LENGTH(_cursor_field);
39			SET _field_position = 1;
40			SET _str_converted = '';
41
42			_iterator:
43			LOOP
44				IF _field_length = 0 OR _field_position >= _field_length OR _cursor_field IS NULL THEN
45					LEAVE _iterator;
46				END IF;
47
48				SET _next_two_characters = SUBSTRING(_cursor_field FROM _field_position FOR 2);
49				SET _next_character = SUBSTRING(_cursor_field FROM _field_position FOR 1);
50
51				SET _next_two_characters = CONVERT(BINARY CONVERT(_next_two_characters USING latin1) USING utf8);
52				IF NOT _convert_error THEN
53					IF LENGTH(_next_two_characters) = 2 THEN
54						SET _str_converted = CONCAT(_str_converted, _next_two_characters);
55						SET _field_position = _field_position + 2;
56					ELSE
57						SET _str_converted = CONCAT(_str_converted, _next_two_characters);
58						SET _field_position = _field_position + 1;
59					END IF;					
60				ELSE
61					SET _str_converted = CONCAT(_str_converted, _next_character);
62					SET _field_position = _field_position + 1;
63				END IF;
64				
65				SET _convert_error = FALSE;				
66
67			END LOOP;
68
69			SET @update_sql = CONCAT("UPDATE ", _table_name, " SET ", _field_name, " = ", QUOTE(_str_converted), " where id = ", _cursor_id, ";");		
70			PREPARE _update_statement FROM @update_sql;
71			EXECUTE _update_statement;
72			DEALLOCATE PREPARE _update_statement;
73
74		END LOOP;
75
76		CLOSE _cursor_values;
77	END;
78
79	DROP TEMPORARY TABLE IF EXISTS temp_cursor_table;
80
81END;;
82DELIMITER ;