concat(...) AS neuer_feldname nicht im select-Teil?

[Felix Riesterer]

Liebe DB-Experten,

ich möchte mir aus zwei Feldern ein neues basteln, um einen Stringvergleich auf die Inhalte beider Felder anzuwenden. MySQL5 meckert, dass der Feldname 'Volltext' in der where-Klausel nicht existiere:

SELECT  
    `URL`,  
    `Titel`,  
    `Text`,  
    CONCAT (`Titel`, ' ', `Text`) AS `Volltext`  
FROM  
    `seiten`  
WHERE  
    `Volltext` REGEXP 'grün'

Was habe ich hier missverstanden?

Liebe Grüße,

Felix Riesterer.

[Matthias Apsel]

Om nah hoo pez nyeetz, Felix Riesterer!

Liebe DB-Experten,

bin zwar keiner, aber

Volltext

schon 'seiten.Volltext' probiert?

Matthias

[Felix Riesterer]

Lieber Matthias Apsel,

schon 'seiten.Volltext' probiert?

das würde doch bedeuten, dass meine Tabelle tatsächlich ein solches Feld hätte, oder nicht? "Volltext" soll ein in der Abfrage neu erzeugtes Feld sein, das in der Tabelle "nativ" nicht vorkommt.

Das Problem ist, dass ich mich in der WHERE-Klausel nicht darauf beziehen kann/darf, obwohl ich den Wert dieses Feldes benötige. Mir ist nicht klar, warum.

Was als (weniger schöne) Alternative geht, ist das jeweilige Notieren der betroffenen, in der Tabelle tatsächlich vorhandenen Felder:

SELECT  
    `URL`,  
    `Titel`,  
    `Text`  
FROM  
    `seiten`  
WHERE  
    `Titel` REGEXP 'grün' OR  
    `Text` REGEXP 'grün'

Da ich aber mehr als nur "grün" testen möchte, wird das eine recht aufwendige Schreiberei, die ich mit dem durch CONCAT erzeugten Feld vereinfachen wollte:

SELECT  
    `URL`,  
    `Titel`,  
    `Text`  
FROM  
    `seiten`  
WHERE  
    (`Titel` REGEXP 'grün' OR  
    `Text` REGEXP 'grün')  
    AND  
    (`Titel` REGEXP 'blau' OR  
    `Text` REGEXP 'blau')  
    AND  
    (`Titel` REGEXP 'rot' OR  
    `Text` REGEXP 'rot')

Mit "Volltext" hätte das zumindest so ausgesehen:

SELECT  
    `URL`,  
    `Titel`,  
    `Text`,  
    CONCAT (`Titel`, ' ', `Text`) AS `Volltext`  
FROM  
    `seiten`  
WHERE  
    `Volltext` REGEXP 'grün'  
    AND  
    `Volltext` REGEXP 'blau'  
    AND  
    `Volltext` REGEXP 'rot'

Warum nur kann ich ein in der SELECT-Klausel frei definiertes Feld nicht in der WHERE-Klausel prüfen?

Liebe Grüße,

Felix Riesterer.

[MudGuard]

Hi,

ich möchte mir aus zwei Feldern ein neues basteln, um einen Stringvergleich auf die Inhalte beider Felder anzuwenden. MySQL5 meckert, dass der Feldname 'Volltext' in der where-Klausel nicht existiere:

Richtig, das liegt an der Auswertungsreihenfolge.

FROM, WHERE, GROUP BY, SELECT (1), HAVING, ORDER BY, SELECT (2)

Zu dem Zeitpunkt, zu dem WHERE ausgewertet wird, ist der ALIAS für die zusammengesetzte Spalte noch gar nicht bekannt, da SELECT erst nach dem WHERE ausgewertet wird.

Du müßtest also bei WHERE auch nochmal den CONCAT angeben.

Select wird 2mal ausgewertet, da für HAVING die having-Werte bekannt sein müssen (im HAVING können die Aliase benutzt werden).

cu,
Andreas

[Felix Riesterer]

Lieber MudGuard,

Richtig, das liegt an der Auswertungsreihenfolge.

FROM, WHERE, GROUP BY, SELECT (1), HAVING, ORDER BY, SELECT (2)

vielen Dank für diese Info!

Zu dem Zeitpunkt, zu dem WHERE ausgewertet wird, ist der ALIAS für die zusammengesetzte Spalte noch gar nicht bekannt, da SELECT erst nach dem WHERE ausgewertet wird.

Das bedeutet, dass wenn ich von den Feldnamen abweichende Aliase benutze, dann sind die auch noch nicht bekannt. Habe das gerade festgestellt:

SELECT  
    `my_table`.`HTML` AS `Text`  
WHERE  
    `Text` LIKE '%id="besonders"%' -- "Text" unbekannt

Das ist sehr ungeschickt... Die Aliase hatten den Zweck, später in der Ergebnismenge dieselben Indices zu tragen. Da kann ich mir das mit den Aliasen in meinem Fall ja komplett sparen! *grrr*

Liebe Grüße,

Felix Riesterer.

[dedlfix]

Hi!

Das bedeutet, dass wenn ich von den Feldnamen abweichende Aliase benutze, dann sind die [im WHERE] auch noch nicht bekannt. [...]
Das ist sehr ungeschickt...

Es geht einfach nicht sinnvoll anders. Willst du zuerst die Ergebnisspalten der gesamten Tabelle (zuzüglich Joins) berechnen und dann erst mit WHERE einschränken oder willst du lieber zuerst einschränken und auf diese eingedampfte Menge die Ergebnisspalten berechnen?

Die Aliase hatten den Zweck, später in der Ergebnismenge dieselben Indices zu tragen. Da kann ich mir das mit den Aliasen in meinem Fall ja komplett sparen! *grrr*

Nö, denn der Alias benennt ja auch die Spalte in der Ergebnismenge. Ansonsten wäre ihr Namen die Formel. Oder du kannst nur nach der Nummer in der Reihenfolge der Abfrage gehen, was aber bei späteren Änderungen eventuell ein Neunummerieren erfordert.

Lo!

[dedlfix]

Hi!

FROM, WHERE, GROUP BY, SELECT (1), HAVING, ORDER BY, SELECT (2)
Select wird 2mal ausgewertet, da für HAVING die having-Werte bekannt sein müssen (im HAVING können die Aliase benutzt werden).

Im GROUP BY und ORDER BY können auch Aliase verwendet werden. Für diese beiden Klauseln kann sogar die Nummer der Spalte (wie in SELECT angegeben) genommen werden. Die Nummer muss man nur später anpassen, wenn sich die Reihenfolge verändert.

Bei der Ausführungsreihenfolge würde ich eher wie folgt plädieren:

FROM, WHERE, SELECT (1), GROUP BY, SELECT (2), HAVING, ORDER BY, LIMIT

(Die nachfolgende Ausführung ist meine Interpretation, auch wenn ich wie ein Fakt formuliere.) Das erste Select liefert mindestens die Aliasnamen für die Gruppierung, das zweite berechnet die Aggregatfunktionen. In einem der beiden findet auch die Berechnung der anderen Spalten statt, was aber nur für MySQL gilt, denn andere DBMSe gestatten nur gruppierte Spalten und Aggregatfunktionen. Kommt keine GROUP-BY-Klausel zum Einsatz, braucht es nur ein SELECT. ORDER BY muss sich auf die Select-Ergebnisse (nicht nur die Aliase) beziehen können, deswegen die Anordnung nach den SELECTs. Zum HAVING schreibt das MySQL-Handbuch: "The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization. (LIMIT is applied after HAVING.)" Demnach müsste es eigentlich nach dem ORDER BY kommen, aber wenn die Ergebnismenge noch eingeschränkt wird, halte ich es eher für sinnvoll, die reduzierte Menge zu sortieren.

Lo!

[MudGuard]

Hi,

FROM, WHERE, GROUP BY, SELECT (1), HAVING, ORDER BY, SELECT (2)
Select wird 2mal ausgewertet, da für HAVING die having-Werte bekannt sein müssen (im HAVING können die Aliase benutzt werden).

Im GROUP BY und ORDER BY können auch Aliase verwendet werden.

Ich wußte, daß das SELECT erst nach dem WHERE ausgewertet wird. Hab daher kurz nach SQL Auswertungsreihenfolge gegoogelt und die oben genannte Reihenfolge gefunden. Da sie meiner Annahme nicht widersprach, hab ich sie ohne weitere Prüfung übernommen ...

cu,
Andreas

[dedlfix]

Hi!

FROM, WHERE, GROUP BY, SELECT (1), HAVING, ORDER BY, SELECT (2)
Ich wußte, daß das SELECT erst nach dem WHERE ausgewertet wird. Hab daher kurz nach SQL Auswertungsreihenfolge gegoogelt und die oben genannte Reihenfolge gefunden. Da sie meiner Annahme nicht widersprach, hab ich sie ohne weitere Prüfung übernommen ...

Ich finde nur die Eselsbrücke zu der von dir zitierten Reihenfolge, aber da steht nie dabei, warum das SELECT da zweimal auftaucht. Genauer gesagt steht nie eine Erklärung dabei und eine solche finde ich auch anderswo nicht. Wer kann besser googlen?

P.S.: Mir ist klar, dass die Reihenfolge nur ein Richtwert sein kann und wohl vorwiegend beim Statement-Parser interessiert, denn der Optimierer wird einige Dinge anders ausführen, vor allem wenn Indexe verwendet werden können.

Lo!

[Felix Riesterer]

Hallo Ingrid,

da ich ein RegEx auf zwei Felder anwenden möchte, brauche ich concat(). Da ich in der where-Klausel das Ergebnis nicht per Alias aus dem select-Statement referenzieren kann (siehe Zeitlichkeit bei der Abarbeitung der Klauseln bei MySQL), muss ich es für jeden String, gegen den ich "regexen" will, neu als concat()-Aufruf formulieren:

SELECT  
    `URL`,  
    `Titel`,  
    `Text`,  
FROM  
    `seiten`  
WHERE  
    CONCAT (`Titel`, ' ', `Text`) REGEXP 'grün' AND  
    CONCAT (`Titel`, ' ', `Text`) REGEXP 'blau' AND  
    CONCAT (`Titel`, ' ', `Text`) REGEXP 'rot'

Damit werde ich leben können.

Vielen Dank an MudGuard und dedlfix für die notwendigen und klärenden Hinweise!

Liebe Grüße,

Felix Riesterer.

[MudGuard]

Hi,

CONCAT (Titel, ' ', Text) REGEXP 'grün' AND
    CONCAT (Titel, ' ', Text) REGEXP 'blau' AND
    CONCAT (Titel, ' ', Text) REGEXP 'rot'[/code]

Die Suche nach konstanten Strings erfordert kein Regex.
Und hier kann die Suche auch auf die Einzelteile angewandt werden, da das Leerzeichen zwischen den Spalteninhalten weder bei 'rot' noch bei 'blau' noch bei 'grün' gematcht werden kann, der Treffer also nur jeweils innerhalb der Spaltentexte auftreten kann.

cu,
Andreas

[Felix Riesterer]

Lieber MudGuard,

Die Suche nach konstanten Strings erfordert kein Regex.

es handelt sich um Benutzereingaben. Deswegen sind es keine "konstanten Strings".

Und hier kann die Suche auch auf die Einzelteile angewandt werden, da das Leerzeichen zwischen den Spalteninhalten weder bei 'rot' noch bei 'blau' noch bei 'grün' gematcht werden kann, der Treffer also nur jeweils innerhalb der Spaltentexte auftreten kann.

aber dazu benötige ich noch mehr Notationsarbeit. Oder wie würdest Du meinen SQL-Code umschreiben, damit er in den einzelnen Feldern sucht, ohne zu konkatenieren?

Liebe Grüße,

Felix Riesterer.

[Sven Rautenberg]

Moin!

Die Suche nach konstanten Strings erfordert kein Regex.

es handelt sich um Benutzereingaben. Deswegen sind es keine "konstanten Strings".

Und du kriegst das notwendige Regex-Escaping hin?

- Sven Rautenberg

[Felix Riesterer]

Lieber Sven Rautenberg,

Die Suche nach konstanten Strings erfordert kein Regex.
Und du kriegst das notwendige Regex-Escaping hin?

jetzt glaube ich verstanden zu haben, was mit den "konstanten" Strings gemeint war. Danke für die Klarstellung. Somit sind die Benutzereingaben im SQL-Code "konstante Strings", z.B. im Gegensatz zum Wert eines Datensatzfeldes. Habe kapiert.

Was ich aber nicht kapiert habe ist, warum ich kein RegExp brauchen sollte. Wenn "konstante Strings" kein RegExp erfordern, wozu existiert diese Funktion dann, insbesondere unter dem Aspekt des Escaping bei "nicht-konstanten" Strings?

Liebe Grüße,

Felix Riesterer.

[Sven Rautenberg]

Moin!

Die Suche nach konstanten Strings erfordert kein Regex.
Und du kriegst das notwendige Regex-Escaping hin?

jetzt glaube ich verstanden zu haben, was mit den "konstanten" Strings gemeint war. Danke für die Klarstellung. Somit sind die Benutzereingaben im SQL-Code "konstante Strings", z.B. im Gegensatz zum Wert eines Datensatzfeldes. Habe kapiert.

Klingt noch nicht so überzeugend.

Was ich aber nicht kapiert habe ist, warum ich kein RegExp brauchen sollte. Wenn "konstante Strings" kein RegExp erfordern, wozu existiert diese Funktion dann, insbesondere unter dem Aspekt des Escaping bei "nicht-konstanten" Strings?

Wenn du feststellen willst, ob in einem Textfeld der Datenbank ein bestimmter String (z.B. die Zeichenfolge "grün") irgendwo enthalten ist, nimm LIKE:

WHERE feld LIKE "%grün%"

Das funktioniert performanter, als dasselbe mit REGEXP zu machen.

Wenn das, was gesucht werden soll, dynamisch vom Nutzer eingegeben werden kann, muss kontextabhängig escaped werden.

Und REGEXP sind dann sinnvoll, wenn das zu Suchende mit Regeln formuliert werden muss: Suche alles, wo erst drei Buchstaben "A-Z", dann zwei Ziffern "0-9" und dann nochmal dieselben Buchstaben von vorne drin sind. Sowas kann man nicht als schlichten Suchstring eingeben, denn "AAA11AAA" oder "ZYX81ZYX" wären Treffer.

- Sven Rautenberg

[Felix Riesterer]

Lieber Sven Rautenberg,

bitte korrigiere mich, wenn ich falsch liegen sollte, aber wenn ich folgenden String im Datenfeld habe, gegen den meine Benutzereingabe gematched werden soll, ist das mit Deinem Vorschlag so lösbar?

// Ein Datensatz enthält in seinem text-Feld: "Grüner geht es nimmer.";  
$benutzereingabe = $db->escape($_GET['q']); // "grün"  
$rs = $db->query("SELECT * FROM `tabelle` WHERE `text` LIKE '%$benutzereingabe%'");

Wenn jetzt jemand tatsächlich "grün" eingegeben haben sollte, dann findet LIKE '%grün%' nur etwas, wenn vor und nach dem "grün" noch Zeichen stehen. Oder nicht? Mein Beispieltext wird also nicht gefunden, da ich dafür LIKE 'grün%' ohne erstes Prozentzeichen notieren müsste, oder nicht? So zumindest hatte ich LIKE verstanden.

Liebe Grüße,

Felix Riesterer.

[Sven Rautenberg]

Moin!

bitte korrigiere mich, wenn ich falsch liegen sollte, aber wenn ich folgenden String im Datenfeld habe, gegen den meine Benutzereingabe gematched werden soll, ist das mit Deinem Vorschlag so lösbar?

// Ein Datensatz enthält in seinem text-Feld: "Grüner geht es nimmer.";

$benutzereingabe = $db->escape($_GET['q']); // "grün"
$rs = $db->query("SELECT * FROM tabelle WHERE text LIKE '%$benutzereingabe%'");

>   
> Wenn jetzt jemand tatsächlich "grün" eingegeben haben sollte, dann findet `LIKE '%grün%'`{:.language-sql} nur etwas, wenn vor und nach dem "grün" noch Zeichen stehen. Oder nicht? Mein Beispieltext wird also nicht gefunden, da ich dafür `LIKE 'grün%'`{:.language-sql} ohne erstes Prozentzeichen notieren müsste, oder nicht? So zumindest hatte ich [LIKE](http://dev.mysql.com/doc/refman/5.1/de/string-comparison-functions.html) verstanden.  
  
Zitat: "%	entspricht einer beliebigen Anzahl von Zeichen (einschließlich null Zeichen)."  
  
Dein Escaping ist aber falsch. Wenn der Nutzer ein "%" eingibt, willst du das Prozentzeichen suchen, nicht das Prozentzeichen als Platzhalter für "beliebig viele Zeichen" nutzen. Und der Unterstrich "\_" ist ebenfalls ein Sonderzeichen, dass escaped gehört, wenn du den String als LIKE-Parameter verwenden willst, und die Sonderbedeutung nicht wirken soll.  
  
Dasselbe Problem hast du, wenn du REGEXP verwendest. Alle vom User eingegebenen Zeichen, die in regulären Ausdrücken Sonderbedeutung haben, müssen escaped werden, damit sie als "das Zeichen" wirken. Und bei REGEXP hast du noch viel mehr Zeichen zu beachten, als bei LIKE.  
  
 - Sven Rautenberg

[dedlfix]

Hi!

$benutzereingabe = $db->escape($_GET['q']); // "grün"
Dein Escaping ist aber falsch. Wenn der Nutzer ein "%" eingibt, [...] Und der Unterstrich "_" [...]

Zur Klarstellung: "falsch" im Sinne von "ergänzungswürdig". Das jetzige hilft "nur" die beabsichtigte Syntax des Statements im Allgemeinen zu sichern (sprich SQL-Injection zu verhindern). noch zwei weitere Zeichen zu beachten, die Sven ja schon erwähnt hat.

Lo!

[Felix Riesterer]

Lieber dedlfix,
lieber Sven Rautenberg

zuerst einmal ein "fachlich hilfreich" für eure Antworten! Und dann noch ein ganz herzliches Dankeschön obendrauf. Heute habe ich wieder etwas Wesentliches dazugelernt. Nicht nur, dass RegExp für meinen Anwendungsfall die ungünstigere Wahl ist (wegen des komplexeren Escapings und geringerer Performance), sondern auch, dass ich hier "konstante Strings" verwende, anstatt "echte Muster".

Liebe Grüße,

Felix Riesterer.

[Matti Mäkitalo]

Hi,

SELECT

URL,
    Titel,
    Text,
FROM
    seiten
WHERE
    CONCAT (Titel, ' ', Text) REGEXP 'grün' AND
    CONCAT (Titel, ' ', Text) REGEXP 'blau' AND
    CONCAT (Titel, ' ', Text) REGEXP 'rot'

  
zumindest bei Oracle war CONCAT im WHERE-Teil furchtbar langsam, und MySQL kann auf jedenfall keinen Index auf Titel und Text nutzen.  
  
Vielleicht überlegst du dir, ob du diesen Regexp nicht vielleicht umgehen kannst.  
  
Bis die Tage,  
Matti

-- 
[Webapplikationen in C++ entwickeln](http://tntnet.org/)

[Felix Riesterer]

Lieber Matti Mäkitalo,

zumindest bei Oracle war CONCAT im WHERE-Teil furchtbar langsam, und MySQL kann auf jedenfall keinen Index auf Titel und Text nutzen.

das will ich alles nicht. Indices oder gar einen Volltext-Suche-Index benötige ich nicht. Das Projekt ist so klein und überschaubar, dass diese Performance-Löcher nicht ins Gewicht fallen. Ich möchte eben eine Volltext-Suche eines Webprojektes ermöglichen, bei der in verschiedenen Tabellen (im HTML-Code) gefunden werden soll. Da ich sowohl '^grün', als auch 'grün$' und '.*grün.*' (wie z.B. in "Grünschnitt" und "Blattgrün", aber auch in "Grün, grün, grün sind alle meine Kleider") finden möchte, kann ich kein LIKE-Statement benutzen, obwohl mir die Groß-/Kleinschreibung egal ist (collation "utf8_general_ci"). Oder findet MySQL dann ebenso alle möglichen Kombinationen?

Vielleicht überlegst du dir, ob du diesen Regexp nicht vielleicht umgehen kannst.

Mir ist die technische Lösung mittels LIKE genauso willkommen, wie die mit RegExp, wenn sie denn dieselben Treffermengen produziert. Aber nach meinen Recherchen bei MySQL im Handbuch hatte ich den Eindruck, dass das wohl nicht so einfach ist. Ich müsste wohl noch mehr notieren, als in meinem RegExp-Statement. Daher hatte ich ja den Weg mit RegExp gewählt!

Was rätst Du mir?

Liebe Grüße,

Felix Riesterer.