问题描述
mysql如何按特定id排序我想讲数据按某些特定id排前面，怎么做到？SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `p`-- ----------------------------DROP TABLE IF EXISTS `p`;CREATE TABLE `p` ( `id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, `categories_id` int(11) default NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;-- ------------------------------ Records of p-- ----------------------------INSERT INTO `p` VALUES ('1', 'jimmy', '2');INSERT INTO `p` VALUES ('2', 'tina', '2');INSERT INTO `p` VALUES ('3', 'dd', '2');INSERT INTO `p` VALUES ('4', 'hello', '2');INSERT INTO `p` VALUES ('5', 'world', '2');INSERT INTO `p` VALUES ('6', 'slucky', '2');SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for `p_sort`-- ----------------------------DROP TABLE IF EXISTS `p_sort`;CREATE TABLE `p_sort` ( `categories_id` int(10) NOT NULL default '0', `best_sort_person_id` varchar(100) default NULL, PRIMARY KEY (`categories_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;-- ------------------------------ Records of p_sort-- ----------------------------INSERT INTO `p_sort` VALUES ('2', '2,5,1');
解决方案
select e.* from (select a.* from p a,p_sort b where a.categories_id = b.categories_id and find_in_set(a.id,b.best_sort_person_id) order by find_in_set(a.id,b.best_sort_person_id)) e unionselect a.* from p a,p_sort b where a.categories_id = b.categories_id and not find_in_set(a.id,b.best_sort_person_id)
解决方案二：
1、这种东西不建议用一条sql搞定 数据量多 因为肯定会存在not 所以有些肯定不走索引 性能肯定差2、在应用中完成2.1、先查p_sort及p_sort中最前边的2.2、查p 然后[2.1] + [2.2]-[2.1] 这种方式肯定走索引3、p_sort表 数据量大吗 如果大 不建议1,2,3这种 还是再写一个关系表（多一个顺序列）4、ENGINE=MyISAM 改成ENGINE=InnoDB 否则无事务