Convert database from Windows1251 to UTF8

Hello. Faced the issue of converting the database from CP1251 to UTF8.
Climbing across the expanses of the Internet did not find the finished Scrit (apparently looking bad).
So I decided to write my own script. Conversion occurs without any dumps of the database. Problems with symbols e = e, etc. are solved.

continued under the cut

Use on health.
Correct the opening tag at the beginning of the script. Habr cuts it, I had to shield a question;)
')
UPD:
At the request of workers I describe the algorithm of work:
1. Rename the table to temporary.
2. We take the description of the table. In it, we change the defaul charset to utf8. We put collate utf8_bin (it is done so that the letters are normally recoded. And there were no problems with e = e, etc. But there is 1 trabla - when comparing string values, the register will be taken into account).
3. Turn off the use of keys (increases the speed of insertion). ALTER TABLE table_in_utf8 DISABLE KEYS;
4. Insert the data from the table that we have in cp1251 in a table with utf8 encoding. INSERT INTO table_in_utf8 SELECT * FROM table_in_cp1251;
5. Enable the use of keys ALTER TABLE table_in_utf8 ENABLE KEYS;

Well, the script is written not to do all this with pens, if your project has a lot of tables.

You can run the script from the console `php -f export.php` or via the web.

<\? php

$ time = microtime (true);

$ db = 'vspomni';
$ login = 'vspomni';
$ passw = ";
$ host = 'vspomni.ru';

$ res = mysql_connect ($ host, $ login, $ passw);
mysql_select_db ($ db);

mysql_query ('SET NAMES utf8;');

$ rs = mysql_query ('SHOW TABLES;');
print mysql_error (); // the notorious 'command out of synch' message :(
while (($ row = mysql_fetch_assoc ($ rs))! == false) {

$ time1 = microtime (true);
// print $ row ['Tables_in_vspomni2']. "\ n";
$ table_name = $ row ['Tables_in _'. $ db];
$ query = 'SHOW CREATE TABLE'. $ table_name;

$ row_create = mysql_query ($ query);
print mysql_error ();
$ row1 = mysql_fetch_assoc ($ row_create);

if (strpos ($ row1 ['Create Table'], 'DEFAULT CHARSET = utf8')! == false)
{
print 'Table'. $ table_name. ' - skipped '. "\ N";
continue;
}

$ create_table_scheme = str_ireplace ('cp1251', 'utf8', $ row1 ['Create Table']); // CREATE TABLE SCHEME
$ create_table_scheme = str_ireplace ('ENGINE = InnoDB', 'MyISAM', $ create_table_scheme);
$ create_table_scheme. = 'COLLATE utf8_bin';

// print $ create_table_scheme;
// continue;

$ query = 'RENAME TABLE'. $ table_name. ' TO '. $ Table_name .'_ tmp_export'; // RENAME TABLE;
mysql_query ($ query);
$ error = mysql_error ();
if (strlen ($ error)> 0)
{
print $ error. ' - LINE '.__ LINE __. "\ N";
break;
}

$ query = $ create_table_scheme;
mysql_query ($ query);
$ error = mysql_error ();
if (strlen ($ error)> 0)
{
print $ error. ' - LINE '.__ LINE __. "\ N";
break;
}

$ query = 'ALTER TABLE'. $ table_name. ' DISABLE KEYS ';
mysql_query ($ query);
$ error = mysql_error ();
if (strlen ($ error)> 0)
{
print $ error. ' - LINE '.__ LINE __. "\ N";
break;
}

$ query = 'INSERT INTO'. $ table_name. ' SELECT * FROM '. $ Table_name .'_ tmp_export';
mysql_query ($ query);
$ error = mysql_error ();
if (strlen ($ error)> 0)
{
print $ error. ' - LINE '.__ LINE __. "\ N";
break;
}

$ query = 'DROP TABLE'. $ table_name .'_ tmp_export ';
mysql_query ($ query);
$ error = mysql_error ();
if (strlen ($ error)> 0)
{
print $ error. ' - LINE '.__ LINE __. "\ N";
break;
}

$ time3 = microtime (true);
$ query = 'ALTER TABLE'. $ table_name. ' ENABLE KEYS ';
mysql_query ($ query);
$ error = mysql_error ();
if (strlen ($ error)> 0)
{
print $ error. ' - LINE '.__ LINE __. "\ N";
break;
}

print 'Enable keys to'. $ table_name. '. time - '. (microtime (true) - $ time3). "\ n";
print 'converted'. $ table_name. '. time - '. (microtime (true) - $ time1). "\ n \ n";

}
mysql_free_result ($ rs);

print 'done. total time - '. (microtime (true) - $ time);
?>

PS Tonight, the DB of the project remember. I converted this script in 17 minutes.

The original is here http://suhanovo.livejournal.com/4560.html