Paar PHP skripti MySQL tabelite automaatseks haldamiseks
(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
Comments are currently closed.