LastBoyScout: mySQL Bei Suche über mehrer Spalten auch den Feldnamen des Treffer ausgeben

Ich Suche in einer mySQL- Tabelle über mehre Spalten hinweg:

SELECT * FROM tabelle WHERE PHONE LIKE '$search' OR MOBILE LIKE '$search' OR FAX LIKE '$search'

Nun würde ich bei einem Treffer gerne auch die Bezeichnung jener Spalte erhalten, welche letztendlich den Treffer liefert. Gibt es hierzu eine elegante Möglichkeit? Oder muss ich hierzu das Ergebnis mit anderen Mitteln erneut auswerten?

Habe schon recherchiert, aber hierzu leider keinen Anhaltspunkt gefunden.

Danke und Gruß,

LBS

akzeptierte Antworten

  1. Hi,

    Ich Suche in einer mySQL- Tabelle über mehre Spalten hinweg:

    SELECT * FROM tabelle WHERE PHONE LIKE '$search' OR MOBILE LIKE '$search' OR FAX LIKE '$search'

    Nun würde ich bei einem Treffer gerne auch die Bezeichnung jener Spalte erhalten, welche letztendlich den Treffer liefert.

    Wieso 'jener Spalte'? 'jene Spalten' wäre doch richtiger.

    Ich kenne Leute, die haben gar keine Festnetznummer, die geben bei solchen Formularen bei PHONE und bei MOBILE ihre einzige Nummer an …

    cu,
    Andreas a/k/a MudGuard

    1. Hi,

      Nachtrag:

      wenn nur eine der Spalten ausgegeben werden soll, die zutrifft,könnte man evtl. was mit verschachteltem if machen.

      A la SELECT t.*, if (PHONE LIKE '$search','PHONE', if (MOBILE LIKE '$search), 'MOBILE', 'FAX') from tabelle t …

      Brauchst Du wirklich like? Suchst Du mit Wildcards ('%' bzw. '_')? Ansonsten besser das like durch ein = ersetzen.

      cu,
      Andreas a/k/a MudGuard

      1. Brauchst Du wirklich like? Suchst Du mit Wildcards ('%' bzw. '_')? Ansonsten besser das like durch ein = ersetzen.

        Ja, Suche mit % am Ende, damit auch Einträge Auflistet werden welche eine abweichenden Durchwahl haben.

        Hatte gehofft mySQL hat für so etwas eine elegante Funktion an Board.

        1. Hallo LastBoyScout,

          ja, hat es. LIKE.

          Wenn die Spalten indexiert sind, ist ein LIKE mit % am Ende effizient. Die Wahrheit liefert aber immer nur der Explain.

          Ich meine jedoch, dass die Zeiten längst vorbei sind, wo man „aus Performancegründen“ die Abfrage PHONE LIKE '$search' durch PHONE >= '$search' AND PHONE < '{$search}999999999' ersetzt hat.

          Rolf

          --
          sumpsi - posui - obstruxi
          1. ja, hat es. LIKE.

            Meine eigentlich eine Funktion welche bei einer Suche auch die Bezeichnung Treffer liefernder Spalte/n zurück gibt.

    2. Hi Andreas,

      Wieso 'jener Spalte'? 'jene Spalten' wäre doch richtiger.

      Ich kenne Leute, die haben gar keine Festnetznummer, die geben bei solchen Formularen bei PHONE und bei MOBILE ihre einzige Nummer an …

      Ne, Singular ist schon korrekt...

      Handelt sich hierbei um eine einigermaßen gepflegte lokale Datenbank 😉

  2. Hello,

    Ich Suche in einer mySQL- Tabelle über mehre Spalten hinweg:

    SELECT * FROM tabelle WHERE PHONE LIKE '$search' OR MOBILE LIKE '$search' OR FAX LIKE '$search'

    Nun würde ich bei einem Treffer gerne auch die Bezeichnung jener Spalte erhalten, welche letztendlich den Treffer liefert. Gibt es hierzu eine elegante Möglichkeit? Oder muss ich hierzu das Ergebnis mit anderen Mitteln erneut auswerten?

    Habe schon recherchiert, aber hierzu leider keinen Anhaltspunkt gefunden.

    Du könntest das Statement mit der if()-Funktion weiter verkomplizieren.

    Oder Du könntest weiter normalisieren.

    clientno   type  data
    --------------------------------  
    70678      Fax   0531/12345-7
    70678      Tel   0531/12345-0
    70678      Mail  hans@dampf.lan
    70678      Mob   0171/555333555
    

    Glück Auf
    Tom vom Berg

    --
    Es gibt nichts Gutes, außer man tut es!
    Das Leben selbst ist der Sinn.
    1. Oder Du könntest weiter normalisieren.

      clientno   type  data
      --------------------------------  
      70678      Fax   0531/12345-7
      70678      Tel   0531/12345-0
      70678      Mail  hans@dampf.lan
      70678      Mob   0171/555333555
      

      Könntest Du das Bitte perzisieren, stehe da momentan auf dem Schlauch!?

      1. Hello,

        Oder Du könntest weiter normalisieren.

        clientno   type  data
        --------------------------------  
        70678      Fax   0531/12345-7
        70678      Tel   0531/12345-0
        70678      Mail  hans@dampf.lan
        70678      Mob   0171/555333555
        

        Könntest Du das Bitte perzisieren, stehe da momentan auf dem Schlauch!?

        Statt dass Du dir einen Krampf in der SQL-Schicht codest mit dem vorhandenen (schlechten) Datenmodell und dann voraussichtlich auch in der API noch so verknotet weitermachen musst, könntest Du dein Datenmodell überdenken, und die Eigenschaften (Tel, Fax, Mobile, Mail, ...) aus der Waagerechten (dem Datensatz) herausnehmen und in die Senkrechte (eine eigene Tabelle) verfrachten.

        Und plötzlich wären die Zuordnungsprobleme verschwunden.

        Glück Auf
        Tom vom Berg

        --
        Es gibt nichts Gutes, außer man tut es!
        Das Leben selbst ist der Sinn.
        1. n'Abend,

          clientno   type  data
          --------------------------------  
          70678      Fax   0531/12345-7
          70678      Tel   0531/12345-0
          70678      Mail  hans@dampf.lan
          70678      Mob   0171/555333555
          

          Könntest Du das Bitte perzisieren, stehe da momentan auf dem Schlauch!?

          Statt dass Du dir einen Krampf in der SQL-Schicht codest mit dem vorhandenen (schlechten) Datenmodell und dann voraussichtlich auch in der API noch so verknotet weitermachen musst, könntest Du dein Datenmodell überdenken, und die Eigenschaften (Tel, Fax, Mobile, Mail, ...) aus der Waagerechten (dem Datensatz) herausnehmen und in die Senkrechte (eine eigene Tabelle) verfrachten.

          für diese Anregung hast du von mir schon ein Plus bekommen, zumal dein Beispiel sehr schön zeigt, dass man dann mühelos noch weitere Kontaktmöglichkeiten einbringen kann (hier die Mailadresse).

          Allerdings würde ich dann dringend raten, die Telefonnummern auch normalisiert zu speichern. Dein Beispielauszug enthält noch verschiedene Trennzeichen. Die würde ich dann komplett rauslöschen.

          Und plötzlich wären die Zuordnungsprobleme verschwunden.

          Genau. Wald? Was für'n Wald? Hier sind nur lauter Bäume.

          Live long and pros healthy,
           Martin

          --
          Ich stamme aus Ironien, einem Land am sarkastischen Ozean.
          1. Liebe Mitdenker, liebe Wissende, liebe Neugierige,

            n'Abend,

            clientno   type  data
            --------------------------------  
            70678      Fax   0531/12345-7
            70678      Tel   0531/12345-0
            70678      Mail  hans@dampf.lan
            70678      Mob   0171/555333555
            

            Könntest Du das Bitte perzisieren, stehe da momentan auf dem Schlauch!?

            Statt dass Du dir einen Krampf in der SQL-Schicht codest mit dem vorhandenen (schlechten) Datenmodell und dann voraussichtlich auch in der API noch so verknotet weitermachen musst, könntest Du dein Datenmodell überdenken, und die Eigenschaften (Tel, Fax, Mobile, Mail, ...) aus der Waagerechten (dem Datensatz) herausnehmen und in die Senkrechte (eine eigene Tabelle) verfrachten.

            für diese Anregung hast du von mir schon ein Plus bekommen, zumal dein Beispiel sehr schön zeigt, dass man dann mühelos noch weitere Kontaktmöglichkeiten einbringen kann (hier die Mailadresse).

            Allerdings würde ich dann dringend raten, die Telefonnummern auch normalisiert zu speichern. Dein Beispielauszug enthält noch verschiedene Trennzeichen. Die würde ich dann komplett rauslöschen.

            Da Speicherplatz nicht mehr sooo teuer ist, baue ich in solchen Fällen immer zwei Datenspalten auf. Eine mit Formatierung (Slashes, Dashes, Umlaute, Nicht-ASCII-Zeichen, Trallala) und eine normalisierte. Die normalisierte wird immer automatisch durch einen Trigger und eine Benutzerfunktion beschrieben, wenn sich in der formatierten etwas ändert. Die normalisierte ist gegen direktes Beschreiben geschützt.

            Spirituelle Grüße
            Euer Robert

            --
            Möge der Forumsgeist ewig leben!
            1. Da Speicherplatz nicht mehr sooo teuer ist, baue ich in solchen Fällen immer zwei Datenspalten auf. Eine mit Formatierung (Slashes, Dashes, Umlaute, Nicht-ASCII-Zeichen, Trallala) und eine normalisierte.

              Das ist natürlich der eleganteste Weg, um derartige Unzulänglichkeiten von vornherein zu vermeiden. 👍 Wünschte die Urheber der DB würden das mal beherzigen.🤬

              1. Hallo LastBoyScout,

                hast Du keine Leserechte auf der DB, oder sollst Du sie einfach nur lesen?

                Es gibt zwei Möglichkeiten, sich vom DBMS helfen zu lassen, ohne dass die Originalanwendung etwas davon mitbekommt (bis auf minimale Tempoverluste):

                (1) Ein Update-Trigger, der Änderungen an Fon, Mob und Fax automatisch von Sonderzeichen befreit und den Wert passend in eine andere Tabelle einträgt. Für diese bietet sich dann die bereits vorgeschlagene Struktur an, wo ein Satz immer nur eine Nummer enthält. Der Trigger ist nicht ganz trivial und braucht etwas Logik, er muss ja auch den Satz in der Suchtabelle löschen können, wenn ein Feld geleert wird. Bei Neueintragungen und Änderungen gibt's INSERT mit ON DUPLICATE KEY UPDATE.

                (2) Ist Dir das zu kompliziert, kann man auch per ALTER TABLE drei materialisierte, berechnete Spalten für Fon, Mob und Fax in die Originaltabelle einsetzen. Die Berechnungsformel kann die REPLACE-Ausdrücke enthalten.

                Rolf

                --
                sumpsi - posui - obstruxi
            2. Hello,

              Da Seicherplatz nicht mehr sooo teuer ist, baue ich in solchen Fällen immer zwei Datenspalten auf. Eine mit Formatierung (Slashes, Dashes, Umlaute, Nicht-ASCII-Zeichen, Trallala) und eine normalisierte. Die normalisierte wird immer automatisch durch einen Trigger und eine Benutzerfunktion beschrieben, wenn sich in der formatierten etwas ändert. Die normalisierte ist gegen direktes Beschreiben geschützt.

              Genaugenommen muss man Aktionen zu dieser Spalte in zwei Triggern vorsehen:

              • INSERT
              • UPDATE

              DELETE kann man sich wohl schenken, wenn die Spalte in derselben Tabelle steht. Wenn man die Information allerdings in eine andere Tabelle auslagert, benötigt man auch einen DELETE-Trigger.

              Glück Auf
              Tom vom Berg

              --
              Es gibt nichts Gutes, außer man tut es!
              Das Leben selbst ist der Sinn.
              1. Lieber Tom,

                Da Seicherplatz nicht mehr sooo teuer ist, baue ich in solchen Fällen immer zwei Datenspalten auf. Eine mit Formatierung (Slashes, Dashes, Umlaute, Nicht-ASCII-Zeichen, Trallala) und eine normalisierte. Die normalisierte wird immer automatisch durch einen Trigger und eine Benutzerfunktion beschrieben, wenn sich in der formatierten etwas ändert. Die normalisierte ist gegen direktes Beschreiben geschützt.

                Genaugenommen muss man Aktionen zu dieser Spalte in zwei Triggern vorsehen:

                • INSERT
                • UPDATE

                Das stimmt wohl. Aber man kann sollte dieselbe benutzerdefinierte Funktion verwenden, die in den beiden Triggern eingebunden wird. Sonst könnte bei Änderungen schnell mal etwas in die Hose gehen.

                DELETE kann man sich wohl schenken, wenn die Spalte in derselben Tabelle steht. Wenn man die Information allerdings in eine andere Tabelle auslagert, benötigt man auch einen DELETE-Trigger.

                Der Punkt geht jedenfalls an Dich ;-)

                Spirituelle Grüße
                Dein Robert

                --
                Möge der Forumsgeist ewig leben!
        2. Hi Tom,

          aus der Waagerechten (dem Datensatz) herausnehmen und in die Senkrechte (eine eigene Tabelle) verfrachten.

          Jetzt hab ich es kapiert... wirklich sehr einleuchtend erklärt DANKE!

          Nur leider bin ich bez. des Datenbankmodell an die Hauptanwendung gebunden und kann es daher nicht selbst ändern. Somit muss ich mit den Unzulänglichkeiten mittels eines geeigneten Workaround irgendwie zurechtkommen. Bin ja zumindest schon mal froh, das wenigstens keine Duplikate vorhanden sind.

          1. Hello,

            aus der Waagerechten (dem Datensatz) herausnehmen und in die Senkrechte (eine eigene Tabelle) verfrachten. Jetzt hab ich es kapiert... wirklich sehr einleuchtend erklärt DANKE!

            Nur leider bin ich bez. des Datenbankmodell an die Hauptanwendung gebunden und kann es daher nicht selbst ändern. Somit muss ich mit den Unzulänglichkeiten mittels eines geeigneten Workaround irgendwie zurechtkommen. Bin ja zumindest schon mal froh, das wenigstens keine Duplikate vorhanden sind.

            Vielleicht kannst Du eine Hilfstabelle aufbauen?
            Um wieviele Datensätze handelt es sich insgesamt?
            Wie oft ändert sich der Datenbestand?

            Glück Auf
            Tom vom Berg

            --
            Es gibt nichts Gutes, außer man tut es!
            Das Leben selbst ist der Sinn.
            1. Vielleicht kannst Du eine Hilfstabelle aufbauen?

              Da ich keine internen Kenntnisse um die Hauptanwendung habe, möchte ich lieber nicht Hand an die Datenbankarchitektur legen. Müsste das dann eher in eine zweite DB auslagern... Nehme dann aber doch lieber einen entsprechenden Workaround in kauf als eine zweite DB zu pflegen.

              Um wieviele Datensätze handelt es sich insgesamt?

              Sind so um die 500 Datensätze... Performance spielt daher eher eine untergeordnete rolle.

              Wie oft ändert sich der Datenbestand?

              Änderungen sind eigentlich selten. Eher kommen hin und wieder neue Daten hinzu.

  3. Hallo LastBoyScout,

    ich hätte zwei Alternativen.

    (1) Verwende UNION ALL statt OR:

      SELECT tabelle.*, 'PHONE' as fundstelle
      FROM tabelle
      WHERE PHONE LIKE '$search' 
    UNION ALL
      SELECT tabelle.*, 'MOBILE' as fundstelle
      FROM tabelle 
      WHERE MOBILE LIKE '$search' 
    UNION ALL
      SELECT tabelle.*, 'FAX' as fundstelle
      FROM tabelle 
      WHERE FAX LIKE '$search'
    

    (2) Ermittle die Fundstelle über einen CASE-Ausdruck.

    SELECT tabelle.*, 
           CASE WHEN PHONE  LIKE '$search' THEN 'PHONE'
                WHEN MOBILE LIKE '$search' THEN 'MOBILE'
                WHEN FAX    LIKE '$search' THEN 'FAX' END as fundStelle
    FROM tabelle
    WHERE PHONE LIKE '$search' OR MOBILE LIKE '$search' OR FAX LIKE '$search'
    

    Schön ist beides nicht. In der ersten Variante rennst Du 3x durch die Tabelle, in der zweiten Variante prüfst Du alles doppelt. Vermutlich ist die 2. Variante schneller.

    Ich überlege gerade noch ob man eine Cursorschleife sinnvoll einsetzen kann, aber das dürfte unter dem Strich am mühsamsten und langsamsten sein.

    Dass Du beim befüllen der Variablen $search an's Escapen denken musst, schreibe ich mal rein pro forma unten drunter 😉

    Rolf

    --
    sumpsi - posui - obstruxi
    1. Hallo Rolf,

      Danke für deine Anregungen... tendiere wohl zu Lösung 2

      Etwas detaillierter sieht es bei mir aktuell so aus, wobei ich beim Suchstring nur Zahlen durchlasse:

      SELECT * FROM tabelle
      WHERE REPLACE(REPLACE(REPLACE(PHONE, ' ', ''), '/', ''), '-', '') LIKE '$search%'
      OR REPLACE(REPLACE(REPLACE(MOBILE, ' ', ''), '/', ''), '-', '') LIKE '$search%'
      OR REPLACE(REPLACE(REPLACE(FAX, ' ', ''), '/', ''), '-', '') LIKE '$search%'
      
      1. Hallo,

        SELECT * FROM tabelle
        WHERE REPLACE(REPLACE(REPLACE(PHONE, ' ', ''), '/', ''), '-', '') LIKE '$search%'
        OR REPLACE(REPLACE(REPLACE(MOBILE, ' ', ''), '/', ''), '-', '') LIKE '$search%'
        OR REPLACE(REPLACE(REPLACE(FAX, ' ', ''), '/', ''), '-', '') LIKE '$search%'
        

        ohne es zu wissen, stelle ich einfach mal zur Diskussion: Ist die String-Manipulation (Blank, Slash und Hyphen entfernen) in der Host-Applikation eventuell effizienter als ein dreifach verschachtelter SQL-Funktionsaufruf?

        Live long and pros healthy,
         Martin

        --
        Ich stamme aus Ironien, einem Land am sarkastischen Ozean.
      2. Tach!

        Etwas detaillierter sieht es bei mir aktuell so aus, wobei ich beim Suchstring nur Zahlen durchlasse:

        SELECT * FROM tabelle
        WHERE REPLACE(REPLACE(REPLACE(PHONE, ' ', ''), '/', ''), '-', '') LIKE '$search%'
        OR REPLACE(REPLACE(REPLACE(MOBILE, ' ', ''), '/', ''), '-', '') LIKE '$search%'
        OR REPLACE(REPLACE(REPLACE(FAX, ' ', ''), '/', ''), '-', '') LIKE '$search%'
        

        Solche Suchanfragen sollte man nicht auf ein DBMS loslassen. Felder, die erst noch berechnet werden müssen, bekommt man nur mit einem Full Table Scan gefunden. Speicher lieber ein extra Feld mit der selben Nummer ohne Sonderzeichen, dann kann das DBMS auch einen Index verwenden.

        dedlfix.

      3. Hallo LastBoyScout,

        aua, das sieht nicht hübsch aus. Dieses Klammerdickicht muss dann ja auch noch in den CASE hinein.

        Der UNION ALL ist dann wohl lesbarer.

        Ich habe gerade auch noch über eine UNPIVOTisierung nachgedacht, d.h. du erzeugst Dir eine temporäre Tabelle in dem Stil wie Tom sie vorgeschlagen hat. Das geht ebenfalls über UNION ALL. Problem ist nur, dass das bei einer großen Tabelle viel Zeit kostet.

        Wie groß ist deine Tabelle? Wie oft suchst Du darin? Kann es sinnvoll sein, berechnete Spalten hinzuzufügen, in der die Telefonnummern ohne Sonderzeichen stehen? Kann es sinnvoll sein, eine Zusatztabelle einzuführen, wo die gefilterten Werte drin stehen und wo jede Nummer zusammen mit Verwendungszweck in einer einzelnen Zeile steht? So eine Suchtabelle muss man pflegen, ja, aber falls die Suche andernfalls grottenlahm wird...

        Rolf

        --
        sumpsi - posui - obstruxi
        1. Mir ist bewusst das diese Verschachtelung äußerst unschön ist... leider kann ich hier auf die Tabelle nur lesend zugreifen, da es sich um Daten einer anderen Anwendung handelt. Und da nicht immer klar ist, wie die Rufnummer eingetragen wurde, muss ich das ja irgendwie berücksichtigen!?

          P.S. Hintergrund ist, bei einem eingehenden Anruf den passenden Datensatz zu ermitteln.