Created
June 24, 2013 09:21
-
-
Save boonkerz/5848823 to your computer and use it in GitHub Desktop.
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
<?php | |
/** | |
* mysql-convert-latin1-to-utf8.php | |
* | |
* Converts incorrect MySQL latin1 columns to UTF8. | |
* | |
* NOTE: Look for 'TODO's for things you may need to configure. | |
* | |
* Documentation at: | |
* http://nicj.net/2011/04/17/mysql-converting-an-incorrect-latin1-column-to-utf8 | |
* | |
* Or, read README.md. | |
* | |
* @author Nic Jansma <nic@nicj.net> | |
* @copyright 2012 Nic Jansma | |
* @link http://www.nicj.net | |
*/ | |
// TODO: Pretend-mode -- if set to true, no SQL queries will be executed. Instead, they will only be echo'd | |
// to the console. | |
$pretend = false; | |
// TODO: The collation you want to convert all columns to | |
$newCollation = 'utf8_unicode_ci'; | |
// TODO: Database information | |
$dbHost = 'localhost'; | |
$dbName = 'name'; | |
$dbUser = 'root'; | |
$dbPass = 'pass'; | |
// Open a connection to the information_schema database | |
$infoDB = new mysqli($dbHost, $dbUser, $dbPass, 'information_schema'); | |
// Open a second connection to the target (to be converted) database | |
$targetDB = new mysqli($dbHost, $dbUser, $dbPass, $dbName); | |
// | |
// TODO: FULLTEXT Indexes | |
// | |
// You may need to drop FULLTEXT indexes before the conversion -- execute the drop here. | |
// eg. | |
// sqlExec($targetDB, "ALTER TABLE MyTable DROP INDEX `my_index_name`", $pretend); | |
// | |
// If so, you should restore the FULLTEXT index after the conversion -- search for 'TODO' | |
// later in this script. | |
// | |
// Get all tables in the specified database | |
$tables = sqlObjs($infoDB, | |
"SELECT TABLE_NAME, TABLE_COLLATION | |
FROM TABLES | |
WHERE TABLE_SCHEMA = '$dbName'"); | |
foreach ($tables as $table) { | |
$tableName = $table->TABLE_NAME; | |
$tableCollation = $table->TABLE_COLLATION; | |
// Find all columns that aren't of the destination collation | |
$cols = sqlObjs($infoDB, | |
"SELECT * | |
FROM COLUMNS | |
WHERE TABLE_SCHEMA = '$dbName' | |
AND TABLE_Name = '$tableName' | |
AND COLLATION_NAME != '$newCollation' | |
AND COLLATION_NAME IS NOT NULL"); | |
foreach ($cols as $col) { | |
// Save current column settings | |
$colName = $col->COLUMN_NAME; | |
$colCollation = $col->COLLATION_NAME; | |
$colType = $col->COLUMN_TYPE; | |
$colDataType = $col->DATA_TYPE; | |
$colLength = $col->CHARACTER_OCTET_LENGTH; | |
$colNull = ($col->IS_NULLABLE === 'NO') ? 'NOT NULL' : ''; | |
$colDefault = ''; | |
if ($col->COLUMN_DEFAULT !== null) { | |
$colDefault = "DEFAULT '{$col->COLUMN_DEFAULT}'"; | |
} | |
// Determine the target temporary BINARY type | |
$tmpDataType = ''; | |
switch (strtoupper($colDataType)) { | |
case 'CHAR': | |
$tmpDataType = 'BINARY'; | |
break; | |
case 'VARCHAR': | |
$tmpDataType = 'VARBINARY'; | |
break; | |
case 'TINYTEXT': | |
$tmpDataType = 'TINYBLOB'; | |
break; | |
case 'TEXT': | |
$tmpDataType = 'BLOB'; | |
break; | |
case 'MEDIUMTEXT': | |
$tmpDataType = 'MEDIUMBLOB'; | |
break; | |
case 'LONGTEXT': | |
$tmpDataType = 'LONGBLOB'; | |
break; | |
default: | |
$tmpDataType = ''; | |
break; | |
} | |
if ($tmpDataType === '') { | |
print "Unknown type! $colDataType\n"; | |
exit; | |
} | |
// Change the column definition to the new type | |
$tempColType = str_ireplace($colDataType, $tmpDataType, $colType); | |
// Convert the column to the temporary BINARY cousin | |
sqlExec($targetDB, "ALTER TABLE `$dbName`.`$tableName` MODIFY `$colName` $tempColType $colNull", $pretend); | |
// Convert it back to the original type with the correct collation | |
sqlExec($targetDB, "ALTER TABLE `$dbName`.`$tableName` MODIFY `$colName` $colType COLLATE $newCollation $colNull $colDefault", $pretend); | |
} | |
if ($tableCollation !== $newCollation) { | |
// Modify the default charset for this table | |
sqlExec($targetDB, "ALTER TABLE `$dbName`.`$tableName` DEFAULT COLLATE $newCollation", $pretend); | |
} | |
} | |
// | |
// TODO: Restore FULLTEXT indexes here | |
// eg. | |
// sqlExec($targetDB, "ALTER TABLE MyTable ADD FULLTEXT KEY `my_index_name` (`mycol1`)", $pretend); | |
// | |
// Set the default collation | |
sqlExec($infoDB, "ALTER DATABASE $dbName COLLATE $newCollation", $pretend); | |
// Done! | |
// | |
// Functions | |
// | |
/** | |
* Executes the specified SQL | |
* | |
* @param object $db Target SQL connection | |
* @param string $sql SQL to execute | |
* @param boolean $pretend Pretend mode -- if set to true, don't execute query | |
* | |
* @return SQL result | |
*/ | |
function sqlExec($db, $sql, $pretend = false) | |
{ | |
echo "$sql;\n"; | |
if ($pretend === false) { | |
if(!$res = $db->query($sql)) { | |
printf("Error: %s\n", $db->error); | |
}; | |
return $res; | |
} | |
return false; | |
} | |
/** | |
* Gets the SQL back as objects | |
* | |
* @param object $db Target SQL connection | |
* @param string $sql SQL to execute | |
* | |
* @return SQL objects | |
*/ | |
function sqlObjs($db, $sql) | |
{ | |
$res = sqlExec($db, $sql); | |
$a = array(); | |
if ($res !== false) { | |
while ($obj = $res->fetch_object()) { | |
$a[] = $obj; | |
} | |
} | |
return $a; | |
} | |
?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment