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 ordering > a.ordering)
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錯誤###############

這個在匯出匯入資料時很可能會用到

insertinto 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