Senos MySQL duomenų bazės koduotės sutvarkymas

Kas kada nors bandė tvarkyti MySQL duomenų bazių koduočių problemas, tas žino, kad tai dažnai nėra paprastas reikalas. Savo koduotes turi duomenų bazės, lentelės ir net lentelių stulpeliai, ir šios koduotės gali būti skirtingos vienoje ir toje pačioje duomenų bazėje. O kadangi MySQL dar prieš gerą dešimtmetį duomenis standartiškai įrašydavo Latin1 koduote, senesnėse žiniatinklio programose pasitaiko visokiausių kuriozų su koduotėmis.

Štai ir aš neseniai gavau pataisyti vienos organizacijos naudojamą klientų registravimo duomenų bazę. Svetainėje, naudojančioje duomenų bazę, mačiau gražias lietuviškas raides, bet atsidaręs tą pačią duomenų bazę su PHPMyAdmin programa lietuviškas raides matydavau beviltiškai sugadintas. Tikra bėda, jei reikia kažką paskubom pataisyti tiesiog PHPMyAdmin duomenų bazės programoje… Aukščiau matėte paveikslėlį, kur kairėje – kaip duomenys atrodo svetainėje, dešinėje – PHPMyAdmin programoje.

PHPMyAdmin sąsaja buvo nustatyta naudoti universaliąją UTF8 koduotę. Svetainės programa – taip pat. Pasižiūrėjau į duomenų lentelę – ten irgi parašyta, kad naudojama UTF8 koduotė!

CREATE TABLE `admins` (
  `id` INT(11) NOT NULL,
  `name` VARCHAR(400) NOT NULL,
  ## praleidžiam kitus stulpelius
  `var` INT(2) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=61 DEFAULT CHARSET=utf8;

Taigi, kame problema? Knisimasis PHPMyAdmin nustatymuose nepadėjo. Nepadėjo ir bandymas eksportuoti duomenų bazę į tekstinį failą ir bandymas jį peržiūrėti pritaikant vis naują koduotę. Taigi, duomenys jau į patį teksto failą pateko sugadinti.

Paieškojus informacijos per Google nusprendžiau, kad matyt į duomenų bazės lentelę, kurios numatytoji koduotė yra UTF8, yra įterpiami Latin1 koduotės duomenys.

Taigi, jei PHPMyAdmin programoje duomenų bazės ir lentelių koduotė nustatyta į utf8, tačiau

SELECT * FROM `admins`;

rodo iškraipytas lietuviškas raides, labai gali būti, kad tai – ir Jūsų duomenų bazės problema. Kad tuo įsitikintumėte, PHPMyAdmin įvykdykite tokią komandą:

SET NAMES latin1; SELECT * FROM `admins`;

Jei turinį staiga parodys teisingai, problema tikrai ta.

Vienas iš būtų sutvarkyti duomenų bazę – pakeisti koduotę visuose duombazės lentelių stulpeliuose iš pradžių nurodant juose esančio teksto tikrąją koduotę (Latin1), tada konvertuojant stulpelius į dvejetainius duomenis, o vėliau iš dvejetainių – į UTF8 koduotės stulpelius.

Tai gan plačiai ir išsamiai aprašyta WordPress dokumentacijoje, žr. čia.

Ten siūloma rankiniu būdu surašyti ilgą sql scenarijų, konvertuojant visų teksto laukų koduotes į dvejetainį kodą tokiu būdu:

    CHAR ⇾ BINARY
    TEXT ⇾ BLOB
    TINYTEXT ⇾ TINYBLOB
    MEDIUMTEXT ⇾ MEDIUMBLOB
    LONGTEXT ⇾ LONGBLOB
    VARCHAR ⇾ VARBINARY

Atskira problema yra įvardinti ENUM tipo laukai.

O aš pamėginau padaryti php scenarijų, kuris vienu ypu padarytų visus keitimus visoje duomenų bazėje.

Su juo sutvarkiau ir organizacijos klientų registravimo sistemos duomenų bazę, ir taip pat šio tinklaraščio duomenų bazę. Kol kas nepastebėjau, kad kas nors būtų ne taip 🙂

WordPress tinklaraščio atveju užteko serveryje šį scenarijų įvykdyti ir įrašyti du papildomus parametrus į tinklaraščio konfigūracijos failą:

define( 'DB_CHARSET', 'utf8' );
define( 'DB_COLLATE', 'utf8_lithuanian_ci' );

Žemiau tą scenarijų pateikiu. Tačiau prieš naudojant primygtinai siūlau pasidaryti duomenų bazės kopiją. ir įvertinti, ar jūsų duomenų bazei scenarijus neturėtų būti išplėstas. Mano tvarkytoje duomenų bazėje nebuvo didelių teksto laukų (LONGTEXT ir MEDIUMTEXT tipo). Ir be to, pamėginau apsieiti be VARCHAR tipo laukų keitimo į BINARY tipo laukus (visi TEXT, TINYTEXT, VARCHAR, CHAR tipo laukai konvertuoti į BLOB). Atrodo, viskas praėjo sklandžiai, bet taip gali būti ne visais atvejais. Scenarijų pertvarkiau taip, kad jis teisingai pakeistų visų laukų tipus (char – į atitinkamą *binary tipą, *text – į atitinkamą \blob tipą.

Beje, scenarijų duomenų bazei galima vykdyti TIK VIENĄ KARTĄ; antrą kartą jis sugadins duombazę, nes scenarijuje preziumuojama, kad pirminė stulpelių koduotė – latin1.

Jei nuspręsite šiuo scenarijumi pasinaudoti, būkite labai atsargūs. Geriausiai jį išmėginkite ant savo tvarkomos duomenų bazės kopijos. Jei dirbsite su pagrindine duomenų baze, būtinai pasidarykite duomenų bazės kopiją ir būkite tikri, kad prireikus galėsite atkurti pirminius duomenis!

Kaip minėta, scenarijus nesutvarkys ENUM tipo laukų. Tačiau jei tokių aptiks, juos visus išvardins, tad galėsite rankiniu būdu juos sutvarkyti, kaip tai siūloma WordPress kodekse.

Tikiuosi kam nors tai bus naudinga. Papildomos informacijos, kaip jau minėjau, ieškokite WordPress kodekse.

<?php
session_start();
 
//ČIA YRA PRISIJUNGIMO DUOMENYS, PAKEISKITE JUOS, KAD TIKTŲ JŪSŲ SISTEMAI
$servername = "localhost";
$username = "as_pats";
$password = "mano_slaptazodis";
$dbname = "mano_db";
 
?>
<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <title>Duomenų bazės sutvarkymas</title>
  <link href="//www.w3schools.com/lib/w3.css" rel="stylesheet">
</head>
<body style="max-width:800px;margin:auto;">
 
<?php
if(isset($_SESSION['jau_leista'])) {
	echo "<h1>Šio scenarijaus leisti antrą kartą tai pačiai duomenų bazei negalima</h1>";
	echo "<p>Paleidus scenarijų tai pačiai duomenų bazei iš naujo jos duomenys bus sugadinti. </p>";
	echo "<p>Perkrovus puslapį scenarijus bus paleistas iš naujo. </p>";
	echo "<p>Jei <b>tikrai norite</b> vykdyti scenarijų, <b>spauskite F5</b>. Jei ne, tiesiog užverkite puslapį. </p>";
	unset($_SESSION['jau_leista']);
 
	echo "</body></html>";
	exit;
}
 
$_SESSION['jau_leista'] = 1;
 
// sukuriame prisijungimą
$conn = mysqli_connect($servername, $username, $password, $dbname);
// tikriname prisijungimą
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
//nustatome numatytąją koduotę
mysqli_set_charset( $conn, 'utf8');
 
 
print "<h1>Atliekamas duomenų bazės tekstinių duomenų koduotės konvertavimas</h1>";
print "<h2>Duomenų bazės pavadinimas: <b>$dbname</b></h2><br><br>";
 
//Ši užklausa sukuria visų duomenų bazės lentelių pavadinimų masyvą
//Žinau, kad surašyta nevėkšliškai :)
$sql2 = "SHOW TABLES";
$result2 = mysqli_query($conn, $sql2);
 
while ($listtables = mysqli_fetch_assoc($result2)) {
        $keys = array_keys($listtables);
        $key = $keys[0];
        $value = $listtables[$key];
        $tables[]=$value;
}
 
foreach ($tables as $table) {
 print "<br><br><b>LENTELĖ $table:</b> <br>";
  $sql = "DESCRIBE $table";
  $result = mysqli_query($conn, $sql);
 
  while ($row = mysqli_fetch_assoc($result)) {
    $col_name=$row['Field'];
    $col_type=$row['Type'];
    echo "<br>";
    print "Tikrinama, ar stulpelį $col_name reikia konvertuoti ...<br>";
    //galimi variantai: TEXT, MEDIUMTEXT, LONGTEXT, TINYTEXT, VARCHAR, CHAR, ENUM
    //šis scenarijus neapima tik enum
    if(strpos($col_type, 'text') !== false || strpos($col_type, 'char') !== false) {
        //nustatome atvejui naują tarpinį stulpelio tipą
        //text keičiame į blob, char - į binary
        $orig = array("char", "text");
        $subs = array("binary", "blob");  
        $col_type_bin = str_replace($orig, $subs, $col_type);
 
        print "... konvertuoti reikia; ";
        print "lentelė: $table, stulpelis: $col_name, tipas: $col_type;  tarpinis tipas: $col_type_bin. <br>";
        print "Vykdomos sql komandos koduotei pakeisti... <br>";
        $sql11="ALTER TABLE ".$table." CHANGE ".$col_name." ".$col_name." ".$col_type." CHARACTER SET latin1 COLLATE latin1_bin";
        $sql22="ALTER TABLE ".$table." CHANGE ".$col_name." ".$col_name." ".$col_type_bin;
        $sql33="ALTER TABLE ".$table." CHANGE ".$col_name." ".$col_name." ".$col_type." CHARACTER SET utf8mb4 COLLATE utf8_lithuanian_ci";
 
        $result11 = mysqli_query($conn, $sql11);
        $result22 = mysqli_query($conn, $sql22);
        $result33 = mysqli_query($conn, $sql33);
        print "... atlikta.<br>";
    }
     elseif(strpos($col_type, 'enum') !== false) {
       $enumlaukai[] = "<p>lentelė: $table, stulpelis: $col_name </p>";
     } else { print "... konvertuoti nereikia.<br>"; }
  }
 //nustatome pačios lentelės koduotę į utf8
 $sql44="ALTER TABLE ".$table." CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci";
 $result44 = mysqli_query($conn, $sql44);
 print "<b>Lentelės $table</b> pabaiga. <br><br>";
}
 print "<br><b>Duomenų bazės $dbname</b> pabaiga. <br><br>";
 
     //pačioj pabaigoj pridėti:
     if(isset($enumlaukai)) {
         echo "<h1>Duomenų bazėje $dbname dar yra „enum“ tipo laukų, kuriuose koduotę reikės tvarkyti rankiniu būdu.</h1>";
         echo "<p>Štai tų laukų sąrašas:</p>";
           foreach ($enumlaukai as $laukas) {
             print $laukas; 
           }
         } else {
         echo "<h1>Duomenų bazėje $dbname „enum“ tipo laukų nėra, tad konvesija baigta.</h1>";
         echo "<p>Atidžiai patikrinkite, ar viskas svetainėje atrodo gerai.</p>";
         }
 
mysqli_close($conn);
echo "</body></html>";
?>