Laufvariablen mit MySQL

Wenn man mit PHP eine Laufvariable bauen will, dann weiß jeder sofort wie das geht. Nehmen wir beispielsweise mal eine Ranking, das die Punkte und den aktuellen Rang einer Benutzertabelle ausgeben soll. Das Skript würde dann so ähnlich aussehen:

View Code PHP
$position = 1;
$result = mysql_query("SELECT benutzername, punkte FROM benutzer ORDER BY punkte DESC");
while($row = mysql_fetch_assoc($result))
{
	echo "{$row['benutzername']} - {$row['punkte']} Punkte, Rang {$position}<br>";
	$position++;
}

Aber wie löst man sowas, wenn man nur MySQL zur Verfügung hat?
Im Grunde funktioniert das genau wie in PHP, nur die Syntax ist ein wenig anders. Es wird genau wie in PHP eine neue Variable deklariert und diese beim jeden ausgelesenen Datensatz hochgezählt.
Das geänderte SQL-Query sieht dann so aus:

View Code SQL
SET @position = 0;
SELECT @position := @position + 1 AS position, benutzername, punkte FROM benutzer ORDER BY punkte DESC;

In der ersten Zeile wird die Variable @position definiert und innerhalb des SELECT-Aufrufs wird die Variable dann jeweils um eins erhöht und der neue Wert zwischengespeichert.

So lässt sich zum Beispiel das aktuelle Ranking der Benutzertabelle direkt über MySQL in eine zweite Tabelle abspeichern, ohne jeden Datensatz einzeln mit PHP auslesen und wieder neu einfügen zu müssen:

View Code SQL
SET @position = 0;
INSERT INTO benutzer_ranking (position, benutzername, punkte) SELECT @position := @position + 1 AS position, benutzername, punkte FROM benutzer ORDER BY punkte DESC;

Mir fällt gerade auf, dass dies hier der 50. Artikel auf meinem Blog ist – ein Jubiläum 🙂
Dann muss ich mir für den nächsten Artikel etwas besonderes einfallen lassen.

MyISAM vs. InnoDB – was ist besser?

Diese Frage lässt sich eigentlich nicht beantworten, denn wie es sooft der Fall ist (z.B. auch bei Windows vs. Linux vs. Mac), haben beide Systeme ihre Vorteile und je nachdem, was man gerade braucht, nutzt man besser MyISAM oder InnoDB. Aber woher weiß man, welches der beiden MySQL-Storage-Engines man braucht? Dazu muss man wissen, welche Vorteile und Nachteile die Systeme haben. Ich habe dazu mal einiges an Wissen zusammengetragen.

Eigenschaften von InnoDB

  • Referentielle Integrität wird automatisch eingehalten
    • Das muss zuvor explizit festgelegt werden
  • SELECT wird schneller ausgeführt
    • INSERT und UPDATE ist dafür langsamer
  • Schreibzugriffe auf Datensätze können gesperrt werden

Eigenschaften MyISAM

  • Häufiger genutzt und gängigster Standard (hohe Stabilität)
  • INSERT und UPDATE werden schneller ausgeführt als bei InnoDB
    • SELECT ist dafür langsamer
  • Integrierte Volltextsuche
  • Schreibzugriffe nur auf ganze Tabellen

Wann setzt man InnoDB ein?

InnoDB eignet sich vor allem bei großen Datenbanken, wo viele Daten miteinander verknüpft sind. Wenn hier Daten gelöscht werden, löscht InnoDB alle referenzierten Daten automatisch mit. Das macht es der Anwendung hinter der Datenbank leichter, die referentielle Integrität einzuhalten.
Prominente Beispiele für Web-Anwendungen mit InnoDB sind unter anderem:

Wann setzt man MyISAM ein?

Bei kleinen Datenbanken oder Anwendungen, wo viel und oft Daten geändert werden. Hier ist MyISAM deutlich performanter, weil INSERT und UPDATE schneller sind. Und natürlich wenn man Texte durchsuchen möchte (Volltextsuche).
Prominente Beispiele für Web-Anwendungen mit MyISAM sind unter anderem:

Und wenn ich beides brauche?
Natürlich ist es möglich, beide Storage Engines für verschiedene Tabellen in der selben Datenbank zu verwenden. So setzt Mediawiki z.B. für die Standard-Suche die MySQL Volltextsuche von MyISAM ein und der Rest der Datenbank verwendet InnoDB.

Schlusswort

Es gibt also nicht DIE beste Storage Engine für MySQL. Je nach Verwendungszweck eignet sich InnoDB oder MyISAM besser.
Ich persönlich nutze lieber InnoDB, aber ihr könnt euch nun selbst eine Meinung bilden.

PHP > MySQL Datenbank in UTF-8 konvertieren

Folgendes PHP-Skript konvertiert eine komplette MySQL Datenbank in UTF-8:

View Code PHP
define('DB_HOST', 'localhost');
define('DB_NAME', 'datenbank_name');
define('DB_USER', 'benutzer');
define('DB_PASSWD', 'supergeheimespasswort');
 
mysql_connect(DB_HOST, DB_USER, DB_PASSWD);
mysql_select_db(DB_NAME);
 
$result = mysql_query("SHOW TABLES");
while($row = mysql_fetch_assoc($result))
{
	$tableName = $row['Tables_in_'.DB_NAME];
	$fields = array();
	$primaryKey = null;
	mysql_query("ALTER TABLE `{$tableName}` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci");
	$_result = mysql_query("DESCRIBE `{$tableName}`");
	while($_row = mysql_fetch_assoc($_result))
	{
		if(isset($_row['Key']) && $_row['Key'] == 'PRI' && !$primaryKey)
		{
			$primaryKey = $_row['Field'];
		}
		if(strpos($_row['Type'], 'varchar') !== false || strpos($_row['Type'], 'text') !== false)
		{
			$fields[] = $_row['Field'];
			mysql_query("ALTER TABLE `{$tableName}` CHANGE `{$_row['Field']}` `{$_row['Field']}` {$_row['Type']} CHARACTER SET utf8 COLLATE utf8_general_ci");
		}
	}
	if(!empty($fields) && !empty($primaryKey))
	{
		$fields[] = $primaryKey;
		$_result = mysql_query("SELECT `".implode('`,`', $fields)."` FROM `{$tableName}`");
		while($_row = mysql_fetch_assoc($_result))
		{
			$values = array();
			foreach($_row as $field => &$value)
			{
				if($field != $primaryKey && !empty($value))
				{
					$values[] = "`".$field."` = '".utf8_encode($value)."'";
				}
			}
			$sql = "UPDATE `{$tableName}` SET ".implode(',', $values)." WHERE `{$primaryKey}` = '{$_row[$primaryKey]}'";
			mysql_query($sql);
		}
	}
}

Es werden nicht nur die Tabellen selbst, sondern auch alle geeigneten Felder konvertiert.

UPDATE 31.03.2011:
Ich habe das Skript noch einmal erweitert, sodass nicht nur die Datenbank konvertiert wird, sondern auch die Inhalte alle Datensätze.