MySQL - 一個欄位儲存多個ID時的查詢

一、說明

    當在一張表的某個欄位中儲存了另外一張表的多個ID時常用查詢

二、準備

    假設有一部落格，每一個主題允許有多個類別。資料庫設計時建了類別和主題兩張表，主題對類別的關聯設定在主題表中。

-- 類別表DDL
CREATE TABLE `T_CATEGORYS` (
  `C_ID` int(11) NOT NULL,
  `C_NAME` varchar(255) NOT NULL,
  PRIMARY KEY (`C_ID`)
) 

-- 主題表DDL
CREATE TABLE `T_TOPIC` (
  `T_ID` int(11) NOT NULL,
  `T_NAME` varchar(255) NOT NULL,
  `T_CATEGORYS` varchar(128) NOT NULL,
  PRIMARY KEY (`T_ID`)
)

-- 準備類別資料
INSERT INTO `T_CATEGORY` (`C_ID`, `C_NAME`) VALUES ('1', 'Struts');
INSERT INTO `T_CATEGORY` (`C_ID`, `C_NAME`) VALUES ('2', 'Spring');
INSERT INTO `T_CATEGORY` (`C_ID`, `C_NAME`) VALUES ('3', 'iBatis');
INSERT INTO `T_CATEGORY` (`C_ID`, `C_NAME`) VALUES ('4', 'Hibernate');

-- 準備主題資料
INSERT INTO `xilen_dev`.`T_TOPIC` (`T_ID`, `T_NAME`, `T_CATEGORYS`) VALUES ('1', 'SSI整合示例', '1,2,3');
INSERT INTO `xilen_dev`.`T_TOPIC` (`T_ID`, `T_NAME`, `T_CATEGORYS`) VALUES ('2', 'SSH整合示例', '1,2,4');

三、查詢

    1、查詢多ID的欄位中包含某個ID的記錄
        SQL：

-- 查詢類別包含了iBatis(id=3)的主題記錄
SELECT * FROM T_TOPIC WHERE FIND_IN_SET(3, T_CATEGORYS)

        結果：

            

    2、查詢多ID欄位中這些ID所代表的記錄

        SQL：

-- 查詢主題 SSH整合示例(id=2) 包含的類別記錄
SELECT * FROM T_CATEGORY c WHERE (SELECT FIND_IN_SET(c.C_ID,t.T_CATEGORYS) from T_TOPIC t where t.T_ID=2)

        結果：

            

    3、查詢時將多ID的欄位的ID轉換為對應的標識

        SQL：

-- 查詢主題表時將類別的ID轉換為類別的NAME
SELECT t.T_ID AS topicId, 
	(SELECT GROUP_CONCAT(c.C_NAME) FROM T_CATEGORY c WHERE FIND_IN_SET(c.C_ID,t.T_CATEGORYS)) AS categoryName     
FROM T_TOPIC t

        結果：

            

    4、補充

        FIND_IN_SET函式預設是以符號 , 作為分割符的，如果多ID欄位使用的不是這個預設的分隔符，而是以如 | 的符號作為分隔符，那麼：

-- 非 , 作為分隔符時，先REPLACE函式替換它
SELECT * FROM T_TOPIC WHERE FIND_IN_SET(3, REPLACE(T_CATEGORYS,'|',','))