Kann ich Tabellen mit durch Semikolons getrennten Werten in MySQL ohne externe Tools verknüpfen?

Kann ich das mit reinem MySQL lösen? (Verknüpfen bei ; Getrennte Werte in einer Spalte)

Problem:

Sie haben Daten in zwei Tabellen gespeichert, wobei eine Spalte in der ersten Tabelle mehrere auflistet Werte durch Semikolons getrennt. Sie müssen einen Inner Join basierend auf diesen getrennten Werten durchführen, haben aber keine Verknüpfungstabelle und können keine externen Programmiersprachen verwenden.

Diskussion:

Die Herausforderung besteht darin, die durch Semikolons getrennte Liste in separate Zeilen umzuwandeln, um eine Verknüpfung mit der zweiten Tabelle zu ermöglichen. Dies kann mithilfe einer Technik namens „Ableiten“ oder „Normalisieren“ der Daten erreicht werden.

Lösung:

1. Erstellen Sie eine Ganzzahlreihentabelle:

Um die Daten zu normalisieren, benötigen Sie zunächst eine Tabelle, die einen Zahlenbereich enthält. In diesem Fall könnten Sie eine Integerseries-Tabelle mit IDs von 1 bis zur maximalen Anzahl von Elementen erstellen, die Sie in der durch Semikolons getrennten Liste erwarten.

2. Verwenden Sie JOIN und Unterabfragen:

Sobald Sie die Integerseries-Tabelle haben, führen Sie die folgende Abfrage aus:

<code class="sql">SELECT user_resource.user, 
       resource.data

FROM user_resource 
     JOIN integerseries AS isequence 
       ON isequence.id <= COUNT_IN_SET(user_resource.resources, ';') /* normalize */

     JOIN resource 
       ON resource.id = VALUE_IN_SET(user_resource.resources, ';', isequence.id)      
ORDER BY
       user_resource.user,  resource.data</code>

Erklärung:

• Diese Abfrage verknüpft die Tabelle „user_resource“ mit der Tabelle „Integerseries“, um eine Reihe von Zeilen für jeden Benutzer und jedes Element in der entsprechenden Ressourcenspalte zu generieren.
• Die Funktion COUNT_IN_SET zählt die Anzahl der Elemente in der Ressourcenspalte und Die Funktion VALUE_IN_SET extrahiert ein bestimmtes Element basierend auf der isequence.id.
• Der abschließende Join mit der Ressourcentabelle gleicht das extrahierte Element mit der ID ab, um die entsprechenden Daten abzurufen.

Zusätzliche Funktionen (optional):

Die Abfrage verwendet zwei benutzerdefinierte Funktionen, COUNT_IN_SET und VALUE_IN_SET, die wie folgt definiert werden können:

<code class="sql">-- Function to count the number of delimited items in a string
DELIMITER $$
DROP FUNCTION IF EXISTS `COUNT_IN_SET`$$
CREATE FUNCTION `COUNT_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1)
                               ) RETURNS INTEGER
BEGIN
      RETURN CHAR_LENGTH(haystack) - CHAR_LENGTH( REPLACE(haystack, delim, '')) + 1;
END$$
DELIMITER ;

-- Function to get the value at a specific index in a delimited string
DELIMITER $$
DROP FUNCTION IF EXISTS `VALUE_IN_SET`$$
CREATE FUNCTION `VALUE_IN_SET`(haystack VARCHAR(1024), 
                               delim CHAR(1), 
                               which INTEGER
                               ) RETURNS VARCHAR(255) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
      RETURN  SUBSTRING_INDEX(SUBSTRING_INDEX(haystack, delim, which),
                     delim,
                     -1);
END$$
DELIMITER ;</code>

Diese Funktionen bieten eine generische Möglichkeit um durch Trennzeichen getrennte Zeichenfolgen in SQL-Abfragen zu bearbeiten.

Beispieltabellen und -daten:

<code class="sql">-- Integerseries table
CREATE TABLE `integerseries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
);
-- Resource table
CREATE TABLE `resource` (
  `id` int(11) NOT NULL,
  `data` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

-- Data for resource table
INSERT INTO `resource` (`id`, `data`) VALUES
(1, 'abcde'),
(2, 'qwerty'),
(3, 'azerty');

-- User_resource table
CREATE TABLE `user_resource` (
  `user` varchar(50) NOT NULL,
  `resources` varchar(250) DEFAULT NULL,
  PRIMARY KEY (`user`)
);

-- Data for user_resource table
INSERT INTO `user_resource` (`user`, `resources`) VALUES
('sampleuser', '1;2;3'),
('stacky', '2'),
('testuser', '1;3');</code>

Ausgabe:

Ausführen der Eine Abfrage der Beispieldaten erzeugt die folgende Ausgabe:

+----------+-------+
| user     | data   |
+----------+-------+
| sampleuser | abcde  |
| sampleuser | qwerty |
| sampleuser | azerty |
| stacky    | qwerty |
| testuser  | abcde  |
| testuser  | azerty |
+----------+-------+

Das obige ist der detaillierte Inhalt vonKann ich Tabellen mit durch Semikolons getrennten Werten in MySQL ohne externe Tools verknüpfen?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!