0x1 需要用到的MySQL函式
count():用來統計表中或陣列中記錄的一個函式 count(*)某表中所有的列
floor():返回小於等於該值的最大整數【向下取整】
rand(): 產生隨機數
0x2原理分析
接下來我們再分析其報錯的形成 原因:
先談group by 函式:
在表中再插入兩條資料,name值都為“bbb”:
mysql> INSERT INTO test VALUES("3","bbb");mysql> INSERT INTO test VALUES("4","bbb");
成功後表如下:
這時候我們使用group by 語句時,MySQL會將查詢結果分類彙總,重複的內容會合併為一項:
mysql> SELECT name FROM test GROUP BY name;
這時候再使用count()函式就可以對不同的條目計數:
mysql> SELECT count(*),name FROM test GROUP BY name;
如圖:aaa有一條,bbb有3條
其背後的實現原理如下:
在執行group by name語句時,MySQL會在內部建立一個虛擬表,用來儲存列的資料,表中會有一個group_key值作為表的主鍵,這裡的主鍵就是用來分類的name列中獲取,當查詢資料時,取資料庫資料,然後檢視虛擬表中存在不,不存在則插入新記錄
當讀取到第一行資料時,aaa不存在,將aaa放入主鍵列中,1放在id列中
然後繼續往下走,到了bbb,不存在,也放進去
往下執行,遇到多餘的bbb,已經有bbb存在,就彙總在一起,內部情況如下:
如下,最後在查詢的時候根據group by內部的實現方式返回分類後的結果:
當我們加上count(*)函式時,操作過程為:檢視虛擬表是否存在該主鍵值,不存在則插入新記錄,存在則count(*)欄位直接加1
這樣就能對上面的分類結果進行統計,然後將統計結果返回:
所以雙查詢報錯的關鍵就在這裡,主要的原因在於rand()函式在group by的過程中被觸發了多次,
0x3 報錯原理讓我們回看一下構造的報錯語句:
mysql> SELECT count(*),concat((SELECT database()),"~",floor(rand()*2))as a FROM test GROUP BY a;
執行前虛擬表為空:
當第一次執行時,group by 分組,其取的資料的是以a為別名的這條語句,假設這時的concat((SELECT database()),"~",floor(rand()*2))生成結果為sql_test~0,group就以sql_test~0查詢虛擬表,發現表中沒有該值的主鍵,於是將這條語句的結果插入到虛擬表中。
注意!是將這條語句的結果插入到虛擬表中,而不是將 sql_test~0 插入到虛擬表中,如下:
(將concat((SELECT database()),"~",floor(rand()*2)) 以a為別名,方便作圖)
由於虛擬表沒有內容,所以會將其插入到虛擬表中,這裡的插入過程中,由於插入的是a語句的結果,所以在插入時a語句中的rand()函式會再次執行,即插入的值可能為sql_test~0 也可能為 sql_test~1 ,這裡假設插入時a執行的結果為sql_test~0 :
所以上面的情況就是用sql_test~1這個結果查詢虛擬表,不存在該資料,於是插入虛擬表,插入時又運算一次,然後插入的值變成了sql_test~0,所以這就是主要的衝突,表中只有一條資料還好,即使查詢虛擬表的值和插入虛擬表的值不是同一個,但虛擬表也只生成一條記錄,不會出現問題。
然而當表的資料出現兩條以上的時候,第group by 在處理完第一條資料後會往下繼續處理第二條,於是第二條還會按第一條的處理方式進行:
於是就會報錯,報錯內容如下:
ERROR 1062 (23000): Duplicate entry 'sql_test~0' for key 'group_key'
如果第二次查詢和插入的結果都一致:就會有下面兩種情況:
都是sql_test~0:表裡已存在,該主鍵的count(*)值+1都是sql_test~1:表裡沒有,插入形成新的主鍵