Bolehkah saya Menyertai Jadual pada Nilai Dipisahkan Titik Koma dalam MySQL Tanpa Alat Luaran?

Bolehkah saya Menyelesaikan Ini dengan MySQL Tulen? (Menyertai pada ; Nilai Terpisah dalam Lajur)

Masalah:

Anda mempunyai data yang disimpan dalam dua jadual, dengan satu lajur dalam jadual pertama menyenaraikan berbilang nilai dipisahkan oleh titik bertitik. Anda perlu melakukan cantuman dalaman berdasarkan nilai yang dipisahkan ini, tetapi anda tidak mempunyai jadual pemautan dan tidak boleh menggunakan sebarang bahasa pengaturcaraan luaran.

Perbincangan:

Cabarannya terletak pada menukar senarai yang dipisahkan koma bertitik ke dalam baris yang berasingan, membolehkan anda bergabung dengan jadual kedua. Ini boleh dicapai menggunakan teknik yang dipanggil "mendapatkan" atau "menormalkan" data.

Penyelesaian:

1. Cipta Jadual Integerseries:

Untuk menormalkan data, anda memerlukan jadual yang mengandungi julat nombor terlebih dahulu. Dalam kes ini, anda boleh membuat jadual siri integer dengan ID daripada 1 hingga bilangan maksimum elemen yang anda jangkakan dalam senarai dipisahkan koma bertitik.

2. Gunakan JOIN dan Subqueries:

Setelah anda mempunyai jadual integerseries, lakukan pertanyaan berikut:

<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>

Penjelasan:

• Pertanyaan ini bergabung dengan jadual sumber_pengguna dengan jadual siri integer untuk menjana satu set baris bagi setiap pengguna dan setiap elemen dalam lajur sumber yang sepadan.
• Fungsi COUNT_IN_SET mengira bilangan elemen dalam lajur sumber dan fungsi VALUE_IN_SET mengekstrak elemen tertentu berdasarkan isequence.id.
• Gabungan terakhir dengan jadual sumber sepadan dengan elemen yang diekstrak dengan ID untuk mendapatkan semula data yang sepadan.

Fungsi Tambahan (Pilihan):

Pertanyaan menggunakan dua fungsi tersuai, COUNT_IN_SET dan VALUE_IN_SET, yang boleh ditakrifkan seperti berikut:

<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>

Fungsi ini menyediakan cara generik untuk memanipulasi rentetan terhad dalam pertanyaan SQL.

Contoh Jadual dan Data:

<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>

Output:

Melaksanakan pertanyaan pada data sampel akan menghasilkan output berikut:

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

Atas ialah kandungan terperinci Bolehkah saya Menyertai Jadual pada Nilai Dipisahkan Titik Koma dalam MySQL Tanpa Alat Luaran?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!