Есть ли SQL или PHP script, который я могу запустить, который изменит сортировку по умолчанию во всех таблицах и полях в базе данных?
Я могу написать один сам, но я думаю, что это должно быть что-то, что легко доступно на таком сайте. Если я смогу придумать один, прежде чем кто-нибудь разместит его, я отправлю его сам.
Будьте осторожны! Если вы действительно используете utf в качестве другой кодировки, у вас может быть реальный беспорядок на ваших руках. Сначала создайте резервную копию. Затем попробуйте некоторые стандартные методы:
например http://www.cesspit.net/drupal/node/898 http://www.hackszine.com/blog/archive/2007/05/mysql_database_migration_latin.html
Мне приходилось прибегать к преобразованию всех текстовых полей в двоичные, а затем обратно в varchar/text. Это спасло мою задницу.
У меня были данные UTF8, сохраненные как latin1. Что я сделал:
Индексы падения. Преобразование полей в двоичные. Преобразовать в utf8-general ci
Если вы используете LAMP, не забудьте добавить команду set NAMES перед взаимодействием с db и убедитесь, что вы устанавливаете заголовки кодировки символов.
Может быть сделано в одной команде (вместо 148 PHP):
mysql --database=dbname -B -N -e "SHOW TABLES" \
| awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' \
| mysql --database=dbname &
Вы должны любить командный пункт...
(Возможно, вам нужно использовать опции --user и --password для mysql).
EDIT: чтобы избежать проблем с внешним ключом, добавлены SET foreign_key_checks = 0; и SET foreign_key_checks = 1;
Я думаю, что это легко сделать в два этапа runin 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
Шаг 2:
Этот запрос выведет список запросов, по одному для каждой таблицы. Вам нужно скопировать список запросов и вставить их в командной строке или на вкладку PhpMyAdmin SQL для внесения изменений.
Хорошо, я написал это с учетом того, что было сказано в этой теме. Спасибо за помощь, и я надеюсь, что этот script поможет другим. У меня нет никаких гарантий для его использования, поэтому ПОЖАЛУЙСТА, ОБРАТИТЕСЬ, прежде чем запускать его. Он должен работать со всеми базами данных; и он отлично работал сам по себе.
EDIT: добавлены vars наверху, для которых 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>";
?>
Этот фрагмент PHP изменит сортировку на всех таблицах в db. (Это взято из этот сайт.)
<?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 />";
}
}
?>
Другой подход с использованием командной строки, основанный на @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
Более полную версию script выше можно найти здесь:
http://www.zen-cart.com/index.php?main_page=product_contrib_info&products_id=1937
Оставьте здесь свой отзыв: http://www.zen-cart.com/forum/showthread.php?p=1034214
Кодировка и сортировка - это не одно и то же. Сортировка - это набор правил о сортировке строк. Кодировка - это набор правил о том, как представлять символы. Сортировка зависит от кодировки.
В сценариях выше всех таблиц, выбранных для преобразования (с SHOW TABLES), но более удобный и переносимый способ проверки сопоставления таблицы перед преобразованием таблицы. Этот запрос делает это:
SELECT table_name
, table_collation
FROM information_schema.tables
Используйте мою собственную оболочку collatedb, она должна работать:
collatedb <username> <password> <database> <collation>
Пример:
collatedb root 0000 myDatabase utf8_bin
Спасибо @nlaq за код, который заставил меня начать работу над нижеследующим решением.
Я выпустил плагин WordPress, не понимая, что WordPress не устанавливает сопоставление автоматически. Так много людей, использующих плагин, оказалось latin1_swedish_ci, когда оно должно было быть utf8_general_ci.
Здесь код, который я добавил в плагин, чтобы определить latin1_swedish_ci, и изменить его на utf8_general_ci.
Протестируйте этот код перед его использованием в своем собственном плагине!
// list the names of your wordpress plugin database tables (without db prefix)
$tables_to_check = array(
'social_message',
'social_facebook',
'social_facebook_message',
'social_facebook_page',
'social_google',
'social_google_mesage',
'social_twitter',
'social_twitter_message',
);
// choose the collate to search for and replace:
$convert_fields_collate_from = 'latin1_swedish_ci';
$convert_fields_collate_to = 'utf8_general_ci';
$convert_tables_character_set_to = 'utf8';
$show_debug_messages = false;
global $wpdb;
$wpdb->show_errors();
foreach($tables_to_check as $table) {
$table = $wpdb->prefix . $table;
$indicies = $wpdb->get_results( "SHOW INDEX FROM `$table`", ARRAY_A );
$results = $wpdb->get_results( "SHOW FULL COLUMNS FROM `$table`" , ARRAY_A );
foreach($results as $result){
if($show_debug_messages)echo "Checking field ".$result['Field'] ." with collat: ".$result['Collation']."\n";
if(isset($result['Field']) && $result['Field'] && isset($result['Collation']) && $result['Collation'] == $convert_fields_collate_from){
if($show_debug_messages)echo "Table: $table - Converting field " .$result['Field'] ." - " .$result['Type']." - from $convert_fields_collate_from to $convert_fields_collate_to \n";
// found a field to convert. check if there an index on this field.
// we have to remove index before converting field to binary.
$is_there_an_index = false;
foreach($indicies as $index){
if ( isset($index['Column_name']) && $index['Column_name'] == $result['Field']){
// there an index on this column! store it for adding later on.
$is_there_an_index = $index;
$wpdb->query( $wpdb->prepare( "ALTER TABLE `%s` DROP INDEX %s", $table, $index['Key_name']) );
if($show_debug_messages)echo "Dropped index ".$index['Key_name']." before converting field.. \n";
break;
}
}
$set = false;
if ( preg_match( "/^varchar\((\d+)\)$/i", $result['Type'], $mat ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` VARBINARY({$mat[1]})" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` VARCHAR({$mat[1]}) CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
} else if ( !strcasecmp( $result['Type'], "CHAR" ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` BINARY(1)" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` VARCHAR(1) CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
} else if ( !strcasecmp( $result['Type'], "TINYTEXT" ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` TINYBLOB" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` TINYTEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
} else if ( !strcasecmp( $result['Type'], "MEDIUMTEXT" ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` MEDIUMBLOB" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` MEDIUMTEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
} else if ( !strcasecmp( $result['Type'], "LONGTEXT" ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` LONGBLOB" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` LONGTEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
} else if ( !strcasecmp( $result['Type'], "TEXT" ) ) {
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` BLOB" );
$wpdb->query( "ALTER TABLE `{$table}` MODIFY `{$result['Field']}` TEXT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
$set = true;
}else{
if($show_debug_messages)echo "Failed to change field - unsupported type: ".$result['Type']."\n";
}
if($set){
if($show_debug_messages)echo "Altered field success! \n";
$wpdb->query( "ALTER TABLE `$table` MODIFY {$result['Field']} COLLATE $convert_fields_collate_to" );
}
if($is_there_an_index !== false){
// add the index back.
if ( !$is_there_an_index["Non_unique"] ) {
$wpdb->query( "CREATE UNIQUE INDEX `{$is_there_an_index['Key_name']}` ON `{$table}` ({$is_there_an_index['Column_name']})", $is_there_an_index['Key_name'], $table, $is_there_an_index['Column_name'] );
} else {
$wpdb->query( "CREATE UNIQUE INDEX `{$is_there_an_index['Key_name']}` ON `{$table}` ({$is_there_an_index['Column_name']})", $is_there_an_index['Key_name'], $table, $is_there_an_index['Column_name'] );
}
}
}
}
// set default collate
$wpdb->query( "ALTER TABLE `{$table}` DEFAULT CHARACTER SET {$convert_tables_character_set_to} COLLATE {$convert_fields_collate_to}" );
if($show_debug_messages)echo "Finished with table $table \n";
}
$wpdb->hide_errors();
Простое (немое?:) решение с использованием функции множественного выбора вашей среды IDE:
Я думаю, что самый быстрый способ - с phpmyadmin и некоторым jQuery на консоли.
Перейдите в структуру таблицы и откройте консоль разработчика xrome/firefox (обычно F12 на клавиатуре):
запустите этот код, чтобы выбрать все поля с неправильной кодировкой и начать изменять:
var elems = $('dfn'); var lastID = elems.length - 1;
elems.each(function(i) {
if ($(this).html() != 'utf8_general_ci') {
$('input:checkbox', $('td', $(this).parent().parent()).first()).attr('checked','checked');
}
if (i == lastID) {
$("button[name='submit_mult'][value='change']").click();
}
});
Когда страница загружается, используйте этот код на консоли, чтобы выбрать правильную кодировку:
$("select[name*='field_collation']" ).val('utf8_general_ci');
сохранить
изменить кодировку таблицы в поле "Collation" на вкладке "Операция"
Проверено на phpmyadmin 4.0 и 4.4, но я думаю, что работа над всеми версиями 4.x
Вот простой способ сделать это с помощью только phpmyadmin, если у вас нет доступа к командной строке или доступа к редактированию INFORMATION_SCHEMA.
Во-первых, послушайте советы многих других ответов здесь - вы действительно можете повредить здесь, поэтому сделайте резервную копию. Теперь сделайте резервную копию своей резервной копии. Кроме того, это вряд ли будет работать, если ваши данные закодированы иначе, чем то, что вы меняете.
Обратите внимание, что вам нужно будет найти точные имена нарушающей схемы и кодировки символов, которые вам нужно изменить до начала.
Это очень простой способ сделать это, но опять же, это не изменит кодировку ваших данных, поэтому оно будет работать только в определенных обстоятельствах.
Я обновил nlaq ответ для работы с PHP7 и корректно обрабатывал индексы с несколькими столбцами, двоичные данные (например, latin1_bin) и т.д. и немного очистил код. Это единственный код, который я нашел/попробовал, который успешно перенесла мою базу данных с latin1 на utf8.
<?php
/////////// BEGIN CONFIG ////////////////////
$username = "";
$password = "";
$db = "";
$host = "";
$target_charset = "utf8";
$target_collation = "utf8_unicode_ci";
$target_bin_collation = "utf8_bin";
/////////// END CONFIG ////////////////////
function MySQLSafeQuery($conn, $query) {
$res = mysqli_query($conn, $query);
if (mysqli_errno($conn)) {
echo "<b>Mysql Error: " . mysqli_error($conn) . "</b>\n";
echo "<span>This query caused the above error: <i>" . $query . "</i></span>\n";
}
return $res;
}
function binary_typename($type) {
$mysql_type_to_binary_type_map = array(
"VARCHAR" => "VARBINARY",
"CHAR" => "BINARY(1)",
"TINYTEXT" => "TINYBLOB",
"MEDIUMTEXT" => "MEDIUMBLOB",
"LONGTEXT" => "LONGBLOB",
"TEXT" => "BLOB"
);
$typename = "";
if (preg_match("/^varchar\((\d+)\)$/i", $type, $mat))
$typename = $mysql_type_to_binary_type_map["VARCHAR"] . "(" . (2*$mat[1]) . ")";
else if (!strcasecmp($type, "CHAR"))
$typename = $mysql_type_to_binary_type_map["CHAR"] . "(1)";
else if (array_key_exists(strtoupper($type), $mysql_type_to_binary_type_map))
$typename = $mysql_type_to_binary_type_map[strtoupper($type)];
return $typename;
}
echo "<pre>";
// Connect to database
$conn = mysqli_connect($host, $username, $password);
mysqli_select_db($conn, $db);
// Get list of tables
$tabs = array();
$query = "SHOW TABLES";
$res = MySQLSafeQuery($conn, $query);
while (($row = mysqli_fetch_row($res)) != null)
$tabs[] = $row[0];
// Now fix tables
foreach ($tabs as $tab) {
$res = MySQLSafeQuery($conn, "SHOW INDEX FROM `{$tab}`");
$indicies = array();
while (($row = mysqli_fetch_array($res)) != null) {
if ($row[2] != "PRIMARY") {
$append = true;
foreach ($indicies as $index) {
if ($index["name"] == $row[2]) {
$index["col"][] = $row[4];
$append = false;
}
}
if($append)
$indicies[] = array("name" => $row[2], "unique" => !($row[1] == "1"), "col" => array($row[4]));
}
}
foreach ($indicies as $index) {
MySQLSafeQuery($conn, "ALTER TABLE `{$tab}` DROP INDEX `{$index["name"]}`");
echo "Dropped index {$index["name"]}. Unique: {$index["unique"]}\n";
}
$res = MySQLSafeQuery($conn, "SHOW FULL COLUMNS FROM `{$tab}`");
while (($row = mysqli_fetch_array($res)) != null) {
$name = $row[0];
$type = $row[1];
$current_collation = $row[2];
$target_collation_bak = $target_collation;
if(!strcasecmp($current_collation, "latin1_bin"))
$target_collation = $target_bin_collation;
$set = false;
$binary_typename = binary_typename($type);
if ($binary_typename != "") {
MySQLSafeQuery($conn, "ALTER TABLE `{$tab}` MODIFY `{$name}` {$binary_typename}");
MySQLSafeQuery($conn, "ALTER TABLE `{$tab}` MODIFY `{$name}` {$type} CHARACTER SET '{$target_charset}' COLLATE '{$target_collation}'");
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
$target_collation = $target_collation_bak;
}
// Rebuild indicies
foreach ($indicies as $index) {
// Handle multi-column indices
$joined_col_str = "";
foreach ($index["col"] as $col)
$joined_col_str = $joined_col_str . ", `" . $col . "`";
$joined_col_str = substr($joined_col_str, 2);
$query = "";
if ($index["unique"])
$query = "CREATE UNIQUE INDEX `{$index["name"]}` ON `{$tab}` ({$joined_col_str})";
else
$query = "CREATE INDEX `{$index["name"]}` ON `{$tab}` ({$joined_col_str})";
MySQLSafeQuery($conn, $query);
echo "Created index {$index["name"]} on {$tab}. Unique: {$index["unique"]}\n";
}
// Set default character set and collation for table
MySQLSafeQuery($conn, "ALTER TABLE `{$tab}` DEFAULT CHARACTER SET '{$target_charset}' COLLATE '{$target_collation}'");
}
// Set default character set and collation for database
MySQLSafeQuery($conn, "ALTER DATABASE `{$db}` DEFAULT CHARACTER SET '{$target_charset}' COLLATE '{$target_collation}'");
mysqli_close($conn);
echo "</pre>";
?>
В дополнение к ответу @davidwinterbottom, пользователи Windows могут использовать следующую команду:
mysql.exe --database=[database] -u [user] -p[password] -B -N -e "SHOW TABLES" \
| awk.exe '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}' \
| mysql.exe -u [user] -p[password] --database=[database] &
Замените заполнители [базы данных], [пользователя] и [пароль] фактическими значениями.
Git - bash пользователи могут загрузить bash script и запустить его легко.