mysql小技巧[随时记录]

 1.###########如何得到上次查询的所得的总行数########

mysql > select SQL_CALC_FOUND_ROWS * from __table__ limit 0,10 ;
mysql > select found_rows();

如果总记录为100,限取10条,通过found_row函数还是可以得到总行数,因为加了SQL_CALC_FOUND_ROWS标识.

[注意]:found_rows()只能运行一次，如果需要保存行数，可追加全局session变量

mysql > select found_rows() into @__amount;
mysql > select @__amount;

2.#######sql的执行顺序##########

(8)  SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1)  FROM <left_table>
(3)    <join_type> JOIN <right_table>
(2)      ON <join_condition>
(4)  WHERE <where_condition>
(5)  GROUP BY <group_by_list>
(6)  WITH {CUBE | ROLLUP}
(7)  HAVING <having_condition>
(10) ORDER BY <order_by_list>

3.#######group by 数据不对应的问题#########

因为group by只取分组中默认排序的一行,所以以下sql语句是有问题的

select max(created_date) from abc group by name;

发现并不是created_date最大的那'一行'记录.

也就是虽然能得到最大的created_date值列,但是其它列的记录并不是在同一行数据内的

有两种解决方案

a.先排序,再group by

select*from (
 select*from abc orderby created_date desc
) a groupby a.name

b.相当于联表操作,很有技巧.

select*from abc a where exists
(
 select 1 from abc where created_date > a.created_date
) groupby a.name

4.#########取数据各个分类前N条记录的语句############

以下语句为取前5条的情况，同样适用于不同表的联表操作。

select * from `table` a where
5 >=(select count(*) from `table` where cid=a.cid and id > a.id)
orderby a.cid,a.ordering desc;

比如取文章+文章前2条评论

select a.*,c.* from article a left join comments c on a.id=c.a_id where 2 > (select count(*) from comments where a_id=c.a_id and comment_date > c.comment_date) ;

5.#########屏蔽duplicate key错误###############

这个在导出导入数据时很可能会用到

insert into module (module_id,module_pid,module_name)values(1,0,'abc'),(2,0,'New module')
on DUPLICATE key
update module_name=module_name

6.########order by rand()的优化###############

在某个老外站点发现，具体效率仍然需要验证。

假设需要取order by rand() limit 10的数据

SELECT * FROM ( 
 SELECT @cnt :=COUNT(*) +1, 
 @lim :=10 
 FROM 表名
) vars STRAIGHT_JOIN(
 SELECT r.*, 
 @lim :=@lim-1 
 FROM 表名 r 
 WHERE (@cnt :=@cnt-1) 
 AND RAND() <@lim/@cnt 
) i