Paar PHP skripti MySQL tabelite automaatseks haldamiseks

18-02-2015 Kristjan MySQL, PHP

(Disclaimer: Antud koodi kopeerimine/mugandamine/käivitamine on selle tegevuse tegija omal vastutusel. Radicenter (ja/või tema töötajad) ei vastuta mingil määral selle koodi,toimivuse/toimimattuse ja/või väärkasutuse eest.)
NB! Ennem antud skriptide kopeerimist/käivitamist, TULEB KINDLASTI VÕTTA MUUDETAVA BAASI VARUKOOPIA!

1. Tabelite repairimine:

<?php

$db = new mysqli('localhost', 'ANDMEBAASIKASUTAJA','SALASÕNA');

if ($db->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";

$dbs = array();
$dbs[] = 'ANDMEBAASINIMI';

foreach($dbs as $v){
    $db->select_db($v);
    $q = $db->query('show tables');
    
    $tables = array();

    while($r = $q->fetch_array()){
            $tables[] = $r[0];
    }
    foreach($tables as $t){
        echo "Repairing $v.$t\n<br>";
        $x = $db->query('REPAIR TABLE `'.$t.'`;');
    }
}
$db->close();
echo 'All is well!';
?>

2. Tabelite storage mootori vahetamine:

<?php

$db = new mysqli('localhost', 'ANDMEBAASIKASUTAJA','SALASÕNA');

if ($db->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";

$dbs = array();
$dbs[] = 'ANDMEBAASINIMI';

foreach($dbs as $v){
    $db->select_db($v);
    $q = $db->query('show tables');
    
    $tables = array();

    while($r = $q->fetch_array()){
            $tables[] = $r[0];
    }
    foreach($tables as $t){
        echo "Altering table engine for: $v.$t\n<br>";
        $db->query('ALTER TABLE `'.$t.'` ENGINE=InnoDB;');
    }
}
$db->close();
echo 'All is well!';
?>

 
NB! Engine tüüp võib olla MYISAM või INNODB või…. – rohkem infot lehel: SIIN

3. Tabelite character set/encoding vahetamine misiganes -> utf8mb4_unicode_ci:
(NB! Ennem selle koodi käivitamist TULEB KINDLASTI TEHA VARUKOOPIA ANDMEBAASIST!)

<?php

$db = new mysqli('localhost', 'ANDMEBAASIKASUTAJA','SALASÕNA');

if ($db->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 
echo "Connected successfully";

$dbs = array();
$dbs[] = 'ANDMEBAASINIMI';

foreach($dbs as $v){
    $db->select_db($v);
    echo "Altering database character set for: $v\n<br>";
    $db->query('ALTER DATABASE `'.$v.'` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;');
    $q = $db->query('show tables');
    
    $tables = array();

    while($r = $q->fetch_array()){
            $tables[] = $r[0];
    }
    foreach($tables as $t){
        echo "Altering table character set for: $v.$t\n<br>";
        $db->query('ALTER TABLE `'.$t.'` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;');
    }
    //Converting also needed columns
    echo "Altering table column_name character sets for all table colums under: $v\n<br>";
    $column_query = $db->query("select CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME,'` CHANGE ',COLUMN_NAME,' ', COLUMN_NAME, ' ', DATA_TYPE,'(',CHARACTER_MAXIMUM_LENGTH,') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') as column_alter from information_schema.COLUMNS WHERE DATA_TYPE = 'varchar' AND TABLE_SCHEMA = '".$v."'");
    foreach($column_query as $x){
        $db->query($x);
    }
}
$db->close();
echo 'All is well!';
?>

Turvalisuse lisa WP ja Joomla Kuidas saata Gmaili manusena saadud e-kirja