php 在MYSQL中将所有表和字段更改为utf-8-bin排序规则的脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/105572/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
A script to change all tables and fields to the utf-8-bin collation in MYSQL
提问by DavidWinterbottom
Is there a SQLor PHPscript that I can run that will change the default collation in all tables and fields in a database?
是否有可以运行的SQL或PHP脚本来更改数据库中所有表和字段中的默认排序规则？
I can write one myself, but I think that this should be something that readily available at a site like this. If I can come up with one myself before somebody posts one, I will post it myself.
我可以自己写一个，但我认为这应该在这样的网站上很容易获得。如果我可以在有人发布之前自己想出一个，我会自己发布。
采纳答案by Buzz
Be careful! If you actually have utf stored as another encoding, you could have a real mess on your hands. Back up first. Then try some of the standard methods:
当心！如果您实际上将 utf 存储为另一种编码，则可能会弄得一团糟。先备份。然后尝试一些标准方法：
for instance http://www.cesspit.net/drupal/node/898http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html
例如 http://www.cesspit.net/drupal/node/898 http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html
I've had to resort to converting all text fields to binary, then back to varchar/text. This has saved my ass.
我不得不求助于将所有文本字段转换为二进制，然后再转换回 varchar/text。这救了我的屁股。
I had data is UTF8, stored as latin1. What I did:
我的数据是UTF8，存储为latin1。我做了什么：
Drop indexes. Convert fields to binary. Convert to utf8-general ci
删除索引。将字段转换为二进制。转换为utf8-general ci
If your on LAMP, don't forget to add set NAMES command before interacting with the db, and make sure you set character encoding headers.
如果您使用 LAMP，请不要忘记在与数据库交互之前添加 set NAMES 命令，并确保您设置了字符编码标题。
回答by DavidWinterbottom
Can be done in a single command (rather than 148 of PHP):
可以在单个命令中完成（而不是 PHP 的 148）：
mysql --database=dbname -B -N -e "SHOW TABLES" \
| awk '{print "SET foreign_key_checks = 0; ALTER TABLE", , "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' \
| mysql --database=dbname &
You've got to love the commandline...
(You might need to employ the --userand --passwordoptions for mysql).
您必须喜欢命令行...（您可能需要使用--user和--password选项mysql）。
EDIT: to avoid foreign key problems, added SET foreign_key_checks = 0;and SET foreign_key_checks = 1;
编辑：为了避免外键问题，添加SET foreign_key_checks = 0;和SET foreign_key_checks = 1;
回答by Ivan
I think it's easy to do this in two steps runin PhpMyAdmin.
Step 1:
我认为在 PhpMyAdmin 中运行两步很容易做到这一点。
第1步：
SELECT CONCAT('ALTER TABLE `', t.`TABLE_SCHEMA`, '`.`', t.`TABLE_NAME`,
'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') as stmt
FROM `information_schema`.`TABLES` t
WHERE 1
AND t.`TABLE_SCHEMA` = 'database_name'
ORDER BY 1
Step 2:
This query will output a list of queries, one for each table. You have to copy the list of queries, and paste them to the command line or to PhpMyAdmin's SQL tab for the changes to be made.
第 2 步：
此查询将输出一个查询列表，每个表一个。您必须复制查询列表，并将它们粘贴到命令行或 PhpMyAdmin 的 SQL 选项卡以进行更改。
回答by Ivan
OK, I wrote this up taking into account what was said in this thread. Thanks for the help, and I hope this script will help out others. I don't have any warranty for its use, so PLEASE BACKUP before running it. It shouldwork with all databases; and it worked great on my own.
好的，我写这个是考虑到这个线程中所说的。感谢您的帮助，我希望这个脚本能帮助其他人。我对它的使用没有任何保证，所以请在运行前备份。它应该适用于所有数据库；对我来说效果很好。
EDIT: Added vars at the top for which charset/collate to convert to. EDIT2: Changes the database's and tables' default charset/collate
编辑：在要转换为的字符集/整理的顶部添加变量。EDIT2：更改数据库和表的默认字符集/整理
<?php
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>";
?>
回答by Rich Adams
This PHP snippet will change the collation on all tables in a db. (It's taken from this site.)
此 PHP 代码段将更改数据库中所有表的排序规则。（取自本站。）
<?php
// your connection
mysql_connect("localhost","root","***");
mysql_select_db("db1");
// convert code
$res = mysql_query("SHOW TABLES");
while ($row = mysql_fetch_array($res))
{
foreach ($row as $key => $table)
{
mysql_query("ALTER TABLE " . $table . " CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci");
echo $key . " => " . $table . " CONVERTED<br />";
}
}
?>
回答by RameshVel
Another approach using command line, based on @david's without the awk
使用命令行的另一种方法，基于@david 的，没有 awk
for t in $(mysql --user=root --password=admin --database=DBNAME -e "show tables";);do echo "Altering" $t;mysql --user=root --password=admin --database=DBNAME -e "ALTER TABLE $t CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;";done
prettified
美化的
for t in $(mysql --user=root --password=admin --database=DBNAME -e "show tables";);
do
echo "Altering" $t;
mysql --user=root --password=admin --database=DBNAME -e "ALTER TABLE $t CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
done
回答by Dustin
A more complete version of the script above can be found here:
可以在此处找到上述脚本的更完整版本：
http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=1937
http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=1937
Please leave any feedback about this contribution here:http://www.zen-cart.com/forum/showthread.php?p=1034214
请在此处留下有关此贡献的任何反馈：http: //www.zen-cart.com/forum/showthread.php?p=1034214
回答by Alexander I.Grafov
In scripts above all tables selected for convertation (with SHOW TABLES), but a more convenient and portable way to check the table collation before converting a table. This query does it:
在上面所有选择转换的表的脚本中（使用SHOW TABLES），但是在转换表之前检查表排序规则的更方便和便携的方法。这个查询做到了：
SELECT table_name
, table_collation
FROM information_schema.tables
回答by troelskn
Charset and collation are not the same thing. A collation is a set of rules about how to sort strings. A charset is a set of rules about how to represent characters. A collation depends on the charset.
字符集和排序规则不是一回事。排序规则是一组关于如何对字符串进行排序的规则。字符集是一组关于如何表示字符的规则。排序规则取决于字符集。
回答by Abdennour TOUMI
Use my custom shell collatedb, it should work :
使用我的自定义 shell collatedb，它应该可以工作：
collatedb <username> <password> <database> <collation>
Example :
例子 ：
collatedb root 0000 myDatabase utf8_bin
