散亂格部落

前段時間收到了要求將現有的資料需要根據某些欄位限制可顯示的資料數，由於現有的搜尋是使用Native SQL，於是便使用以往的經驗加上Row_Number() OVER(PARTITION BY …)，同時也在本機試驗過成功得到期望的結果。但當這個更新部署到Server上時竟出現了
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY…..'
的Exception。
經過一番查詢(同事)後發現是MySQL Server的版本不同引致，本機使用的版本是8.0.19，而測試的是5.7.30。
(想要知道自己使用的是哪個版本可Execute SELECT version();)
根據官方 MySQL 8.0 Release Notes Function and Operator Notes Section提及：

Each of the following words now is a reserved word and cannot be used as an identifier without identifier quoting: CUME_DIST, DENSE_RANK, FIRST_VALUE, GROUPS, LAG, LAST_VALUE, LEAD, NTH_VALUE, NTILE, OVER, PERCENT_RANK, RANK, ROW_NUMBER, WINDOW.

證實ROW_NUMBER的Function只在8.0後的版本才支援。
為了重現ROW_NUMBER的功能，參考網上的相關資料得出以下的Query

SELECT @row_number:=CASE WHEN @key = key THEN @row_number + 1 ELSE 1 END as rowNum,
@key:=key key, …. FROM table ORDER BY key;

在這句Query中，key為每條資料可Group By的欄位，當Query fetch到一條Record時會先比較當前的key與上一條資料所得的@key是否相同，再跟據此條件設定@row_number是1/+1。然後再設定當前的key到@key中。
如此，我們便可得到相關資料的Row_number。

由於我們使用了Hibernate/JPQL的Query，加上帶入了部分WHERE Clause令當中的@row_number在每次搜尋後都未有重整為NULL，結果@row_number在經多次搜尋後一直加上。對此，我們可加上JOIN Statement令該Parameter設為我們所需的Value。

SELECT @row_number:=CASE WHEN @key = key THEN @row_number + 1 ELSE 1 END as rowNum,
@key:=key key, …. FROM table JOIN (SELECT @row_number:=1) ORDER BY key;

備註：在JPQL等，:= 前需要加上 \\ 才可Escape到SQL中運行。

參考：
MySQL 8.0 Release Notes
“row_number alternative in mysql” Code Answer
MySQL get row position in ORDER BY