Created
December 29, 2014 14:39
-
-
Save wesamly/2c994e6bd4466151b60b to your computer and use it in GitHub Desktop.
Changing collation of MySQL database, tables and columns
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 | |
/* | |
* @Changing collation of database, tables and columns | |
* @Run this script only at your own risk. If you have a big database | |
* @you need to change the script execution time in your php | |
* @copyright (C) Jan Pavelka www.phoca.cz | |
* @license http://www.gnu.org/copyleft/gpl.html GNU/GPL | |
* @based on script from http://php.vrana.cz/ - Author - Jakub Vrana | |
* @license http://creativecommons.org/licenses/by/2.5/ | |
* @Creative Commons Attribution 2.5 Generic | |
*/ | |
?> | |
<html> | |
<head> | |
<style type="text/css"> | |
body {background-color:#000; font-family:"Courier New", Courier, monospace; font-size:12px;color:#ffffff} | |
</style> | |
</head> | |
<body> | |
<?php | |
function start_db($mysqlhost,$mysqldatabase, $mysqluser, $mysqlpass) | |
{ | |
global $conn; | |
$conn = mysql_connect($mysqlhost, $mysqluser, $mysqlpass); | |
if (!$conn) | |
{ | |
echo '<a href="index.php">Back to the main site</a><br />'; | |
die('Database error.'); | |
} | |
$select = mysql_select_db($mysqldatabase, $conn); | |
if (!$select) | |
{ | |
echo '<a href="index.php">Back to the main site</a><br />'; | |
die('Database error.'); | |
} | |
} | |
function end_db ($conn) | |
{ | |
mysql_close($conn); | |
} | |
if ( isset($_POST['host']) | |
&& isset($_POST['user']) | |
&& isset($_POST['pass']) | |
&& isset($_POST['name']) | |
&& isset($_POST['col'])) | |
{ | |
$mysqlhost = $_POST['host']; | |
$mysqluser = $_POST['user']; | |
$mysqlpass = $_POST['pass']; | |
$mysqldatabase = $_POST['name']; | |
$collation = $_POST['col']; | |
start_db($mysqlhost,$mysqldatabase, $mysqluser, $mysqlpass); | |
//Start code from http://php.vrana.cz/ - Author - Jakub Vrana | |
function mysql_convert($query) { | |
echo $query . ' ... <span style="color:#26d92b">OK</span><br /> '; | |
return mysql_query($query); | |
} | |
mysql_convert("ALTER DATABASE $mysqldatabase COLLATE $collation"); | |
$result = mysql_query("SHOW TABLES"); | |
while ($row = mysql_fetch_row($result)) { | |
mysql_convert("ALTER TABLE $row[0] COLLATE $collation"); | |
$result1 = mysql_query("SHOW COLUMNS FROM $row[0]"); | |
while ($row1 = mysql_fetch_assoc($result1)) { | |
if (preg_match('~char|text|enum|set~', $row1["Type"])) { | |
mysql_convert("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] CHARACTER SET binary"); | |
mysql_convert("ALTER TABLE $row[0] MODIFY $row1[Field] $row1[Type] COLLATE $collation" . ($row1["Null"] ? "" : " NOT NULL") . ($row1["Default"] && $row1["Default"] != "NULL" ? " DEFAULT '$row1[Default]'" : "")); | |
} | |
} | |
} | |
mysql_free_result($result); | |
//End code from http://php.vrana.cz/ - Author - Jakub Vrana | |
end_db($conn); | |
echo '<br /><a href="index.php">Back to the main page</a>'; | |
} | |
else | |
{ | |
?> | |
<h2> CHANGE DATABASE COLLATION (DATABASE, TABLES, COLUMNS)</h2> | |
<form action="index.php" method="post"> | |
DB Host:______<input type="text" name="host" value="localhost" /><br /> | |
DB User:______<input type="text" name="user" value="username" /><br /> | |
DB Password:__<input type="password" name="pass" value="password" /><br /> | |
DB Name:______<input type="text" name="name" value="database name" /><br /> | |
DB Collation:_<input type="text" name="col" value="utf8_general_ci" /><br /> | |
______________<input type="submit" value="Submit" /> | |
</form> | |
<?php | |
} | |
?> | |
</body> | |
</html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I couldn't find this script online, so I added it as a gist. Copyright notes are mentioned in the opening comments.