Mysql in、exists、find_set_in
事例:www表、shop表
www表:
CREATE TABLE `www` ( `id` int(11) NOT NULL AUTO_INCREMENT, `year` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `season` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `amount` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
shop表:
CREATE TABLE `shop` ( `id` int(11) NOT NULL AUTO_INCREMENT, `company_id` int(11) DEFAULT NULL, `shop_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, `shop_pic` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
IN:
in通常是走索引的,当in后面的数据在数据表中超过30%及可能不走索引(全盘扫描)
#查询www表里的 年为1991、1992
select * from www where year in (1991,1992);
EXISTS:可以代替in
# select * from www where id = 1 条件作为bool值 true执行 false不执行
select * from www where exists (select * from www where id = 1);
FIND_IN_SET:允许在逗号分隔的字符串列表中查找指定字符串的位置
#FIND_IN_SET(查找的字段,所有字段);
#注意所有的字段是以逗号分割的 比如2,3,4
#完整版 find_in_set(id,(2,3,4))
#select * from shop where FIND_IN_SET(id,(2,3,4));
select * from shop where FIND_IN_SET(id,(select season from www where id = 1));
case when then ... else end:
#case when then ...else end #相当于程序中的 if else 或 switch case select name, (case when age>500 then '大于500' when age<500 then '小于500' else '等于500' end )age from test1;