Mit ‘Datenbanksysteme/MySQL’ getaggte Artikel

Fortlaufende Nummer/Zahlen in einer MySQL Abfrage erzeugen

Donnerstag, 21. Oktober 2010

Fortlaufende Nummer in Dokumenten werden oft gebraucht, insbesondere in Buchhaltungsunterlagen, wo strengente fortlaufende Zahlen benötigt werden und keine IDs. Die IDs sind zwar in der Regel nie gleich, jedoch um Fremdschlüsselkonflikte zu vermeiden, werden sie nur einmal vergeben und wenn eine ID dazwischen wegspringt, so wird die freie Position nicht mehr belegt. Deswegen benötigen wir eine eindeutige Durchnummerierung, die von eins bis zur Anzahl der Zeilen in der MySQL Abfrage weitergeht.

Fortlaufende Nummer in MySQL Abfragen werden vor allem gebraucht wenn wir keine Möglichkeit haben die Daten zu manipulieren, nachdem die Abfrage ausgeführt wird, zum Beispiel bei einem universellen CSV Export. Dieser Artikel beschäftigt sich mit der Programmierung und Anpassung von einer MySQL Abfrage, die unabhängig von der Struktur eine zusätzliche Spalte für die fortlaufenden Nummer enthält.

MySQL Abfrage – fortlaufende Nummer automatisch erzeugen lassen

Gegeben ist eine MySQL Tabelle mit gemischten IDs (Spaltenname ist “id”) und einen Inhalt, den wir durchnummerieren lassen wollen:

SELECT
COUNT(tabelle2.id)+1 as Position,
tabelle1.sText as Inhalt
FROM MeineTabelle as tabelle1 LEFT JOIN MeineTabelle as tabelle2
ON tabelle1.id>tabelle2.id
GROUP BY tabelle.id
ORDER BY t1.id ASC

Was genau macht diese MySQL Abfrage zur Generierung von fortlaufenden Nummern?

  1. Die Tabelle wird mit sich selbst verknüpft, bei der Bedingung, dass beim Überprüfen jeder Zeile vom Join, der Abstand des gerade betrachteten IDs zu der ersten Zeile der Tabelle  berechnet und gezählt wird.
  2. Der Teil der Abfrage COUNT(tabelle2.id)+1 as Position erzeugt die Spalte Position, die die fortlaufenden Nummer enthält, wobei der Abstand im Punkt 1 gezählt wird. Die Addierung der eins ist notwendig, damit die erste fortlaufende Nummer die 1 und nicht die 0 ist. Damit Count ohne Sternchen funktionieren darf, müssen wir zwingend den GROUP BY Klausel aufführen
  3. Es wird in der ersten Tabelle nach der Spalte sortiert, die unsere Bedingung für den LEFT JOIN erfüllt, ohne diese Sortierung wird die Abfrage nicht funktionieren.

Und, es funktioniert!

Benötigen Sie Hilfe bei der Programmierung Ihrer Abfragen? Nehmen Sie gerne mit uns Kontakt auf – wir helfen Ihnen weiter.

Tags:, ,
Abgelegt in MySQL Tipps | Keine Kommentare »

MySQL SELECT/WHERE mit Explode PHP Character für kommagetrennte Felder

Freitag, 11. Juni 2010

Es kommt oft vor, dass wir nicht normalisierte Tabellen in MySQL mit Feldern, die Kommagetrennte Werte enthalten, ähnlich wie per JOIN abfragen möchten. Das ist leider ohne weiteres nicht möglich, da aufgrund der 1-ten Normalisierung jeder Wert in einer eigenen Zeile geschrieben werden soll, damit der JOIN funktionieren kann. Stellen Sie sich vor Sie haben eine alte und große Tabelle, die Felder in folgender Form enthält:

Spaltenname: SelectedIDs
Werte: 1,5,23,333,2315

Das erste, was man machen würde um die Werte abzufragen, wäre eine Abfrage mit WHERE … IN (wert1, wert4):

SELECT Name FROM Tabelle WHERE MeineID IN (SelectedIDs)

Das funktioniert leider nicht, da die Kommagetrennte Werte in dieser Form nicht an IN(…) übergeben werden können, im Gegensatz zu einem Array aus ein SELECT oder eine wirklich reingeschriebene Zeichenkette – 1,24,54 usw. Die PHP Funktion EXPLODE(TRENNZEICHEN, ZEICHENKETTE) generiert ein Array, der so ähnlich von MySQL generiert werden könnte, um an die WHERE … IN (AUS_EXPLODE_ARRAY) übergeben werden kann. MySQL unterstützt leider kein EXPLODE, reguläre Ausdrücke aber schon. Das wird nämlich unsere Programmierlösung für die Kommagetrennten Werte, die wir abfragen möchten.

Lösung

Es ist auch nicht immer möglich die Tabelle zu normalisieren, deswegen gibts es eine Erleichterung der Programmierung an der Stelle. Betrachten wir folgende zwei Tabellen:

Tabelle1
—————-
Name - Michael
MeineID – 2

Tabelle2
—————–
KlausurName – Englisch
SchulerIDs - 1,2,5,6

Wir haben hier den Michael, der die Klausur Englisch, zusammen mit vier anderen Schülern geschrieben hat. Wie bekommen wir ein JOIN auf die Zwei Tabellen? Folgende Abfrage kümmert sich mit einem regulären Ausdruck darum:

SELECT Name, MeineID, (SELECT KlausurName FROM Tabelle2 WHERE Tabelle1.MeineID REGEXP REPLACE(SchulerIDs, ‘,’, ‘|’)) as GeschriebeKlausur FROM Tabelle1

An der Stelle kommt der reguläre Ausdruck ins Spiel – WHERE MeinWert REGEXP (Wert1|Wert3|Wert4) gibt Wahr zurück, wenn einer der mit der Pipeline getrennten Werte mit MeinWert übereinstimmt. Mit der Funktion REPLACE wird die kommagetrennte Zeichenkette für den regulären Ausdruck aufbereitet – die Kommas werden durch ein Pipeline ersetzt. Das bedeutet, dass Ihre Werte beliebig getrennt werden können, Sie müssten einfach den zweiten Parameter der REPLACE Funktion berücksichtigen.

Somit macht MySQL einfach Spass!

Tags:, , ,
Abgelegt in MySQL Tipps | 4 Kommentare »

MySQL – Einfügen bei vorhandenen Datensätzen / INSERT IGNORE

Dienstag, 25. Mai 2010

Heute werden wir die Schwierigkeiten beim Einfügen von Datensätzen via MySQL betrachten. Der bekannte Befehl “INSERT INTO” kann oft fehlschlagen aufgrund von bereits mit der einzufügenden ID vorhandenen Datensätzen. Es gibt drei Wege dies zu vermeiden, beziehungsweise solche Fälle direkt mit MySQL abzufangen um automatisierte INSERTs nicht abbrechen zu lassen. Diese Beispiele sind bei der Programmierung besonders nützlich, wenn wir nicht in der Lage sind, vor jedem Einfügen mit PHP oder ähnliches zu überprüfen, ob der Datensatz bereits existiert. Das spart insgesamt bei der MySQL Befehlen Zeit.

Es gibt drei Wege die MySQL Queries so auszuführen, so dass man sich nicht mehr Gedanken machen muss, ob Fehler bei dem Einfügen auftreten:

MySQL: REPLACE INTO

Wenn Sie eine Abfrage dieser Art ausführen,

REPLACE INTO table SET id=3454,a='1', b='2'

wird ein vorhandener Datensatz überschrieben. Das ist nicht besonders gut, wenn wir die Daten einfach behalten wollen und die Abfrage den vorhandenen Datensatz ignorieren lassen wollen. Hiermit verlieren Sie die Daten, die Sie schon mal in der Datenbank hatten.

MySQL: INSERT IGNORE

Einfacher ist es mit INSERT IGNORE:

INSERT IGNORE INTO table SET id=3454,a='1', b='2'

Sollte hier ein Datensatz mit dieser ID existieren, so wird die Ausführung der Abfrage einfach ignoriert und überschritten (d.h. es geht dann zu der nächsten Abfrage). Wenn der Datensatz nicht vorliegt, wird ein neuer Datensatz angelegt. Das funktioniert ganz gut in den meisten Fällen. Jedoch können hier weitere MySQL Fehler auftreten, die sich nicht auf die bereits vorhandene ID beziehen, die die Ausführung der Abfrage trotzdem nicht verhindern werden. Es kann sein, dass man bei solchen Fehlern die Ausführung abbrechen lassen möchte. Deswegen gibt es die elegantere Lösung:

MySQL: INSERT … ON DUPLICATE KEY UPDATE …

INSERT INTO table SET id=3454,a='1', b='2' ON DUPLICATE KEY UPDATE ....

Bei diesem Fall wird die Abfrage nur ignoriert, wenn ein vorhandener Datensatz vorliegt. Alle weiteren Fehler, die auftreten könnten, werden sämtliche Abfragen abbrechen lassen. Der ”….” Abschnitt sollte ein MySQL Befehl enthalten, der keine Auswirkung auf die Daten haben wird, wie etwa UPDATE id=id.

Tags:, , ,
Abgelegt in MySQL Tipps | Keine Kommentare »