原本只是想用一下FIND_IN_SET()這函數而想的題目,沒想到越變越複雜
目標:
有兩個表article和article_type,搜尋出所有種類為hot的文章,用article_type.id去搜尋,並把他所有種類描述列出來
資料庫:
mysql> select * from article; +----+----------+-------+---------+ | id | type_set | title | content | +----+----------+-------+---------+ | 1 | 1,2,4 | test1 | test1 | | 2 | 2,4 | test2 | test2 | | 3 | 2 | test3 | test3 | +----+----------+-------+---------+ mysql> select * from article_type; +----+---------------+ | id | description | +----+---------------+ | 1 | headline | | 2 | hot | | 3 | recommend | | 4 | international | +----+---------------+例:article.id = 1時, type_set=1,2,4 ,多一個欄位description_set = headline,hot,international
提示:
使用子查詢
解法:
mysql> SELECT article.*, ( SELECT GROUP_CONCAT(description SEPARATOR ",") FROM `article_type` WHERE FIND_IN_SET(id,type_set) ) AS description_set FROM `article`, article_type WHERE article_type.id IN(article.type_set) AND FIND_IN_SET('2',article.type_set); +----+----------+-------+---------+----------------------------+ | id | type_set | title | content | description_set | +----+----------+-------+---------+----------------------------+ | 1 | 1,2,4 | test1 | test1 | headline,hot,international | | 2 | 2,4 | test2 | test2 | hot,international | | 3 | 2 | test3 | test3 | hot | +----+----------+-------+---------+----------------------------+
喇叭王:設計資料庫 ,不會把1,2,4 放到欄位裡面,再開個table做relation可能會比較好。 => 同意
sqlfiddle.com模擬結果:
http://sqlfiddle.com/#!9/a0117/89/0
資料庫SQL:
CREATE TABLE IF NOT EXISTS `article` ( `id` int(11) NOT NULL, `type_set` varchar(255) NOT NULL, `title` varchar(255) NOT NULL, `content` text NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; INSERT INTO `article` (`id`, `type_set`, `title`, `content`) VALUES (1, '1,2,4', 'test1', 'test1'), (2, '2,4', 'test2', 'test2'), (3, '2', 'test3', 'test3'); CREATE TABLE IF NOT EXISTS `article_type` ( `id` int(11) NOT NULL, `description` varchar(255) NOT NULL ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; INSERT INTO `article_type` (`id`, `description`) VALUES (1, 'headline'), (2, 'hot'), (3, 'recommend'), (4, 'international'); ALTER TABLE `article` ADD PRIMARY KEY (`id`); ALTER TABLE `article_type` ADD PRIMARY KEY (`id`);
總結:
所以如果list是常量,則可以直接用IN, 否則要用find_in_set()函數。
參考資料:
http://www.111cn.net/database/mysql/50190.htm mysql数据库中find_in_set()和in()用法区别
沒有留言:
張貼留言