home

หน้าแรก
 Computer >> บทช่วยสอนคอมพิวเตอร์ >  >> การเขียนโปรแกรม >> MySQL

Stored Procedure ใช้ตัวแปรใน LIMIT ด้วย Prepared Statement

สร้างตารางตัวอย่าง (14 แถว)

mysql> CREATE TABLE LimitWithStoredProcedure (
    -> Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> Name VARCHAR(10)
    -> );
mysql> INSERT INTO LimitWithStoredProcedure(Name) VALUES
('John'), ('Chris'), ('Maxwell'), ('Bob'), ('David'), ('Carol'),
('James'), ('Jace'), ('Robert'), ('Mike'), ('Sam'), ('Peter'), ('Ramit'), ('Tony');

Stored Procedure ใช้ตัวแปรใน LIMIT

mysql> DELIMITER //
mysql> CREATE PROCEDURE Sp_limit(IN beg INTEGER, IN end INTEGER)
    -> BEGIN
    ->     PREPARE myStatement FROM "SELECT * FROM LimitWithStoredProcedure LIMIT ?, ?";
    ->     SET @beginning = beg;
    ->     SET @ending = end;
    ->     EXECUTE myStatement USING @beginning, @ending;
    ->     DEALLOCATE PREPARE myStatement;
    -> END //
Query OK, 0 rows affected (0.25 sec)
mysql> DELIMITER ;

เรียกใช้ Procedure

mysql> CALL Sp_limit(4, 7);

ผลลัพธ์ (ข้าม 4 แถวแรก แล้วเอา 7 แถว = แถวที่ 5-11):

+----+--------+
| Id | Name   |
+----+--------+
| 5  | David  |
| 6  | Carol  |
| 7  | James  |
| 8  | Jace   |
| 9  | Robert |
| 10 | Mike   |
| 11 | Sam    |
+----+--------+

คำอธิบาย

  • MySQL ไม่รองรับ ตัวแปรใน LIMIT โดยตรง: LIMIT @offset, @limit จะ error
  • ต้องใช้ Prepared Statement กับ placeholder ?
  • PREPARE สร้าง statement object
  • EXECUTE ... USING ผูกค่าตัวแปรกับ placeholder
  • DEALLOCATE PREPARE ปล่อยทรัพยากร

MySQL 8.0+ ใช้ CTE ได้ (ทางเลือก)

-- สำหรับ SELECT ธรรมดา ไม่ต้องใช้ procedure
WITH numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS rn FROM LimitWithStoredProcedure
)
SELECT * FROM numbered WHERE rn > 4 AND rn <= 11;

กรณีใช้งานจริง

Pagination แบบ dynamic ที่ offset และ limit มาจากพารามิเตอร์ของแอปพลิเคชัน