Skip to content

Chris' Laboratory

chrislee.kr – Personal blog as bookshelves

Menu
  • Home
  • Github
  • Contact Me
Menu

A script to change all tables and fields to the utf-8-bin collation in MYSQL

Posted on 13/12/201029/08/2020 by Chris Lee
Read Time:2 Minute, 41 Second

A script to change all tables and fields to the utf-8-bin collation in MYSQL

(This article is from http://stackoverflow.com/questions/105572/a-script-to-change-all-tables-and-fields-to-the-utf-8-bin-collation-in-mysql)

 

<?php
set_time_limit(0);
function MysqlError()
{
if (mysql_errno())
{
echo "<b>Mysql Error: " . mysql_error() . "</b>\n";
}
}
$username = "root";
$password = "";
$db = "database";
$host = "localhost";
$target_charset = "utf8";
$target_collate = "utf8_general_ci";
echo "<pre>";
$conn = mysql_connect($host, $username, $password);
mysql_select_db($db, $conn);
$tabs = array();
$res = mysql_query("SHOW TABLES");
MysqlError();
while (($row = mysql_fetch_row($res)) != null)
{
$tabs[] = $row[0];
}
// now, fix tables
foreach ($tabs as $tab)
{
$res = mysql_query("show index from {$tab}");
MysqlError();
$indicies = array();
while (($row = mysql_fetch_array($res)) != null)
{
if ($row[2] != "PRIMARY")
{
$indicies[] = array("name" => $row[2], "unique" => !($row[1] == "1"), "col" => $row[4]);
mysql_query("ALTER TABLE {$tab} DROP INDEX {$row[2]}");
MysqlError();
echo "Dropped index {$row[2]}. Unique: {$row[1]}\n";
}
}
$res = mysql_query("DESCRIBE {$tab}");
MysqlError();
while (($row = mysql_fetch_array($res)) != null)
{
$name = $row[0];
$type = $row[1];
$set = false;
if (preg_match("/^varchar\((\d+)\)$/i", $type, $mat))
{
$size = $mat[1];
mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARBINARY({$size})");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARCHAR({$size}) CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
else if (!strcasecmp($type, "CHAR"))
{
mysql_query("ALTER TABLE {$tab} MODIFY {$name} BINARY(1)");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARCHAR(1) CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
else if (!strcasecmp($type, "TINYTEXT"))
{
mysql_query("ALTER TABLE {$tab} MODIFY {$name} TINYBLOB");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} TINYTEXT CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
else if (!strcasecmp($type, "MEDIUMTEXT"))
{
mysql_query("ALTER TABLE {$tab} MODIFY {$name} MEDIUMBLOB");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} MEDIUMTEXT CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
else if (!strcasecmp($type, "LONGTEXT"))
{
mysql_query("ALTER TABLE {$tab} MODIFY {$name} LONGBLOB");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} LONGTEXT CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
else if (!strcasecmp($type, "TEXT"))
{
mysql_query("ALTER TABLE {$tab} MODIFY {$name} BLOB");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} TEXT CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
if ($set)
mysql_query("ALTER TABLE {$tab} MODIFY {$name} COLLATE {$target_collate}");
}
// re-build indicies..
foreach ($indicies as $index)
{
if ($index["unique"])
{
mysql_query("CREATE UNIQUE INDEX {$index["name"]} ON {$tab} ({$index["col"]})");
MysqlError();
}
else
{
mysql_query("CREATE INDEX {$index["name"]} ON {$tab} ({$index["col"]})");
MysqlError();
}
echo "Created index {$index["name"]} on {$tab}. Unique: {$index["unique"]}\n";
}
// set default collate
mysql_query("ALTER TABLE {$tab}  DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
}
// set database charset
mysql_query("ALTER DATABASE {$db} DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
mysql_close($conn);
echo "</pre>";

 

Share

Facebook
Twitter
LinkedIn
Email

Related

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Categories

  • Databases (11)
    • MongoDB (4)
    • MS-SQL (1)
    • MySQL (6)
  • E-Commerce (8)
    • Magento (8)
  • Finance (2)
  • Frameworks (84)
    • Adobe Flex (1)
    • Angular (ngx) (3)
    • Codeigniter (6)
    • CSS (5)
    • Django (2)
    • Javascript (13)
    • Node.js (6)
    • PHP (17)
    • React Native (4)
    • React.js (1)
    • Sencha Touch (4)
    • Terraform (1)
    • Vue.js (1)
    • WordPress (4)
    • Yii2 (3)
  • General Documents (15)
  • Marketing (3)
  • Mobile Development (33)
    • Android (20)
    • iPhone (13)
  • Platforms (21)
    • Arduino (2)
    • Docker (5)
    • Google App Engine (5)
    • Raspberry Pi (5)
    • Samsung Smart TV (4)
  • Security (17)
  • Server (30)
    • Linux (12)
  • Tools (14)
    • SVN (7)
  • Uncategorized (2)

Search

Recent Posts

  • Taint all resources in the one module
  • Alpine – Plugin caching_sha2_password could not be loaded
  • npm link with peerDependencies
  • How to setup Gitlab runner with KVM enabled
  • Failed to transform bcprov-jdk15on-1.68.jar

Recent Comments

  • Obayed on Binance Auto Trading Bot – Buy low/Sell high with stop loss limit/Trade multiple coins
  • Ari on How to install memcache.so/memcached.so for MAMP Pro (Mac)
  • Mida ali on Binance Auto Trading Bot – Buy low/Sell high with stop loss limit/Trade multiple coins
  • Chris Lee on How to install memcache.so/memcached.so for MAMP Pro (Mac)
  • Chris Lee on Setting Up A VPN Server On OSX 10.6

Tags

1 ajax amazon android android-addpart browser chrislee-kr codeigniter codeigniter-tcpdf com-apple-net-racoon CSS CSS history hack delpaigmail-com entity-addpart-double exception-printing-is-disabled-by-default-for-security-reasons ext-plugins-listpagingplugin ext-plugins-listpagingplugin-example f iphone javascript jquery-defaultchecked jquery-samsung-smart-tv listpagingplugin mac magento-exception-printing-is-disabled-by-default-for-security-reasons magento-sample-data-exception-printing-is-disabled-by-default-for-security-reasons nu-vot null-core-errors-confignotfound-config-mk9engine-ini php samsung-smart-tv-jquery samsung-smart-tv-sdk-ajax samsung-smart-tv-sdk-jquery samsung-tv-sdk samsung-tv-sdk-jquery samsung tv sencha-smart-tv sencha-touch-list-paging smart-tv-jquery sqlite subversion svn tcedook tcpdf-codeigniter uilinebreakmodecharacterwrap-is-deprecated unknown-column-link-area

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
© 2023 Chris' Laboratory | Powered by Minimalist Blog WordPress Theme