Ich versuche eine normalisierte MySQL-Datenbank zu erstellen, die die folgenden drei Tabellen enthält. Die erste Tabelle enthält eine Liste von Elementen, die durch verschiedene Tags beschrieben werden können. Die dritte Tabelle enthält die verschiedenen Tags, die zur Beschreibung der Elemente in der ersten Tabelle verwendet werden. Die mittlere Tabelle verbindet die anderen beiden Tabellen miteinander. In jedem Fall ist die ID ein automatisch inkrementierender Primärschlüssel (und jeder Schlüssel wird als Fremdschlüssel in der mittleren Tabelle verwendet)
%Vor%Ich möchte eine Abfrage von einem oder mehreren Tags für die drei Tabellen ausführen, um die Elemente zurückzugeben, die mit ALLEN Tags übereinstimmen.
Wenn zum Beispiel nach "pet" gefragt wird, werden die Gegenstände (1) spaniel, (2) tabby und (4) goldfish zurückgegeben, da alle mit "pet" markiert sind. Die Abfrage nach "billig" und "Haustier" zusammen würde (1) Spaniel und (4) Goldfisch zurückgeben, weil sie beide mit "billig" und "Haustier" markiert sind. Tabby würde nicht zurückgegeben werden, da es nur "Haustier", aber nicht "billig" getaggt ist (in meiner Welt Tabby Katzen sind teuer: P)
Die Suche nach "billig", "Haustier" und "Hund" würde nur (1) Spaniel zurückgeben, da es der einzige ist, der alle drei Tags zusammenbringt.
Wie auch immer, das ist das gewünschte Verhalten. Ich habe zwei Fragen.
Ist dies der beste Weg, meine Tabellen für meine beabsichtigten Zwecke einzurichten? ich bin noch immer neu in Sachen Normalisierung Datenbanken, und ich nehme das auf wie ich gehen Sie mit - irgendwelche Eingabe auf der Effizienz oder auch wenn das ein passendes Layout ist Für meine Datenbank wäre das viel geschätzt.
Vorausgesetzt, das obige Setup ist praktikabel, wie könnte ich ein einzelne MySQL-Abfrage, um meine zu erreichen beabsichtigter Zweck? * (das für, a Reihe von Tags, die NUR die Artikel, die alle angegebenen übereinstimmen Stichworte). Ich habe versucht, eine Abwechslung zu machen von JOINs / UNIONs, aber keiner von ihnen gib mir den gewünschten Effekt (normalerweise Gib ALLE Gegenstände zurück, die mit ANY of übereinstimmen die Tags). Ich habe einige Zeit verbracht Durchsehen des MySQL-Handbuchs online, aber ich fühle mich wie ich vermisse etwas konzeptionell.
* Ich sage single query, da ich natürlich eine Reihe von einfachen WHERE / JOIN-Abfragen ausführen kann, eine für jedes Tag und dann die zurückgegebenen Elemente in PHP oder etwas nachher zusammenfasse / sortiere, aber es erscheint unsinnig und ineffizient Art und Weise es zu tun. Ich habe das Gefühl, dass es möglich sein sollte, dies mit einer einzigen MySQL-Abfrage zu tun, vorausgesetzt, das entsprechende Setup ist vorhanden.
Ihr Schema sieht ziemlich gut aus. Die ID-Spalte in Ihrer Join-Tabelle ist nicht erforderlich. Erstellen Sie einfach einen Primärschlüssel aus den ID-Spalten der anderen Tabellen (siehe auch den Kommentar von Marjan Venema und Sollte ich zusammengesetzte Primärschlüssel verwenden oder nicht? für alternative Ansichten dazu). Die folgenden Beispiele zeigen, wie Sie die Tabellen erstellen, einige Daten hinzufügen und die angeforderten Abfragen ausführen können.
Erstellen Sie Tabellen mit Fremdschlüsseleinschränkungen . Kurz gesagt, Fremdschlüsseleinschränkungen tragen dazu bei, die Integrität der Datenbank sicherzustellen. In diesem Beispiel verhindern sie das Einfügen von Elementen in die Join-Tabelle ( item_tag ), wenn in den Tabellen item und tag keine übereinstimmenden Elemente vorhanden sind:
Fügen Sie einige Testdaten ein:
%Vor%Wählen Sie alle Elemente und alle Tags aus:
%Vor%Wählen Sie Elemente mit einem bestimmten Tag:
%Vor%Wählen Sie Elemente mit einem oder mehreren Tags aus. Beachten Sie, dass dadurch Artikel mit den Tags billig ODER pet zurückgegeben werden:
%Vor%Die obige Abfrage erzeugt eine Antwort, die Sie möglicherweise nicht möchten, wie in der folgenden Abfrage hervorgehoben. In diesem Fall gibt es keine Elemente mit dem Tag house , aber diese Abfrage gibt immer noch einige Zeilen zurück:
%Vor% Sie können das beheben, indem Sie GROUP BY und HAVING :
GROUP BY bewirkt, dass alle Elemente mit derselben ID (oder der von Ihnen angegebenen Spalte) in einer einzelnen Zeile gruppiert werden, wodurch Dubletten effektiv entfernt werden. HAVING COUNT begrenzt die Ergebnisse auf diejenigen, bei denen die Anzahl der übereinstimmenden gruppierten Zeilen gleich zwei ist. Dadurch wird sichergestellt, dass nur Elemente mit zwei Tags zurückgegeben werden. Beachten Sie, dass dieser Wert mit der Anzahl der in der Klausel IN angegebenen Tags übereinstimmen muss. Hier ist ein Beispiel, das etwas hervorbringt:
Beachten Sie, dass im vorherigen Beispiel die Elemente gruppiert wurden, sodass Sie keine Duplikate erhalten. In diesem Fall ist die Spalte tag nicht erforderlich, da dies die Ergebnisse nur verwirrt. Sie wissen bereits, welche Tags vorhanden sind, da Sie Elemente mit diesen Tags angefordert haben. Sie können daher die Dinge ein wenig vereinfachen, indem Sie die Spalte tag aus der Abfrage entfernen:
Sie könnten einen Schritt weiter gehen und % co_de verwenden % , um eine Liste übereinstimmender Tags bereitzustellen. Dies kann nützlich sein, wenn Sie eine Liste von Elementen haben möchten, die eines oder mehrere der angegebenen Tags haben, aber nicht notwendigerweise alle:
%Vor% Ein Problem mit dem obigen Schemadesign besteht darin, dass doppelte Elemente und Tags eingegeben werden können. Das heißt, Sie könnten bird so oft in die Tabelle GROUP_CONCAT einfügen, wie Sie möchten, und das ist nicht gut. Eine Möglichkeit, das zu beheben, besteht darin, den Spalten tag und UNIQUE INDEX ein item hinzuzufügen. Dies hat den zusätzlichen Vorteil, dass Abfragen beschleunigt werden, die auf diesen Spalten basieren. Die aktualisierten tag -Befehle sehen nun so aus:
Wenn Sie jetzt versuchen, einen doppelten Wert einzufügen, wird MySQL Sie daran hindern:
%Vor%Ja. Dies nennt man relationale Teilung. Eine Vielzahl von Techniken wird hier diskutiert Ссылка
Ein Ansatz wäre, ein doppeltes Negativ zu verwenden. dh. um alle Datensätze aus Tabelle 1 auszuwählen, für die kein Tag in der Liste 'cheap' vorhanden ist, hat 'pet' keinen zugeordneten Datensatz in table2
%Vor%Dieses Mapping-Tabellen-Konzept ist ziemlich Standard und sieht hier gut umgesetzt aus. Das einzige, was ich ändern würde, ist, die ID in Tabelle 2 loszuwerden; wofür würdest du es benutzen? Machen Sie einen gemeinsamen Schlüssel für Tabelle 2 sowohl für die Artikel-ID als auch für die Tag-ID.
Eigentlich ist es schwierig, auszuwählen, wo ein Element mit ALLEN Tags übereinstimmt. Versuchen Sie Folgendes:
SELECT item_id, COUNT (tag_id) FROM Tabelle2 WHERE tag_id IN (hier gesetzt) GROUP BY item_id
Wo die Anzahl der Anzahl der Tag-IDs in Ihrem Set entspricht, haben Sie eine Übereinstimmung gefunden.
Nicht sicher, dass andere dies bereits erwähnt haben, aber die ID-Spalte in der zweiten Tabelle ist redundant. Sie können einfach einen Join-Primärschlüssel erstellen:
%Vor%Ansonsten ist es ein bretty Standard m: n Datenbankschema und es sollte gut funktionieren.
Vielen Dank für Ihre ausführlichen und hilfreichen Antworten. Das Bit zur Verwendung von "WHERE Tag IN ('tag_1' ... 'tag_x')" in Verbindung mit COUNT zur Auswahl von Elementen, die mit allen Tags übereinstimmen, war genau das, was ich vorher vermisste.
Die Eingabe bei der Verwendung von zusammengesetzten Primärschlüsseln war auch sehr hilfreich - ich hatte das Gefühl, dass es keinen Sinn hatte, einen eindeutigen ID-Schlüssel in der mittleren Tabelle zu verwenden, aber nie realisiert, dass ich zusammengesetzte Schlüssel verwenden könnte.
Nochmals vielen Dank! Ihr seid großartig!
Tags und Links mysql database-design join relational-database