我敢肯定，到目前為止，您已經(jīng)以多種方式弄錯(cuò)了。 而且您可能很快將無(wú)法正確處理。 那么，當(dāng)您可以實(shí)施業(yè)務(wù)邏輯時(shí)，為什么還要在SQL調(diào)整上浪費(fèi)您的寶貴時(shí)間呢？

讓我解釋…

直到最近的SQL：2008標(biāo)準(zhǔn) ，MySQL用戶才知道的LIMIT .. OFFSET被標(biāo)準(zhǔn)化為以下簡(jiǎn)單語(yǔ)句：

SELECT * FROM BOOK OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY

是。 關(guān)鍵字太多了。

SQL確實(shí)是一種非常冗長(zhǎng)的語(yǔ)言。 就個(gè)人而言，我們真的很喜歡MySQL / PostgreSQL的LIMIT .. OFFSET子句的簡(jiǎn)潔性，這就是為什么我們?yōu)閖OOQ DSL API選擇它的原因 。

在SQL中：

SELECT * FROM BOOK LIMIT 1 OFFSET 2

在jOOQ中：

select().from(BOOK).limit(1).offset(2);

現(xiàn)在，當(dāng)您是SQL框架供應(yīng)商時(shí)，或者在滾動(dòng)自己的內(nèi)部SQL抽象時(shí)，您可能會(huì)考慮標(biāo)準(zhǔn)化此簡(jiǎn)潔的小子句。 這是數(shù)據(jù)庫(kù)中固有支持偏移分頁(yè)的兩種口味：

-- MySQL, H2, HSQLDB, Postgres, and SQLite SELECT * FROM BOOK LIMIT 1 OFFSET 2-- CUBRID supports a MySQL variant of the -- LIMIT .. OFFSET clause SELECT * FROM BOOK LIMIT 2, 1-- Derby, SQL Server 2012, Oracle 12, SQL:2008 SELECT * FROM BOOK OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY-- Ingres. Eek, almost the standard. Almost! SELECT * FROM BOOK OFFSET 2 FETCH FIRST 1 ROWS ONLY-- Firebird SELECT * FROM BOOK ROWS 2 TO 3-- Sybase SQL Anywhere SELECT TOP 1 ROWS START AT 3 * FROM BOOK-- DB2 (without OFFSET) SELECT * FROM BOOK FETCH FIRST 1 ROWS ONLY-- Sybase ASE, SQL Server 2008 (without OFFSET) SELECT TOP 1 * FROM BOOK

到目前為止，一切都很好。 這些都可以處理。 一些數(shù)據(jù)庫(kù)將偏移量放在限制之前，另一些數(shù)據(jù)庫(kù)則將限制放在偏移量之前，并且T-SQL系列將整個(gè)TOP子句放在SELECT列表之前。 這很容易模仿。 現(xiàn)在呢：

• Oracle 11g及以下
• SQL Server 2008及更低版本
• 具有偏移量的DB2

（ 請(qǐng)注意，您可以在DB2中啟用各種替代語(yǔ)法 ）

當(dāng)您使用google搜索時(shí)，您會(huì)發(fā)現(xiàn)數(shù)百萬(wàn)種方法可以在那些較舊的數(shù)據(jù)庫(kù)中模擬OFFSET .. FETCH 。 最佳解決方案始終涉及：

• 在Oracle中使用帶有ROWNUM篩選的雙嵌套派生表
• 在SQL Server和DB2中使用帶有ROW_NUMBER()篩選的單嵌套派生表格

因此，您正在模仿它。

您認(rèn)為您會(huì)做對(duì)嗎？

讓我們來(lái)解決一些您可能沒(méi)有想到的問(wèn)題。

首先，Oracle。 甲骨文顯然想創(chuàng)建一個(gè)最大的供應(yīng)商鎖定，只有蘋果公司最近推出了Swift才超過(guò)了。 這就是為什么ROWNUM解決方案的性能最佳，甚至優(yōu)于基于SQL：2003標(biāo)準(zhǔn)窗口函數(shù)的解決方案的原因。 不相信嗎？ 閱讀有關(guān)Oracle偏移分頁(yè)性能的這篇非常有趣的文章 。

因此，Oracle中的最佳解決方案是：

-- PostgreSQL syntax: SELECT ID, TITLE FROM BOOK LIMIT 1 OFFSET 2-- Oracle equivalent: SELECT * FROM (SELECT b.*, ROWNUM rnFROM (SELECT ID, TITLEFROM BOOK) bWHERE ROWNUM <= 3 -- (1 + 2) ) WHERE rn > 2

那真的是等價(jià)的嗎？

當(dāng)然不是。 您正在選擇其他列，即rn列。 在大多數(shù)情況下，您可能并不在意，但是如果您想進(jìn)行有限的子查詢以與IN謂詞一起使用怎么辦？

-- PostgreSQL syntax: SELECT * FROM BOOK WHERE AUTHOR_ID IN (SELECT IDFROM AUTHORLIMIT 1 OFFSET 2 )-- Oracle equivalent: SELECT * FROM BOOK WHERE AUTHOR_ID IN (SELECT * -- Ouch. These are two columns!FROM (SELECT b.*, ROWNUM rnFROM (SELECT IDFROM AUTHOR) bWHERE ROWNUM <= 3)WHERE rn > 2 )

因此，如您所見，您將不得不執(zhí)行一些更復(fù)雜的SQL轉(zhuǎn)換。 如果您要手動(dòng)模擬LIMIT .. OFFSET ，則可以將ID列修補(bǔ)到子查詢中：

SELECT * FROM BOOK WHERE AUTHOR_ID IN (SELECT ID -- betterFROM (SELECT b.ID, ROWNUM rn -- betterFROM (SELECT IDFROM AUTHOR) bWHERE ROWNUM <= 3)WHERE rn > 2 )

所以，更像是吧？ 但是由于您并不是每次都手動(dòng)編寫此代碼，因此您將開始創(chuàng)建自己的漂亮的內(nèi)部SQL框架，該框架涵蓋到目前為止遇到的2-3個(gè)用例，對(duì)嗎？

你能行的。 因此，您將自動(dòng)regex-search-replace列名以產(chǎn)生上述內(nèi)容。

所以現(xiàn)在，對(duì)嗎？

當(dāng)然不是！ 因?yàn)槟梢栽陧敿?jí)SELECT包含不明確的列名，但不能在嵌套select中包含。 如果要這樣做：

-- PostgreSQL syntax: -- Perfectly valid repetition of two ID columns SELECT BOOK.ID, AUTHOR.ID FROM BOOK JOIN AUTHOR ON BOOK.AUTHOR_ID = AUTHOR.ID LIMIT 1 OFFSET 2-- Oracle equivalent: SELECT * FROM (SELECT b.*, ROWNUM rnFROM (-- Ouch! ORA-00918: column ambiguously definedSELECT BOOK.ID, AUTHOR.IDFROM BOOKJOIN AUTHORON BOOK.AUTHOR_ID = AUTHOR.ID) bWHERE ROWNUM <= 3 ) WHERE rn > 2

不。 而且，由于您有多個(gè)ID實(shí)例，因此手動(dòng)修補(bǔ)前面示例中的ID列的技巧不起作用。 并且將列重命名為隨機(jī)值是很麻煩的，因?yàn)槟约旱膬?nèi)部數(shù)據(jù)庫(kù)框架的用戶希望接收定義良好的列名稱。 即ID和… ID 。

因此，解決方案是將列重命名兩次。 在每個(gè)派生表中一次：

-- Oracle equivalent: -- Rename synthetic column names back to original SELECT c1 ID, c2 ID FROM (SELECT b.c1, b.c2, ROWNUM rnFROM (-- synthetic column names hereSELECT BOOK.ID c1, AUTHOR.ID c2FROM BOOKJOIN AUTHORON BOOK.AUTHOR_ID = AUTHOR.ID) bWHERE ROWNUM <= 3 ) WHERE rn > 2

但是現(xiàn)在，我們完成了嗎？

當(dāng)然不是！ 如果您將這樣的查詢加倍嵌套怎么辦？ 您是否考慮將ID列重命名為合成名稱，然后再重新命名？ ……讓我們留在這里，談?wù)撏耆煌氖虑?#xff1a;

SQL Server 2008是否可以使用相同的功能？

當(dāng)然不是！ 在SQL Server 2008中，最流行的方法是使用窗口函數(shù)。 即ROW_NUMBER() 。 因此，讓我們考慮：

-- PostgreSQL syntax: SELECT ID, TITLE FROM BOOK LIMIT 1 OFFSET 2-- SQL Server equivalent: SELECT b.* FROM (SELECT ID, TITLE, ROW_NUMBER() OVER (ORDER BY ID) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3

就這樣吧？

當(dāng)然不是！

好的，我們已經(jīng)遇到了這個(gè)問(wèn)題。 我們不應(yīng)該選擇* ，因?yàn)樵谖覀儗⑵溆米鱅N謂詞的子查詢的情況下，這會(huì)生成過(guò)多的列。 因此，讓我們考慮使用綜合列名稱的正確解決方案：

-- SQL Server equivalent: SELECT b.c1 ID, b.c2 TITLE FROM (SELECT ID c1, TITLE c2,ROW_NUMBER() OVER (ORDER BY ID) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3

但是現(xiàn)在我們明白了，對(duì)不對(duì)？

做出有根據(jù)的猜測(cè)： 不！

如果您在原始查詢中添加ORDER BY子句，會(huì)發(fā)生什么情況？

-- PostgreSQL syntax: SELECT ID, TITLE FROM BOOK ORDER BY SOME_COLUMN LIMIT 1 OFFSET 2-- Naive SQL Server equivalent: SELECT b.c1 ID, b.c2 TITLE FROM (SELECT ID c1, TITLE c2,ROW_NUMBER() OVER (ORDER BY ID) rnFROM BOOKORDER BY SOME_COLUMN ) b WHERE rn > 2 AND rn <= 3

現(xiàn)在，這在SQL Server中不起作用。 子查詢不允許具有ORDER BY子句，除非它們也具有TOP子句（或SQL Server 2012中的OFFSET .. FETCH子句）。

好的，我們可能可以使用TOP 100 PERCENT進(jìn)行調(diào)整，以使SQL Server滿意。

-- Better SQL Server equivalent: SELECT b.c1 ID, b.c2 TITLE FROM (SELECT TOP 100 PERCENTID c1, TITLE c2,ROW_NUMBER() OVER (ORDER BY ID) rnFROM BOOKORDER BY SOME_COLUMN ) b WHERE rn > 2 AND rn <= 3

現(xiàn)在，根據(jù)SQL Server，這是正確的SQL，盡管您不能保證在查詢執(zhí)行后派生表的順序?qū)⒗^續(xù)存在。 很可能是由于某種影響再次更改了順序。

如果要在外部查詢中按SOME_COLUMN進(jìn)行排序， SOME_COLUMN必須再次轉(zhuǎn)換SQL語(yǔ)句以添加另一個(gè)綜合列：

-- Better SQL Server equivalent: SELECT b.c1 ID, b.c2 TITLE FROM (SELECT TOP 100 PERCENTID c1, TITLE c2,SOME_COLUMN c99,ROW_NUMBER() OVER (ORDER BY ID) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3 ORDER BY b.c99

確實(shí)開始變得有點(diǎn)討厭。 讓我們猜一下是否：

這是正確的解決方案！

當(dāng)然不是！ 如果原始查詢中包含DISTINCT怎么辦？

-- PostgreSQL syntax: SELECT DISTINCT AUTHOR_ID FROM BOOK LIMIT 1 OFFSET 2-- Naive SQL Server equivalent: SELECT b.c1 AUTHOR_ID FROM (SELECT DISTINCT AUTHOR_ID c1,ROW_NUMBER() OVER (ORDER BY AUTHOR_ID) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3

現(xiàn)在，如果一位作家寫了幾本書，會(huì)發(fā)生什么？ 是的， DISTINCT關(guān)鍵字應(yīng)該刪除這些重復(fù)項(xiàng)，并且有效地，PostgreSQL查詢將首先正確刪除重復(fù)項(xiàng)，然后應(yīng)用LIMIT和OFFSET 。

但是， ROW_NUMBER()謂詞在 DISTINCT可以再次刪除它們之前總是生成不同的行號(hào)。 換句話說(shuō)， DISTINCT無(wú)效。

幸運(yùn)的是，我們可以使用以下巧妙的小技巧再次調(diào)整此SQL ：

-- Better SQL Server equivalent: SELECT b.c1 AUTHOR_ID FROM (SELECT DISTINCT AUTHOR_ID c1,DENSE_RANK() OVER (ORDER BY AUTHOR_ID) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3

在此處閱讀有關(guān)此技巧的更多信息：

SQL技巧：row_number（）是SELECT，而density_rank（）是SELECT DISTINCT 。

請(qǐng)注意， ORDER BY子句必須包含SELECT字段列表中的所有列。 顯然，這會(huì)將SELECT DISTINCT字段列表中的可接受列限制為窗口函數(shù)的ORDER BY子句中允許的列（例如，沒(méi)有其他窗口函數(shù)）。

我們當(dāng)然也可以嘗試使用通用表表達(dá)式來(lái)解決此問(wèn)題，或者我們考慮

另一個(gè)問(wèn)題？

當(dāng)然是！

您甚至不知道窗口函數(shù)的ORDER BY子句中的列應(yīng)該是什么？ 您是否剛剛隨機(jī)選擇了任何一欄？ 如果該列上沒(méi)有索引該怎么辦，您的窗口函數(shù)仍會(huì)執(zhí)行嗎？

當(dāng)原始的SELECT語(yǔ)句還具有ORDER BY子句時(shí)，答案很容易，那么您可能應(yīng)該采用該子句（如果適用，還要加上SELECT DISTINCT子句中的所有列）。

但是，如果您沒(méi)有任何ORDER BY子句怎么辦？

還有另一把戲！ 使用“常量”變量：

-- Better SQL Server equivalent: SELECT b.c1 AUTHOR_ID FROM (SELECT AUTHOR_ID c1,ROW_NUMBER() OVER (ORDER BY @@version) rnFROM BOOK ) b WHERE rn > 2 AND rn <= 3

是的，您需要使用一個(gè)變量，因?yàn)樵赟QL Server中的那些ORDER BY子句中不允許使用常量。 痛苦，我知道。

在此處閱讀有關(guān)此@@ version技巧的更多信息 。

我們完成了嗎？

可能不是！ 但是，我們可能已經(jīng)涵蓋了大約99％的常見案例和邊緣案例。 現(xiàn)在，我們可以睡個(gè)好覺(jué)了。

注意，所有這些SQL轉(zhuǎn)換都是在jOOQ中實(shí)現(xiàn)的。 jOOQ是唯一認(rèn)真對(duì)待SQL（帶有所有缺點(diǎn)和警告）的SQL抽象框架，對(duì)所有這些瘋狂行為進(jìn)行了標(biāo)準(zhǔn)化。

如開頭所述，使用jOOQ，您只需編寫：

// Don't worry about general emulation select().from(BOOK).limit(1).offset(2);// Don't worry about duplicate column names // in subselects select(BOOK.ID, AUTHOR.ID) .from(BOOK) .join(AUTHOR) .on(BOOK.AUTHOR_ID.eq(AUTHOR.ID)) .limit(1).offset(2);// Don't worry about invalid IN predicates select() .from(BOOK) .where(BOOK.AUTHOR_ID).in(select(AUTHOR.ID).from(AUTHOR).limit(1).offset(2) );// Don't worry about the ROW_NUMBER() vs. // DENSE_RANK() distinction selectDistinct(AUTHOR_ID).from(BOOK).limit(1).offset(2);

使用jOOQ，您可以像編寫PostgreSQL一樣出色地編寫Oracle SQL或Transact SQL！ ……而不必完全跳起SQL船 ，而是繼續(xù)使用JPA。

鍵集分頁(yè)

當(dāng)然，現(xiàn)在，如果您一直在閱讀我們的博客或我們的合作伙伴博客SQL Performance Explained ，那么現(xiàn)在您應(yīng)該知道，首先， OFFSET分頁(yè)通常是一個(gè)錯(cuò)誤的選擇。 您應(yīng)該知道，鍵集分頁(yè)幾乎總是優(yōu)于OFFSET分頁(yè)。

在此處，了解jOOQ如何使用SEEK子句原生支持鍵集分頁(yè) 。

翻譯自: https://www.javacodegeeks.com/2014/06/stop-trying-to-emulate-sql-offset-pagination-with-your-in-house-db-framework.html

總結(jié)

以上是生活随笔為你收集整理的停止尝试使用内部DB框架模拟SQL OFFSET分页！的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。