Bagaimana untuk Menyertai Lajur Data Dipisahkan Koma dengan Cekap dalam Pangkalan Data?-tutorial mysql-php.cn

Bagaimana untuk Menyertai Lajur Data Dipisahkan Koma dengan Cekap dalam Pangkalan Data?

Susan Sarandon

Lepaskan： 2024-12-27 16:07:10

asal

455 orang telah melayarinya

How to Efficiently Join Comma-Separated Data Columns in Databases?

Menyertai Lajur Data Dipisahkan Koma: Penyelesaian Mendalam

Dalam sistem pangkalan data, selalunya perlu untuk memanipulasi data yang disimpan dalam koma- lajur yang dipisahkan. Menormalkan data menjadi berbilang jadual adalah penyelesaian yang ideal, tetapi terdapat kes yang mungkin tidak dapat dilaksanakan. Di sini kami membentangkan pelbagai kaedah untuk menyertai lajur yang dipisahkan koma dengan cekap.

Normalisasi dan Cantuman Jadual

Menormalkan data ke dalam jadual berasingan ialah pendekatan yang paling berkesan. Ini melibatkan mencipta jadual baharu dengan baris untuk setiap nilai unik dalam lajur yang dipisahkan koma. Jadual kemudiannya boleh dicantumkan menggunakan hubungan kunci asing.

-- T1 Table
CREATE TABLE T1
(
  col1 varchar(2), 
  col2 varchar(5),
  constraint pk1_t1 primary key (col1)
);

-- T2 Table
CREATE TABLE T2
(
  col1 varchar(2), 
  col2 varchar(2),
  constraint pk1_t2 primary key (col1, col2),
  constraint fk1_col2 foreign key (col2) references t1 (col1)
);

Salin selepas log masuk

Setelah dinormalisasi, data boleh disoal dengan mudah menggunakan gabungan:

SELECT t2.col1, t1.col2
FROM t2
INNER JOIN t1
  ON t2.col2 = t1.col1

Salin selepas log masuk

Fungsi Pisahan Tersuai untuk Data Tidak Dinormalkan

Jika penormalan tidak dapat dilakukan, kita boleh mencipta fungsi pisah tersuai untuk menukar data yang dipisahkan koma ke dalam baris individu.

CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))       
RETURNS @temptable TABLE (items varchar(MAX))       
AS       
BEGIN      
    DECLARE @idx int       
    DECLARE @slice varchar(8000)       

    SELECT @idx = 1       
    IF LEN(@String)<1 OR @String IS NULL RETURN       

    WHILE @idx!= 0       
    BEGIN       
        SET @idx = CHARINDEX(@Delimiter,@String)       
        IF @idx!=0       
            SET @slice = LEFT(@String,@idx - 1)       
        ELSE       
            SET @slice = @String       

        IF(LEN(@slice)>0)  
            INSERT INTO @temptable(Items) VALUES(@slice)       

        SET @String = RIGHT(@String,LEN(@String) - @idx)       
        IF LEN(@String) = 0 BREAK       
    END   
RETURN 
END;

Salin selepas log masuk

Menggunakan fungsi ini, kita boleh menyertai jadual asal dengan data belah:

;WITH cte AS
(
  SELECT c.col1, t1.col2
  FROM t1
  INNER JOIN 
  (
    SELECT t2.col1, i.items col2
    FROM t2
    CROSS APPLY dbo.split(t2.col2, ',') i
  ) c
    ON t1.col1 = c.col2
)
SELECT DISTINCT c.col1, 
  STUFF(
         (SELECT DISTINCT ', ' + c1.col2
          FROM cte c1
          WHERE c.col1 = c1.col1
          FOR XML PATH('')), 1, 1, '') col2
FROM cte c

Salin selepas log masuk

UNTUK Aplikasi Langsung XML PATH

Kaedah lain melibatkan aplikasi langsung FOR XML PATH ciri:

SELECT col1, 
(
  SELECT ', '+t1.col2
  FROM t1
  WHERE ','+t2.col2+',' LIKE '%,'+CAST(t1.col1 AS VARCHAR(10))+',%'
  FOR XML PATH(''), TYPE
).value('SUBSTRING(TEXT()[1], 3)', 'VARCHAR(MAX)') AS col2
FROM t2;

Salin selepas log masuk

Kesimpulan

Penyelesaian optimum bergantung pada senario tertentu. Menormalkan data ialah pilihan yang paling cekap, tetapi jika itu tidak boleh dilaksanakan, menggunakan fungsi split tersuai atau aplikasi langsung FOR XML PATH boleh memberikan hasil yang cekap.

Atas ialah kandungan terperinci Bagaimana untuk Menyertai Lajur Data Dipisahkan Koma dengan Cekap dalam Pangkalan Data?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!