MySQL จำกัด & OFFSET พร้อมตัวอย่าง

คีย์เวิร์ด LIMIT คืออะไรใน MySQL?

การขอ LIMIT คีย์เวิร์ดนี้จำกัดจำนวนแถวที่แสดงในผลลัพธ์การค้นหา สามารถใช้ได้กับคำสั่ง SELECT, UPDATE และ DELETE ดังนั้นจึงจำกัดจำนวนแถวที่การค้นหาอ่าน รวมถึงจำนวนแถวที่การเขียนข้อมูลมีผลด้วย

รูปแบบการใช้งานของคำสั่ง LIMIT มีดังนี้

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT N;

ที่นี่

• “เลือก {ชื่อฟิลด์ | *} จากชื่อตาราง” คือ คำสั่ง SELECT มีช่องที่เราต้องการส่งคืนในการสืบค้นของเรา
• “[เงื่อนไขอยู่ที่ไหน]” เป็นตัวเลือกเสริม แต่หากระบุไว้ จะเป็นการกำหนดตัวกรองสำหรับชุดผลลัพธ์ WHERE ข้อ ฟังก์ชันนี้จะถูกนำไปใช้ก่อนฟังก์ชัน LIMIT ดังนั้นการกรองจะเกิดขึ้นก่อน และขีดจำกัดจะถูกนำไปใช้กับส่วนที่เหลืออยู่
• “ลิมิต เอ็น” คือคำหลัก และ N N คือจำนวนใดๆ ที่เริ่มต้นจาก 0 การกำหนดค่า 0 เป็นค่าจำกัดจะไม่แสดงผลลัพธ์ใดๆ เลย การกำหนดค่าเช่น 5 จะแสดงผลลัพธ์ห้ารายการ หากตารางมีข้อมูลน้อยกว่า N ระบบจะส่งคืนข้อมูลทั้งหมดและไม่แสดงข้อผิดพลาดใดๆ

ไวยากรณ์นั้นสั้น แต่เหตุผลที่มันมีอยู่นั้นควรค่าแก่การกล่าวถึงก่อนที่จะยกตัวอย่าง

เหตุใดเราจึงควรใช้คีย์เวิร์ด LIMIT?

สมมติว่าเรากำลังพัฒนา...ping แอปพลิเคชันนี้ทำงานอยู่บนฐานข้อมูล myflixdb นักออกแบบระบบขอให้เราจำกัดจำนวนระเบียนที่แสดงบนหน้าเว็บไว้ที่ 20 รายการ เพื่อแก้ปัญหาการโหลดช้า เราจะพัฒนาระบบให้ตรงตามข้อกำหนดนี้ได้อย่างไร?

คำสั่ง LIMIT ช่วยจัดการสถานการณ์นี้ได้อย่างแม่นยำ แทนที่จะดึงข้อมูลทุกแถวเข้ามาในแอปพลิเคชันแล้วทิ้งส่วนใหญ่ไป การค้นหาจะส่งคืนข้อมูล 20 รายการต่อหน้า และฐานข้อมูลจะเป็นผู้ดำเนินการส่วนที่เหลือ ซึ่งมีข้อดีสามประการดังนี้

• ตอบสนองเร็วขึ้น: มีการอ่านข้อมูลจากดิสก์น้อยลง และมีการส่งข้อมูลผ่านเครือข่ายน้อยลง
• ลดการใช้หน่วยความจำ: แอปพลิเคชันนี้เก็บข้อมูลเพียงหน้าเดียว ไม่ใช่ทั้งตาราง
• เซเฟอร์เขียนว่า: ข้อจำกัดในการอัปเดตหรือ ลบ คำสั่งดังกล่าวจำกัดจำนวนแถวที่ข้อผิดพลาดอาจส่งผลกระทบได้

MySQL ตัวอย่างคำสั่ง LIMIT

ตัวอย่างด้านล่างนี้ทำงานกับตาราง members ของฐานข้อมูล myflixdb ตัวอย่างแรกจะส่งคืนสองแถวและไม่มีอะไรเพิ่มเติม

SELECT * FROM members LIMIT 2;

จากผลลัพธ์ข้างต้น แสดงให้เห็นว่ามีสมาชิกเพียงสองคนเท่านั้นที่ได้รับเลือกกลับมา

ดึงรายชื่อสมาชิกสิบ (10) คนจากฐานข้อมูล

สมมติว่าเราต้องการรายชื่อสมาชิก 10 คนแรกที่ลงทะเบียนจากฐานข้อมูล Myflix สคริปต์ด้านล่างนี้จะขอรายชื่อเหล่านั้น

SELECT * FROM members LIMIT 10;

เมื่อรันสคริปต์จะได้ผลลัพธ์ดังที่แสดงด้านล่าง

มีการแสดงผลข้อมูลเพียง 9 รายการ เนื่องจากค่า N ในเงื่อนไข LIMIT มากกว่าจำนวนระเบียนในตาราง การขอข้อมูล 9 แถวโดยตรงก็ให้ผลลัพธ์ชุดเดียวกัน

SELECT * FROM members LIMIT 9;

💡 เคล็ดลับ: คำสั่ง LIMIT จะเลือกแถวตามลำดับใดก็ตามที่เซิร์ฟเวอร์สร้างขึ้น เพิ่ม สั่งโดย เงื่อนไขนี้ใช้เมื่อใดก็ตามที่เอกลักษณ์ของแถวมีความสำคัญ มิฉะนั้น "สมาชิก 10 คนแรก" จะไม่รับประกันว่าจะหมายถึงบุคคล 9 คนเดียวกันสองครั้ง

การจำกัดจำนวนแถวเป็นส่วนแรกของฟีเจอร์นี้ การเลือกจุดเริ่มต้นของหน้าต่างเป็นส่วนที่สอง

การใช้ค่า OFFSET ในคำสั่ง LIMIT

การขอ OFFSET ค่า (value) มักใช้ร่วมกับคีย์เวิร์ด LIMIT โดยจะระบุว่าเซิร์ฟเวอร์จะเริ่มดึงข้อมูลจากแถวใด ดังนั้นแถวก่อนหน้านั้นจะถูกข้ามไป

สมมติว่าเราต้องการสมาชิกจำนวนจำกัด โดยเริ่มจากตรงกลางตาราง สคริปต์ด้านล่างจะเริ่มต้นที่แถวที่สองและจำกัดผลลัพธ์ไว้ที่สองรายการ

SELECT * FROM `members` LIMIT 1, 2;

ดำเนินการใน MySQL ม้านั่งทำงานของช่างเครื่อง เมื่อเปรียบเทียบกับ myflixdb จะได้ผลลัพธ์ดังต่อไปนี้

โปรดทราบว่าตรงนี้ ออฟเซ็ต = 1ดังนั้น แถวที่ 2 จึงเป็นแถวแรกที่แสดงผล และ ลิมิต = 2ดังนั้นจึงได้ผลลัพธ์กลับมาเพียง 2 รายการเท่านั้น

ในรูปแบบที่มีสองอาร์กิวเมนต์ ค่าออฟเซ็ตจะถูกเขียนไว้ก่อน แล้วตามด้วยจำนวนแถว ซึ่งอาจทำให้เกิดการสลับลำดับโดยไม่ตั้งใจได้ง่าย MySQL นอกจากนี้ยังยอมรับรูปแบบที่ชัดเจนซึ่งช่วยขจัดความกำกวม และเป็นรูปแบบที่ควรเลือกใช้ในโค้ดใหม่

SELECT * FROM `members` LIMIT 2 OFFSET 1;

ทั้งสองคำสั่งให้ผลลัพธ์เป็นสองแถวเหมือนกัน เมื่อเข้าใจค่าออฟเซ็ตแล้ว รูปแบบการแบ่งหน้าซึ่งหน้าจอแสดงรายการทุกหน้าใช้ก็จะอธิบายได้โดยตรงจากค่าออฟเซ็ตนั้น

วิธีการแบ่งหน้าผลลัพธ์การค้นหาด้วย LIMIT และ OFFSET

การแบ่งหน้าจะแบ่งชุดผลลัพธ์ขนาดใหญ่เป็นหน้าที่มีหมายเลขกำกับ และคำสั่ง LIMIT ร่วมกับ OFFSET คือกลไกที่ใช้ในการแบ่งหน้า ค่าสองค่าที่ขับเคลื่อนการร้องขอหน้าแต่ละครั้ง ได้แก่ ขนาดของหน้า ซึ่งเป็นจำนวนระเบียนที่แสดงบนหนึ่งหน้าจอ และหมายเลขหน้าที่ผู้ใช้ร้องขอ

ค่าชดเชยได้มาจากการใช้สูตรเดียวในการคำนวณ

-- OFFSET = page_size * (page_number - 1)
SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 0;   -- page 1

หน้าที่ 2 ยังคงใช้ขีดจำกัดเดิม แต่เลื่อนตำแหน่งไปข้างหน้าหนึ่งขนาดหน้า

SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 20;  -- page 2

สามกฎสำคัญที่จะช่วยให้การแสดงรายการแบบแบ่งหน้าถูกต้องและรวดเร็ว

1. เรียงลำดับเสมอ: การค้นหาแบบแบ่งหน้าโดยไม่ใช้ ORDER BY อาจแสดงเรคอร์ดเดียวกันในสองหน้าต่างกัน และซ่อนเรคอร์ดอื่นไว้อย่างสมบูรณ์ เนื่องจากเซิร์ฟเวอร์สามารถเปลี่ยนแปลงลำดับแถวระหว่างการเรียกใช้ได้
2. เรียงลำดับตามคอลัมน์ที่ไม่ซ้ำกัน: หากมีค่าเท่ากันในคอลัมน์ที่ใช้เรียงลำดับ ลำดับของแถวที่มีค่าเท่ากันจะไม่แน่นอน การเรียงลำดับโดยใช้คีย์หลัก หรือการเพิ่มคีย์หลักเป็นตัวตัดสิน จะช่วยแก้ปัญหานี้ได้
3. ดูรายละเอียดเพิ่มเติมในหน้าเว็บ: ชดเชย 100000 แรง MySQL อ่านข้อมูลหนึ่งแสนแถวแล้วทิ้งไป ก่อนจะส่งข้อมูลยี่สิบแถวถัดไปกลับมา เวลาตอบสนองจะเพิ่มขึ้นตามจำนวนหน้า

สำหรับการแบ่งหน้าแบบละเอียดมาก การแบ่งหน้าด้วยชุดคีย์จะหลีกเลี่ยงการใช้ค่าออฟเซ็ตโดยสิ้นเชิง แทนที่จะนับจำนวนแถวที่จะข้ามไป การค้นหาจะจดจำคีย์สุดท้ายจากหน้าก่อนหน้าและขอข้อมูลแถวถัดจากนั้น

SELECT membership_number, full_names
FROM members
WHERE membership_number > 20      -- last id from the previous page
ORDER BY membership_number ASC
LIMIT 20;

รูปแบบนี้ยังคงทำงานได้เร็วในทุกระดับความลึก เนื่องจากดัชนีจะกระโดดไปยังคีย์เริ่มต้นโดยตรง แทนที่จะไล่ดูแถวข้างหน้า ข้อเสียคือต้องไล่ดูหน้าต่างๆ ตามลำดับ ดังนั้นการกระโดดจึง...ping การไปยังหน้า 500 โดยตรงนั้นไม่สามารถทำได้อีกต่อไปแล้ว

ขีดจำกัดใน MySQL เทียบกับ TOP และ FETCH FIRST

คำสั่ง LIMIT ไม่ได้เป็นส่วนหนึ่งของภาษา SQL ทุกภาษา ซึ่งเป็นเรื่องสำคัญเมื่อต้องส่งคำสั่งค้นหาข้ามฐานข้อมูล MySQL, PostgreSQLและ SQLite ใช้คีย์เวิร์ด LIMIT ร่วมกัน SQL Server ใช้ TOP และ Oracle ใช้คำสั่ง FETCH FIRST มาตรฐาน ตารางด้านล่างเปรียบเทียบทั้งสามแบบ

พฤติกรรมจะเหมือนกันในแต่ละกรณี คือ จำกัดจำนวนแถว และอาจข้ามแถวจำนวนหนึ่งก่อนก็ได้ ต่างกันแค่การสะกดคำเท่านั้น ดังนั้น คำสั่งค้นหาที่ต้องทำงานบนหลายเครื่องควรแยกส่วนการจำกัดจำนวนแถวออกจากส่วนอื่นในโค้ด แทนที่จะกระจายไปทั่ว

คำถามที่พบบ่อย