[sql] Hurtowa zmiana kodowania. Opcje

[starach]

Witam.
Chciałbym hurtowo zmienić kodowanie w bazie MySQL z latin2-general na utf8-general.
Jak to zrobić żeby kodowanie zostało zmienione od razu na wszystkich tabelach w danej bazie i jak zmienić domyślne kodowanie. Czy muszę przeprowadzać jakąś konwersję jeśli w tabelach znajdują się już rekordy ?

[SongoQ]

@qrcze_pieczone To chyba mowa o innej konversji.

[starach]

[SQL] pobierz, plaintext 
ALTER TABLE `config` CHARACTER SET utf8 COLLATE utf8_bin,
DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
[SQL] pobierz, plaintext 

Zadziałało ale w pewnym sensie...
Domyślny zbiór znaków został zmieniony ale nie zmienił mi obecnych.
To znaczy że jak było latin2 tak po wykonaniu powyższej komendy nadal jest.

edit>

[SQL] pobierz, plaintext 
ALTER TABLE `news` CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin,
DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
[SQL] pobierz, plaintext 

Tylko to działa.
Nie da się bezpośrednio z poziomu SQL'a zmienić kodowanie we wszystkich tabelach.

Ten post edytował orglee 14.04.2007, 17:52:48

[odpornedziecko]

czesc,
oczywiscie ze sie da hurtowo zmienic!
oto kod php z sql'em - trzeba tylko podac dostep do bazy
(oczywiscie przekopiowac to do pliku, zmienic txt na php, umiescic na serwerze i ... juz wynik odpalic w sql phpadmina)

p.s.

oto adres goscia, ktory to wymyslil heh

www.phpwact.org

a oto kodzik

[/php]

<?php

// this script will output the queries need to change all fields/tables to a different collation
// it is HIGHLY suggested you take a MySQL dump prior to running any of the generated
// this code is provided as is and without any warranty
// Code provided by <a href="http://www.phpwact.org/php/i18n/utf-8/mysql
//" title="http://www.phpwact.org/php/i18n/utf-8/mysql
//" rel="nofollow">http://www.phpwact.org/php/i18n/utf-8/mysql
//</a> Minor modifications made by webservant

die("Make a backup of your MySQL database then comment out this line");

set_time_limit(0);

// collation you want to change:
$convert_from = 'latin2_general_ci';

// collation you want to change it to:
$convert_to = 'utf8_general_ci';

// character set of new collation:
$character_set= 'utf8';

$show_alter_table = true;
$show_alter_field = true;
$show_alter_database = true;

// DB login information
$username = 'login';
$password = 'haslo';
$database = 'baza';
$host = 'host sql';

mysql_connect($host, $username, $password);
mysql_select_db($database);

$rs_tables = mysql_query(" SHOW TABLES ") or die(mysql_error());

print '<pre>';
// Alter database collation
if ($show_alter_database) {
echo("ALTER DATABASE `$database` DEFAULT CHARACTER SET $character_set COLLATE $convert_to;\r\n");
}
while ($row_tables = mysql_fetch_row($rs_tables)) {
$table = mysql_real_escape_string($row_tables[0]);

// Alter table collation
if ($show_alter_table) {
echo("ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set COLLATE $convert_to;\r\n");
}

$rs = mysql_query(" SHOW FULL FIELDS FROM `$table` ") or die(mysql_error());
while ($row=mysql_fetch_assoc($rs)) {

if ($row['Collation']!=$convert_from)
continue;

// Is the field allowed to be null?
if ($row['Null']=='YES') {
$nullable = ' NULL ';
} else {
$nullable = ' NOT NULL';
}

// Does the field default to null, a string, or nothing?
if ($row['Default']==NULL) {
$default = " DEFAULT NULL";
} else if ($row['Default']!='') {
$default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
} else {
$default = '';
}

// Alter field collation:
if ($show_alter_field) {
$field = mysql_real_escape_string($row['Field']);
echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable; \r\n";
}
}
}

?>

[starach]

@odpornedziecko: Kod odkrywczy nie jest. Ja wiem że łącząc skrypt z zapytaniami się da.

Nie da się bezpośrednio z poziomu SQL'a zmienić kodowanie we wszystkich tabelach.

Ale dzięki

[kacpero1094]

Dzięki. Bardzo przydatne

[tomsson]

czesc,
oczywiscie ze sie da hurtowo zmienic!
oto kod php z sql'em - trzeba tylko podac dostep do bazy
(oczywiscie przekopiowac to do pliku, zmienic txt na php, umiescic na serwerze i ... juz wynik odpalic w sql phpadmina)

p.s.

oto adres goscia, ktory to wymyslil heh

www.phpwact.org

a oto kodzik

[/php]

<?php

// this script will output the queries need to change all fields/tables to a different collation
// it is HIGHLY suggested you take a MySQL dump prior to running any of the generated
// this code is provided as is and without any warranty
// Code provided by <a href="http://www.phpwact.org/php/i18n/utf-8/mysql
//" title="http://www.phpwact.org/php/i18n/utf-8/mysql
//" rel="nofollow">http://www.phpwact.org/php/i18n/utf-8/mysql
//</a> Minor modifications made by webservant

die("Make a backup of your MySQL database then comment out this line");

set_time_limit(0);

// collation you want to change:
$convert_from = 'latin2_general_ci';

// collation you want to change it to:
$convert_to = 'utf8_general_ci';

// character set of new collation:
$character_set= 'utf8';

$show_alter_table = true;
$show_alter_field = true;
$show_alter_database = true;

// DB login information
$username = 'login';
$password = 'haslo';
$database = 'baza';
$host = 'host sql';

mysql_connect($host, $username, $password);
mysql_select_db($database);

$rs_tables = mysql_query(" SHOW TABLES ") or die(mysql_error());

print '<pre>';
// Alter database collation
if ($show_alter_database) {
echo("ALTER DATABASE `$database` DEFAULT CHARACTER SET $character_set COLLATE $convert_to;\r\n");
}
while ($row_tables = mysql_fetch_row($rs_tables)) {
$table = mysql_real_escape_string($row_tables[0]);

// Alter table collation
if ($show_alter_table) {
echo("ALTER TABLE `$table` DEFAULT CHARACTER SET $character_set COLLATE $convert_to;\r\n");
}

$rs = mysql_query(" SHOW FULL FIELDS FROM `$table` ") or die(mysql_error());
while ($row=mysql_fetch_assoc($rs)) {

if ($row['Collation']!=$convert_from)
continue;

// Is the field allowed to be null?
if ($row['Null']=='YES') {
$nullable = ' NULL ';
} else {
$nullable = ' NOT NULL';
}

// Does the field default to null, a string, or nothing?
if ($row['Default']==NULL) {
$default = " DEFAULT NULL";
} else if ($row['Default']!='') {
$default = " DEFAULT '".mysql_real_escape_string($row['Default'])."'";
} else {
$default = '';
}

// Alter field collation:
if ($show_alter_field) {
$field = mysql_real_escape_string($row['Field']);
echo "ALTER TABLE `$table` CHANGE `$field` `$field` $row[Type] CHARACTER SET $character_set COLLATE $convert_to $nullable; \r\n";
}
}
}

?>

ej zrobiłem plik wrzuciłem tam podany kod, dopisałem usera,baze , hasło i hosta i przy probie odpalenia pliku on mam takie coś
[code]
[/php]

ALTER DATABASE `dafe_ro` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bak_artforms` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `bak_artforms` CHANGE `titel` `titel` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `text` `text` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `danktext` `danktext` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `customjscode` `customjscode` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `customcss` `customcss` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `metakey` `metakey` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `metadesc` `metadesc` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `email` `email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `ccmail` `ccmail` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `bccmail` `bccmail` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `html` `html` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `seccode` `seccode` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `emailfield` `emailfield` char(2) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `allowatt` `allowatt` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `allowattfiles` `allowattfiles` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `author` `author` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `created_by_alias` `created_by_alias` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `bak_artforms` CHANGE `afeditor` `afeditor` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;
ALTER TABLE `jos_contact_details` CHANGE `misc` `misc` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci NULL ;

itd ...

[code/]

baza MYSQL dalej ma złą metodę porównywania napisów :/

zrobiłem coś nie tak ?