問題描述
插入新元素時“LIMIT OFFSET”是否穩定？ (Is 'LIMIT OFFSET' stable when new element inserted?)
讓我們考慮一個場景。
一個表有1000行。
我查詢了LIMIT 100的行。
查詢後，一些插入表中的行。
然後，我再次查詢，使用 LIMIT 100 和 OFFSET 100。
我希望結果與第一次查詢無關。
我想在第一次查詢後得到結果。
我該怎麼做？請幫忙。
參考解法
方法 1:
The SQL standard makes no promise regarding the natural ordering of rows in a table ‑ new rows may be inserted at the end of a table or anywhere in the middle. Depending on where your dbms inserted the rows, which will likely depend on what rows were previously deleted, your second query could easily select rows already selected in the first query, or skip some rows between queries.
The only way I can think to do what you want reliably is to select the entire table into a temporary table, or a cursor, and then read records 100 at a time from that second record set.
You could also perform all your queries within a single serializable transaction (which would hide any inserts or deletes performed by other users for the duration of your transaction) but even that would not be a foolproof solution.
(by Beom Soo Joung、Darwin von Corax)