Datenbanken: MySQL

MySQL ist ein Datenbankmanagementsystem, welches ursprünglich von Michael "Monty" Widenius von der schwedischen Firma TcX programmiert wurde. Aktuell ist die Version 3.23, welche unter der GPL steht. Links zu MySQL und SQL im Allgemeinen findet man unter sql-lernen .

Frage: Kommt MySQL mit mehr als x Datensätzen pro Tabelle klar? Wie stabil ist MySQL?

MySQL ist als Datenbank äußerst stabil und auch bei großen Datenmengen extrem schnell und effizient. Die Grenzen von MySQL liegen nicht so sehr in der Größe der Tabellen oder der Anzahl von Datensätzen, sondern in der Komplexität der Datenmodelle, die damit implementiert werden kann.

MySQL speichert Daten mit Index in Baumstrukturen. Auf diese Datenstrukturen kann mit logarithmischer Komplexität zugegriffen werden, d.h. für eine Tabelle mit n Datensätzen sind log(b, n) Zugriffe notwendig, bis der gesuchte Datensatz gefunden ist. b ist die Basis des Logarithmus. Wäre b gleich 2, dann wären zum Zugriff auf eine Tabelle mit 1.000 Datensätzen maximal 10, auf eine Tabelle mit 1.000.000 Datensätzen maximal 20 und auf eine Tabelle mit 1.000.000.000 maximal 30 Zugriffe notwendig, um einen beliebigen Zieldatensatz über den Index zu finden. Tatsächlich ist die Basis jedoch nicht 2, sondern weit größer. Sie ist abhängig von der internen Blockgröße der Datenbank und der mittleren Satzlänge in einem Index. Man kann annehmen, dass sie je nach Art der Daten zwischen 20 und 40 liegt. Damit wären zum Finden eines Datensatzes aus 1.000 Datensätzen maximal 3, aus 1.000.000 Datensätzen maximal 5 und aus 1.000.000.000 Datensätzen maximal 7 Vergleiche und Plattenzugriffe notwendig.

Entsprechend sind die Erfahrungen, die mit MySQL berichtet werden: Im Rahmen der Begrenzungen des Betriebssystems kommt MySQL mit beliebig großen Tabellen problemlos klar. Eine Auflistiung der Betriebssystem-spezifischen Dateigrößen-Beschränkungen findest du im MySQL-Handbuch unter Wie groß können MySQL-Tabellen sein? .

Beschränkungen ergeben sich in MySQL aus dem Fehlen bestimmter Eigenschaften wie Erzwingung referentieller Integrität (keine foreign key Prüfungen, siehe dazu auch die Bemerkungen über PostgreSQL) und Transaktionen. Das Fehlen dieser Eigenschaften macht die Implementierung von Datenbankschemata sehr mühsam, die schreibend auf mehr als eine Tabelle zur Zeit zugreifen.

Man kann abschätzen, ob MySQL für eine Aufgabe das passende Tool ist, indem man sich das geplante Datenbankschema und die geplanten Transaktionen auf diesem Schema ansieht, alle n:m und Sternbeziehungen isoliert und dann feststellt, in welchen dieser Beziehungen schreibende Zugriffe notwendig sind, die mehr als eine Tabelle aktualisieren.

MySQL ist geeignet für alle Modelle, die read-mostly sind oder die weitaus überwiegend Schreibzugriffe auf einzelne Tabellen haben. MySQL ist nicht optimal geeignet, wenn ein Modell sehr viele Schreibzugriffe hat, wenn ein Modell mehr als 2 Schreibzugriffe hat, die mehr als eine Tabelle gleichzeitig aktualisieren oder wenn ein Modell zwingend auf referentielle Integrität angewiesen ist, aber mehr als eine Anwendung schreibend auf den Bestand zugreift.

Frage: Wie greife ich auf eine MySQL-Datenbank zu?

Mit Hilfe der Funktion mysql_connect() kann man eine Datenbankverbindung zu einem MySQL-Server aufbauen, um dann mit mysql_select_db() die Datenbank auf dem Server auszuwählen. Das Resultat ist eine Link-ID , die man bei allen anderen MySQL-Funktionen angeben kann.

In der php.ini kann man festlegen, welche Datenbank und welcher Benutzername und welches Passwort die Funktion verwenden soll, wenn diese Angaben beim Funktionsaufruf weggelassen werden, auch wenn es nur untermittelschlau ist, dies zu tun:

; default host for mysql_connect() (doesn't apply in safe mode)
mysql.default_host  =
; default user for mysql_connect() (doesn't apply in safe mode)
mysql.default_user  =
; default password for mysql_connect() (doesn't apply in safe mode)
mysql.default_password  =

; Note that this is generally a *bad* idea to store passwords
; in this file. *Any* user with PHP access can run
; 'echo cfg_get_var("mysql.default_password")' and reveal that
; password!  And of course, any users with read access to this
; file will be able to reveal the password as well. 

Kontaktaufnahme mit dem Server und Festlegen der Datenbank:

$link = mysql_connect("localhost", "ich", "geheim");
  if (!$link)
    die("Kann den Server nicht erreichen.");
  if (!mysql_select_db("meinedatenbank", $link))
    die("Kann die Datenbank nicht anwählen."); 

Das Senden einer Anfrage an die Datenbank erfolgt mit Hilfe der Funktion mysql_query() . Diese Funktion liefert einen Result-Identifier, mit dem dann das Ergebnis abgefragt werden kann: Mit der Funktion mysql_num_rows() bestimmt man die Anzahl der Zeilen des Ergebnisses und mit Hilfe der Funktion mysql_fetch_array() kann man die jeweils aktuelle Zeile des Ergebnisses einlesen.

$query = "SELECT * FROM meinetabelle ORDER BY id";
$result = mysql_query($query, $link);
if (!$result)
{
    print mysql_error();
    die("Query $query ist ungültiges SQL.");
}

$zeilen = mysql_num_rows($result);
printf("Das Ergebnis hat %d Zeilen.\n", $zeilen);

while($avar = mysql_fetch_array($result))
    printf("Spalte bla hat den Wert %s\n", $avar["bla"]);

mysql_free_result($result);
mysql_close($link); 

Frage: Wie kann ich eine CSV-Datei in MySQL importieren?

In MySQL gibt es die Anweisung LOAD DATA INFILE zum Importieren von Dateien im CSV-Format in die Datenbank. Diese Anweisung wird vom Datenbankserver ausgeführt, d.h. die Datei muss auf dem Rechner liegen, auf dem der Datenbankserver abläuft und die Datei muss world-readable sein. Man benötigt file_priv , um dieses Kommando ausführen zu können.

Seit Version 3.22.6 von MySQL gibt es die Kommandovariante LOAD DATA LOCAL INFILE zum Importieren von Daten im CSV-Format. Dieses Kommando wird auf dem MySQL-Client (also im PHP-Interpreter) ausgeführt. Die Datei muss also auf dem Rechner liegen, auf dem der Client läuft und durch den Client lesbar sein. Man benötigt keine besonderen Privilegien, um dieses Kommando ausführen zu können. Dies ist die empfohlene Variante des Kommandos, falls die zur Verfügung steht.

Das folgende SQL-Kommando liest eine Datei ein, bei der die Datensätze optional mit Anführungszeichen eingeschlossen sind und durch Semikolons getrennt sind. Vorhandene Datensätze in der Tabelle, die ebenfalls im Import enthalten sind, werden durch den Import überschrieben.

LOAD DATA LOCAL
        INFILE '/home/www/servers/www.servername.de/tmp/import.csv'
        REPLACE
        INTO TABLE tabellenname
        FIELDS
                TERMINATED BY ';'
                OPTIONALLY ENCLOSED BY '"'; 

Eine vollständige Beschreibung des Kommandos in englischer Sprache ist Bestandteil des MySQL Manuals unter der URL http://dev.mysql.com/doc/mysql/en/load-data.html .

Will man die Daten manuell laden, darf man die Zeile nicht mit explode() zerlegen, weil dies bei Datensätzen versagt, die selbst Kommata enthalten. Stattdessen bietet PHP die Funktion fgetcsv() an.

Frage: Wie kann ich eine CSV-Datei aus MySQL exportieren?

Die Umkehrung von LOAD DATA INFILE ist das SELECT INTO OUTFILE , eine Variante des regulären SELECT.

SELECT ...
    INTO OUTFILE '/home/www/servers/www.servername.de/tmp/export.csv'
    FIELDS
            TERMINATED BY ';'
            OPTIONALLY ENCLOSED BY '"'
    FROM ...; 

Das Kommando wird die Datei auf dem Rechner anlegen, auf dem der Datenbankserver läuft und die Datei wird dem Benutzer gehören, unter dessen User-ID der Datenbankserver abläuft. Der Datenbankserver wird eine existierende Datei nicht überschreiben. Zur Ausführung des Kommandos ist file_priv notwendig.

Eine vollständige Beschreibung des Kommandos in englischer Sprache gibt es im MySQL Manual .

Wenn man kein file_priv hat, muss man sich stattdessen eine entsprechende Funktion in PHP selber bauen. Dabei ist folgendes zu beachten:

• In CSV-Dateien sind Datensätze durch Kommata getrennt.

• In CSV-Dateien sind Datensätze, die Sonderzeichen enthalten, insbesondere solche, die Kommata oder Anführungszeichen enthalten, durch Anführungszeichen einzuschließen.

• In CSV-Dateien dürfen alle Datensätze in Anführungszeichen eingeschlossen werden.

• In CSV-Dateien sind in Datensätzen, die Anführungszeichen enthalten, die Anführungszeichen zu verdoppeln.

Eine zweispaltige Tabelle, die die Tupel ( a; 10,4) und (b; Er sagte: "Hallo, Du!" ) enthält, muss nach dem Export also so aussehen:

a,"10,4"
b,"Er sagte: ""Hallo, Du!""" 

Frage: Wie kann ich die Datensätze der letzten 2 Wochen listen?

Ein vollständiges Beispiel folgt. Zunächst die Definition einer einfachen Tabelle mit einigen Werten:

mysql> create table beispiel (
    ->   id integer not null auto_increment primary key,
    ->   nutzlast varchar(80) not null,
    ->   changed timestamp not null );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into beispiel ( nutzlast ) values ( "eins");
Query OK, 1 row affected (0.02 sec)

mysql> insert into beispiel ( nutzlast ) values ( "zwei");
Query OK, 1 row affected (0.00 sec)

mysql> insert into beispiel ( nutzlast ) values ( "drei");
Query OK, 1 row affected (0.00 sec)

mysql> select * from beispiel;
+----+----------+----------------+
| id | nutzlast | changed        |
+----+----------+----------------+
|  1 | eins     | 20000126204514 |
|  2 | zwei     | 20000126204517 |
|  3 | drei     | 20000126204520 |
+----+----------+----------------+
3 rows in set (0.02 sec)

mysql> update beispiel set changed = "20000101123456" where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from beispiel where changed > "20000115000000";
+----+----------+----------------+
| id | nutzlast | changed        |
+----+----------+----------------+
|  1 | eins     | 20000126204514 |
|  3 | drei     | 20000126204520 |
+----+----------+----------------+
2 rows in set (0.02 sec)

mysql> quit
Bye 

In diesem Beispiel ist eine Tabelle beispiel angelegt worden, die neben einer Nutzlastspalte mit dem Namen nutzlast auch noch einen automatisch vergebenen Primärschlüssel id und ein automatisch aktualisiertes Änderungsdatum enthält. Wir haben das Änderungsdatum für den Datensatz mit der Nummer 2 künstlich zurückgestellt.

Das folgende Beispielscript listet nun alle Datensätze, die in den letzten 2 Wochen geändert worden sind.

<?php

// Eigene Daten eintragen:
$dbhost = "localhost"; // DB-Server
$dbuser = "root";      // DB-Server Username
$dbpass = "...";       // DB-Server Passwort
$dbname = "...";       // Datenbank Name

// Mit DB-Server und Datenbank verbinden, bei Fehler Script beenden.
$db_link = @mysql_connect($dbhost, $dbuser, $dbpass);

if (! $db_link) {
    die("Fehler beim Verbinden mit Server '$dbhost' als '$dbuser' mit Passwort '$dbpass'.");
}

if (! mysql_select_db($dbname, $db_link)) {
    die("Fehler beim Verbinden mit der Datenbank $dbname");
}

// Zeitgrenze bestimmen (86400 Sekunden = 1 Tag).
$twoweeks = date("YmdHis", time() - 14*86400);

// Query generieren.
$query = "SELECT id,
                 nutzlast,
                 date_format(changed, '%Y-%m-%d %H:%i:%s') as changed
          FROM   beispiel
          WHERE  changed > $twoweeks";

$result = mysql_query($query, $db_link);

// Falls Query fehlerhaft, Script beenden.
if (! $result) {
    die("Fehler bei der Query: $query");
}

// Query senden, Resultat ausgeben.
while($avar = mysql_fetch_array($result)) 
{
    printf("%s - %s - %s\n",
            $avar['id'], $avar['nutzlast'], $avar['changed']);
}

// Speicher wieder freigeben und Verbindung zur DB schließen.
mysql_free_result($result);
mysql_close($db_link);

?>

Der Output des Scripts ist:

1 - eins - 2000-01-26 20:45:14
3 - drei - 2000-01-26 20:45:20 

Frage: Wie kann ich eine Tabelle nach IP-Nummern sortieren lassen?

Seit MySQL 3.23.15 gibt es die Funktionen INET_NTOA(expr) (number to address) und INET_ATON(expr) (address to number), die einem die Konvertierung abnehmen. Damit ist das Sortieren von IP-Adressen kein Problem mehr:

SELECT ip FROM table ORDER BY INET_ATON(ip); 

Frage: Wie lösche ich alle Datensätze, die älter als n Tage sind?

Die betreffende Tabelle sollte ein Datumsfeld haben, etwa ein selbstaktualisierendes Feld vom Typ TIMESTAMP oder ein manuell aktualisiertes Feld vom Typ DATE . Die folgenden drei Queries löschen jeweils alle Datensätze, die älter als 30 Tage sind, mit steigender Effizienz.

1. DELETE
       FROM kalender AS k
      WHERE (to_days(current_date) - to_days(k.datum)) > 30

2. DELETE
       FROM kalender AS k
      WHERE to_days(k.datum) < to_days(current_date)-30;

3. DELETE
       FROM kalender AS k
      WHERE k.datum < date_add(current_date, interval -30 day) 

Die erste Query ist vergleichsweise langsam, denn hier ist die linke Seite der Query ein Ausdruck, der für jede Zeile berechnet werden muss. Der Spaltenname k.datum taucht auf der linken Seite in einer Funktionsanwendung auf, sodass keine Indizes angewendet werden können.

Die zweite Query ist insofern optimiert, als dass der konstante Teil der Rechnung auf die rechte Zeit gebracht werden kann, sodass diese Seite der Ungleichung zu einer Konstanten optimiert werden kann. Die linke Seite der Query ist jedoch noch immer eine Funktionsanwendung, sodass ein full table scan notwendig ist.

Die dritte Query ist durchoptimiert: Hier ist die linke Seite der Ungleichung ein reiner Spaltenausdruck, die rechte Seite zu einer Konstanten optimierbar. Wenn ein INDEX(k.datum) existiert, kann er in dieser Query angewendet werden, um den Zugriff zu beschleunigen.

Frage: Wie kann ich Bilder in einer MySQL-Datenbank speichern?

Die Vor- und Nachteile dieser Methode werden im Kapitel "Datenbanken" im Abschnitt " db-blob " diskutiert.

Eine sehr gelungene Anleitung, um Binärdaten (also auch Bilder) in einer MySQL-Datenbank zu speichern, beschreibt Florian Dittmer auf http://www.phpbuilder.com/columns/florian19991014.php3 . In einem Artikel bei ZDnet beschreibt Julie Meloni einen Datei-Upload per Formular in die Datenbank inkl. Hinweisen speziell zu MySQL, Microsoft SQL Server und Oracle.

Frage: Wie kann ich einen zufälligen Eintrag aus einer MySQL-Tabelle auswählen?

Seit MySQL 3.23 steht folgende Syntax zur Verfügung, um einen zufälligen Eintrag aus einer Datenbank-Tabelle auszulesen:

SELECT * FROM tabelle ORDER BY RAND() LIMIT 1 

Frage: Ich habe eine Tabelle mit n Einträgen und möchte auf jeder Seite m davon anzeigen

In MySQL kann man zu diesem Zweck die LIMIT -Direktive verwenden, die m Einträge ab Position s einer geordneten Tabelle anzeigt. In anderen Datenbanken muss man sich eine Zeilennummer definieren und kann dann einen Teil der Tabelle mittels einer BETWEEN -Clause auswählen.

# MySQL
mysql> SELECT * FROM tabelle LIMIT s,m; 

Es ist nicht effizient, alle n Datensätze der Tabelle zu selektieren und dann alle Datensätze vor Position s zu überlesen.

Mit Hilfe eines solchen SQL-Statements kann man sich dann leicht eine Funktion schreiben, die den entsprechenden Ausschnitt der Tabelle anzeigt und Links zum vorhergehenden und folgenden Tabellenausschnitt enthält. Im Folgenden möchten wir hier diese einfache "Blättern"-Funktion realisieren, die uns erlaubt, über die Ergebnisse einer Datenbank-Query vor- und zurück zu browsen.

Es wird davon ausgegangen, dass eine einfache MySQL-Datenbank (deren Name über die Variable $database definiert wird) vorhanden ist. Diese enthält die von uns benötigte Tabelle (Variable $table ), über die wir "blättern" wollen.

CREATE DATABASE nameDerDatenbank;
USE nameDerDatenbank;
CREATE TABLE nameDerTabelle (ID int(10) unsigned NOT NULL,
                             INHALT text NOT NULL); 

Diese Datenbankstruktur kann z.B. mit phpMyAdmin , einem anderem PHP-Script oder direkt mit dem MySQL-Monitor erstellt werden. Um Ausgabeergebnisse zu erhalten, muss die Tabelle selbstverständlich zuerst mit Inhalt gefüllt werden - an dieser Stelle gehen wir davon aus, dass die Tabelle mehrere Einträge enthält.

<?php
// Daniel T. Gorski  dtg/240900/18:49/01
// Achtung: die Definition der $user- und $passwort-Variablen
// _sollte_ in einer externen Datei außerhalb des Document-Root
// festgelegt werden. Diese Datei muss dann an dieser Stelle
// [mit include() oder require()] importiert werden.
// Mehr dazu in dieser dclp-FAQ unter: "Wie kann ich mein
// Datenbankpasswort gegen Spionage sichern?"

// Datendefinition für Datenbankverbindung.
$host     = "localhost";  // MySQL - Zielrechner.
                          // Normallerweise ist es "localhost", bzw.
                          // synonym "127.0.0.1", also der Rechner,
                          // auf dem auch _dieses_ Script läuft.
$user     = "deinLogin";         // Dein Userlogin.
$password = "deinPasswort";      // Dein Datenbankpasswort.

$database = "nameDerDatenbank";  // Gewünschte Datenbank
                                 // innerhalb von MySQL
$table    = "nameDerTabelle";    // Der Name der Datenbanktabelle

// Datendefinition für die Clientausgabe
$start = (isset($start)) ? abs((int)$start) : 0;
$limit = 10;                     // Datensätze pro Ausgabeseite

// Verbindung zu MySQL-Datenbank herstellen oder sterben.
@mysql_connect($host,$user,$password)
   or die("Abbruch: Verbindung zu '$host'"
         ." konnte nicht hergestellt werden.");

// Benötigte Datenbank auswählen oder sterben.
@mysql_select_db($database)
   or die("Abbruch: Datenbank '$database' konnte nicht"
         ." selektiert werden.<br><br>MySQL sagt: ".mysql_error());

// Feststellen der Anzahl der verfügbaren Datensätze.
$resultID = @mysql_query("SELECT COUNT(ID) FROM ".$table);
$total    = @mysql_result($resultID,0);

// Ggf. $start korrigieren (falls Parameter in
// der URL manipuliert wurde)
$start    = ($start >= $total) ? $total - $limit : $start;

// Datenbankabfrage ausführen.
$query    = "SELECT ID,INHALT FROM ".$table
           ." LIMIT ".$start.",".$limit;
$resultID = @mysql_query($query);

// Ergebnisse lesen und an den Client ausgeben
while ($data = mysql_fetch_array($resultID))
{
  echo $data["ID"].": ".$data["INHALT"]."<br>";
}

// Zurück- und Vorblättern
if ($start > 0)
{
  $newStart = ($start - $limit < 0) ? 0 : ($start-$limit);
  echo "<a href="php/php-faq/static/.%24_SERVER%5B%27PHP_SELF%27%5D..html"?start=".$newStart
      .">&lt;&lt; zurück</a>";
}

if ($start + $limit < $total)
{
  $newStart = $start + $limit;
  echo " <a href="php/php-faq/static/.%24_SERVER%5B%27PHP_SELF%27%5D..html"?start=".$newStart
      .">vor &gt;&gt;</a>";
}

// Die benutzte (nichtpersistente) Verbindung zu der MySQL-Datenbank,
// wird nach dem Script-Ende automatisch geschlossen.
// That's it.
?> 

Arbeitet man mit dem DB-Abstraktionslayer aus dem PEAR , kann man komfortabel die Pakete DB_Pager und Pager_Sliding einsetzen.

Frage: Wozu ist auto_increment nützlich? Wie erfahre ich den Wert des letzten Inkrements?

Ganzzahlige Datenbankfelder in MySQL können mit dem Attribut auto_increment versehen werden. Wird über die betreffende Tabelle eine INSERT-Query ausgeführt, so wird automatisch der Wert des mit auto_increment gekennzeichneten Feldes um Eins erhöht (inkrementiert), ohne dass dieses in der INSERT-Query explizit angegeben werden muss bzw. darf. Dies ist bei "flachen" Tabellen ohne Relationen nützlich z.B. bei Gästebüchern.

Mit dem MySQL-Monitor erzeugtes Beispiel:

mysql> CREATE DATABASE foo;
mysql> USE foo;

mysql> CREATE TABLE bar (
    ->    ID int(10) unsigned NOT NULL auto_increment,
    ->    INHALT varchar(32) NOT NULL,
    ->    PRIMARY KEY (ID)
    -> );

mysql> DESCRIBE bar;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| ID     | int(10) unsigned |      | PRI | 0       | auto_increment |
| INHALT | varchar(32)      |      |     |         |                |
+--------+------------------+------+-----+---------+----------------+

mysql> INSERT bar SET INHALT='erster Datensatz';
mysql> INSERT bar SET INHALT='zweiter Datensatz';

mysql> SELECT * FROM bar;
+----+-------------------+
| ID | INHALT            |
+----+-------------------+
|  1 | erster Datensatz  |
|  2 | zweiter Datensatz |
+----+-------------------+
2 rows in set (0.00 sec) 

Wie man sehen kann, wird das Feld "ID" automatisch erhöht. Logischerweise darf nur ein Feld mit dem auto_increment Attribut versehen werden. Zusätzlich muss dieses Feld als Index definiert werden - z.B. als Primär-Schlüssel (PRIMARY KEY).

Um den Wert des letzten Inkrements erfahren, stellt PHP die Funktion mysql_insert_id() zur Verfügung:

<?php
// Es wird davon ausgegangen, dass $host, $user und
// $passwort korrekt initialisiert sind

// Verbindung zu MySQL-Datenbank herstellen oder sterben.
$linkID = mysql_connect($host,$user,$password)
   or die("Abbruch: Verbindung zu Host '$host' konnte"
         ." nicht hergestellt werden.");

// Benötigte Datenbank auswählen oder sterben.
@mysql_select_db("foo")
   or die("Abbruch: Datenbank '$database' konnte nicht"
         ." selektiert werden.<br><br>MySQL sagt: ".mysql_error());

// INSERT ausführen
@mysql_query("INSERT bar SET INHALT='dritter Datensatz'");

//  In unserem Beispiel ergibt das beim erstmaligen Aufruf "3"
//  dann "4", dann "5" etc.
echo mysql_insert_id($linkID);
?> 

Die Funktion mysql_insert_id() liefert nichts zurück, wenn vorher keine INSERT-Query ausgeführt wurde; sie liefert einen falschen Wert, wenn der Typ des auto-increment -Feldes als BIGINT definiert wird, für die meisten Anwendungen sollte aber der Typ INT UNSIGNED mehr als ausreichend sein.

Frage: Wie lege ich den Initialwert des auto_increment fest? Läuft dieser Wert über?

Üblicherweise benutzt man Werte ohne Vorzeichen ( UNSIGNED ) für den Typ des auto_increment -Feldes. Ab der MySQL Server-Version 3.23 ist es auch nicht mehr möglich, in diesen Feldern negative Zahlen zu führen.

Geeignete Datentypen für auto_increment wären:

• TINYINT UNSIGNED - Wertebereich 0 bis 255.

• SMALLINT UNSIGNED - Wertebereich 0 bis 65535.

• MEDIUMINT UNSIGNED - Wertebereich 0 bis 16777215.

• INT UNSIGNED - Wertebereich 0 bis 4294967295.

Für die meisten Anwendungen ist der Datentyp INT UNSIGNED mehr als ausreichend - immerhin ermöglicht dieser eine Adressierung von über vier Milliarden Datensätzen!

Der Initialwert des auto_increment -Feldes ist 1. Um diesen Wert zu ändern, muss man ihn explizit setzen:

mysql> DELETE FROM bar;

mysql> INSERT bar SET INHALT='erster Datensatz';

mysql> SELECT * FROM bar;
+----+------------------+
| ID | INHALT           |
+----+------------------+
|  1 | erster Datensatz |
+----+------------------+

mysql> INSERT bar SET ID='1000', INHALT='zweiter Datensatz';

mysql> SELECT * FROM bar;
+------+-------------------+
| ID   | INHALT            |
+------+-------------------+
|    1 | erster Datensatz  |
| 1000 | zweiter Datensatz |
+------+-------------------+

mysql> INSERT bar SET INHALT='dritter Datensatz';

mysql> SELECT * FROM bar;
+------+-------------------+
| ID   | INHALT            |
+------+-------------------+
|    1 | erster Datensatz  |
| 1000 | zweiter Datensatz |
| 1001 | dritter Datensatz |
+------+-------------------+ 

Der auto_increment -Wert läuft nicht über - d.h. er wird nicht wieder negativ (bzw. Null bei UNSIGNED ), wenn er über seinen Wertebereich hinaus adressiert wird. Stattdessen wird MySQL einen Fehler melden:

mysql> INSERT bar SET ID='4294967295', INHALT='letzter Datensatz';

mysql> SELECT * FROM bar;
+------------+-------------------+
| ID         | INHALT            |
+------------+-------------------+
|          1 | erster Datensatz  |
|       1000 | zweiter Datensatz |
|       1001 | dritter Datensatz |
| 4294967295 | letzter Datensatz |
+------------+-------------------+

mysql> INSERT bar SET INHALT='geht noch einer rein?';
ERROR 1062: Duplicate entry '4294967295' for key 1 

Frage: Wie realisiere ich eine Volltextsuche mit MySQL?

Um eine Volltextsuche für eine Website zu realisieren, eignen sich speziell dafür erstellte Tools besser. Siehe hierzu auch scripte-volltextsuche .

Liegen die Inhalte der Website in einer MySQL-Tabelle, so kann man jedoch auch MySQL zur Suche verwenden. Für den "Hausgebrauch" sollte das auf wenig belasteten Servern oft reichen. MySQL bietet hierzu ab der Version 3.23.23 die Möglichkeit, einen Volltextindex anzulegen.

Um die Spalte einer Tabelle mit einem solchen Index zu belegen muss das SQL-Statement ALTER TABLE tabellenname ADD FULLTEXT (textpalte) ausgeführt werden, welches einen entsprechenden Wortindex anlegt. Anschließend kann mit einer Query wie SELECT * FROM tabellenname WHERE MATCH textspalte1 AGAINST ('suchtext') der Index durchsucht werden. Dieser Wortindex reagiert nur auf ganze Worte, es kann also nicht nach Teilworten oder Wortkombinationen gesucht werden. Die Suche nach "Bauer" findet also nicht "Bauernhof".

Der Ausdruck MATCH a AGAINST b gibt einen Zahlenwert zurück, der die Relevanz des gefundenen Datensatzes wiedergibt, er kann also auch im SELECT -Teil eines SQL-Statements sinnvoll eingesetzt werden. Im ORDER BY -Teil des Statements braucht er nicht vorzukommen, denn MySQL sortiert automatisch nach Relevanz, wenn im WHERE -Teil der Volltextindex abgefragt wird.

SELECT   * FROM tabellenname
WHERE    MATCH textspalte AGAINST ('wort1 wort2') 

...gibt alle Datensätze aus, in denen eines der Suchworte in textspalte vorkommt - nach Relevanz absteigend sortiert.

Hat man eine MySQL-Version älter als 3.23.23, dann kann man auch eine Volltextsuche realisieren. Diese geht jedoch dann wesentlich langsamer vonstatten und belastet den Datenbankserver unverhältnismäßig stark, da MySQL hier nicht den Index benutzen kann.

SELECT   * FROM tabellenname
WHERE    textspalte LIKE '%wort1%'
OR       textspalte LIKE '%wort2%' 

Das Prozentzeichen hat im LIKE-Statement von SQL die Funktion, die man in anderen Situationen auch vom Sternchen (*) her kennt. Diese Query findet auch Teilwörter. Die Suche nach "Bauer" findet also auch "Bauernhof".

Ab Version 4.0.1 bietet MySQL für den Ausdruck MATCH a AGAINST b zusätzlich einen BOOLEAN MODE . Dadurch ist es möglich genauere Suchabfragen zu definieren und die Gewichtung einzelner Spalten beim Ranking der Treffer zu beeinflussen

SELECT felder FROM tabellenname
WHERE MATCH textspalte AGAINST 
('+Docu -PHP' IN BOOLEAN MODE) 

Eine Übersicht der zulässigen Operatoren und deren Wirkung findet sich in der MySQL-Dokumentation

Frage: Meine Datenbankabfrage/Mein SQL-Statement funktioniert nicht

Folgende Abschnitte der FAQ könnten für Dich interessant sein:

• sql-fehlerbehandlung (allgemein)

• mysql-betroffene-datensaetze

• fehler-mysql-result

Frage: Wie kann ich Umlaute richtig sortieren?

Gelegentlich steht man vor dem Problem, Tabelleinhalt mit Text richtig sortiert auslesen zu müssen, welcher auch Umlaute enthält. Das ist aber bei MySQL nicht so ohne weiteres möglich, da MySQL standardmäßig einen Zeichensatz unterstützt, der eine Sortierung mit Umlauten nicht nach unserem natürlichen Verständnis unterstützt. Eine bessere Beschreibung dieses Problems findet sich in der MySQL-FAQ der Newsgroup de.comp.datenbanken.mysql.

Es gibt 3 Lösungsmöglichkeiten:

• Wer Zugriff auf mysql-Einstellungen hat kann diese Möglichkeit anwenden: MySQL-FAQ

• Erzeugen einer zusätzlichen Sortier-Spalte in MySQL-Tabellen

• Direkt beim Auslesen der Daten eine Umlautformatierung und Sortierung vornehmen

Anhand einer Tabelle mit Namen sei die Lösung mit der zusätzlichen Sortier-Spalte hier kurz erläutert. Follgende Tabelle ist gegeben:

+----+------------+
| ID |    Name    |
+----+------------+
|  1 | Äußerung   |
|  2 | Österreich |
|  3 | Überfluss  |
+----+------------+ 

Diese Tabelle wird nun um eine Spalte erweitert, in der später die Namen stehen, nach denen sortiert werden kann. Ich nenne die Spalte einfach sortiert . Diese Spalte sollte die gleichen Eigenschaften haben, wie die Spalte Name .

ALTER TABLE tabelle ADD sortiert VARCHAR(255) NOT NULL 

Nun sieht unsere Tabelle so aus:

+----+------------+----------+
| ID |    Name    | sortiert |
+----+------------+----------+
|  1 | Äußerung   |          |
|  2 | Österreich |          |
|  3 | Überfluss  |          |
+----+------------+----------+ 

Die Umwandlung der Umlaute kann nun mit einer einzigen MySQL-Anweisung erfolgen:

mysql_query("UPDATE tabelle
              SET sortiert = REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
                             REPLACE(Name, 'Ä', 'A'), 'Ö', 'O'), 'Ü', 'U'),
                             'ä', 'a'), 'ö', 'o'), 'ü','u'), 'ß', 's')"); 

... Hierbei ist Name die entsprechende Tabellenspalte, aus der die umzuwandelnden Namen genommen werden. Es dürfen für den Spaltennamen keine Hochkommas verwendet werden!

Die fertige Tabelle sollte jetzt so aussehen:

+----+------------+------------+
| ID |    Name    |  sortiert  |
+----+------------+------------+
|  1 | Äußerung   | Ausserung  |
|  2 | Österreich | Osterreich |
|  3 | Überfluss  | Uberfluss  |
+----+------------+------------+ 

Richtig sortiert lässt sich das Ganze nun so auslesen:

$result = mysql_query("SELECT * FROM tabelle ORDER BY sortiert"); 

Anwendung in laufenden Scripten

mit MySQL:

$name = "irgend ein Name";
mysql_query("INSERT INTO tabelle (Name, sortiert)
             VALUES('$name', REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
                             REPLACE(Name, 'Ä', 'A'), 'Ö', 'O'), 'Ü', 'U'),
                             'ä', 'a'), 'ö', 'o'), 'ü','u'), 'ß', 's'))"); 

oder mit PHP:

$name = "irgend ein Name";
$sortiert = strtr($name, "ÄÖÜäöüß", "AOUaous");
mysql_query("INSERT INTO tabelle (Name, sortiert) VALUES('$name','$sortiert')"); 

Das Sortieren nach einer bestimmten Spalte kann auch während einer Abfrage stattfinden:

mysql_query("SELECT *, REPLACE( REPLACE( REPLACE( REPLACE( REPLACE( REPLACE(
                       REPLACE(Name, 'Ä', 'A'), 'Ö', 'O'), 'Ü', 'U'),
                       'ä', 'a'), 'ö', 'o'), 'ü','u'), 'ß', 's') AS sortiert
             FROM tabelle ORDER BY sortiert"); 

Zu bedenken ist dabei allerdings, dass die Serverlast eine nicht zu unterschätzende Rolle spielen kann. Wenn es zuviele gleichzeitige Zugriffe auf den Server gibt bzw. wenn es zuviele zu bearbeitende Datensätze gibt, kann die Performance empfindlich darunter leiden.

Frage: Wie kann ich mehrere Verbindungen zu MySQL öffnen?

Manchmal ist es sinnvoll, gleichzeitig mehr als eine Verbindung zum Datenbankserver offen zu haben (z.B. wenn man mit mehr als einer Datenbank arbeitet). Der Befehl mysql_connect() hat leider den Mangel, dass er bei wiederholtem Aufruf mit identischem Hostnamen, Benutzernamen und Kennwort immer dieselbe Verbindungskennung liefert. Anstatt für diesen Zweck verschiedene Benutzer anzulegen, kann man ihn jedoch auch mit unterschiedlichen Hostnamen austricksen - einmal mit localhost und einmal mit 127.0.0.1 aufgerufen, liefert er bereits zwei unterschiedliche Links...

Ab PHP Version 4.2 besitzt mysql_connect() einen optionalen Parameter, mit dem man eine neue Verbindung erzwingen kann.

Frage: Wie kann ich feststellen wie viele Datensätze von meiner Abfrage betroffen sind / gefunden wurden?

Mit den Funktionen mysql_num_rows() bzw. mysql_affected_rows() kann man die Anzahl der gefundenen Datensätze bei einem SELECT-Statement bzw. die Anzahl der betroffenen Datensätze bei einem UPDATE-/INSERT-Statement überprüfen.

// Beispiel für ein Select-Statement
$query = "SELECT * FROM table WHERE x = '$x'";
$result = @mysql_query($query);

if(!$result)
{
    echo "Fehler: " . mysql_error();
}
else
{
    echo mysql_num_rows() . " Datensätze gefunden.<br />\n";
}

// Beispiel für ein UPDATE-Statement
$query = "UPDATE table SET a = '$a' WHERE x = '$x'";
$result = @mysql_query($query);

if(!$result)
{
    echo "Fehler: " . mysql_error();
}
else
{
    echo mysql_affected_rows() . " Datensätze geändert.<br />\n";
}