Retrieve large selection by chunks in MySQL?
To retrieve a large selection in chunks, you need to use ORDER BY LIMIT. The syntax is as follows:
SELECT *FROM yourTableName ORDER BY yourColumnName LIMIT 0,10;
From the above syntax, you will get 10 rows from the table. In the above syntax, 0 represents the first row in the result set of the table, which means it is zero-based indexed. The second value of LIMIT represents the maximum number of rows that can be retrieved from the table.
If you want to get the rows after 10 to 30, then use the following syntax in LIMIT:
SELECT *FROM yourTableName ORDER BY yourColumnName LIMIT 10,20; //11 to 30
If you want another set of rows from 30 to 50, use LIMIT again :
SELECT *FROM yourTableName ORDER BY yourColumnName LIMIT 30 ,20; 31 to 50.
For this you need to use a temporary table. The syntax is as follows:
DROP TEMPORARY TABLE IF EXISTS yourTemporaryTableName; CREATE TEMPORARY TABLE yourTempTableName AS ( SELECT *FROM yourOriginalTableName ORDER BY LIMIT 0,100 );
If all records are in the temporary table, then use LIMIT as discussed above to get all records from the temporary table.
SELECT *FROM yourTemporaryTableName LIMIT 0,100; SELECT *FROM yourTemporaryTableName LIMIT 100,1000;
Now it's up to you to set the limit value. It is a good practice to delete temporary tables now. The query is as follows:
DROP TEMPORARY TABLE yourTemporaryTableName;
Let us demonstrate the above discussion. First create a table. The query to create the table is as follows:
mysql> create table getRecordsDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (1.68 sec)
Use the insert command to insert some records into the table. The query is as follows:
mysql> insert into getRecordsDemo values(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); Query OK, 738 rows affected (0.34 sec) Records: 738 Duplicates: 0 Warnings: 0
Now create a temporary table like the above table. The query to create the temporary table is as follows:
mysql> drop temporary table if exists TempRecord; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create temporary table TempRecord -> as -> ( -> select * from getRecordsDemo order by Id limit 0,738 -> ); Query OK, 738 rows affected (0.03 sec) Records: 738 Duplicates: 0 Warnings: 0
Now you can use the LIMIT clause to get the results in chunks.
Case 1: The following query is used to get some records from the temporary table 'TempRecord':
mysql> select *from TempRecord limit 0,10;
The following is the output:
+----+ | Id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | | 7 | | 8 | | 9 | | 10 | +----+ 10 rows in set (0.00 sec)
Case 2: Query as follows to obtain the next set of records:
mysql> select *from TempRecord limit 10,20; +----+ | Id | +----+ | 11 | | 12 | | 13 | | 14 | | 15 | | 16 | | 17 | | 18 | | 19 | | 20 | | 21 | | 22 | | 23 | | 24 | | 25 | | 26 | | 27 | | 28 | | 29 | | 30 | +----+ 20 rows in set (0.00 sec)
Case 3: Query as follows to obtain another set of records:
mysql> select *from TempRecord limit 30,20; +----+ | Id | +----+ | 31 | | 32 | | 33 | | 34 | | 35 | | 36 | | 37 | | 38 | | 39 | | 40 | | 41 | | 42 | | 43 | | 44 | | 45 | | 46 | | 47 | | 48 | | 49 | | 50 | +----+ 20 rows in set (0.00 sec)
The above is the detailed content of Retrieve large selection by chunks in MySQL?. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

This article explores optimizing MySQL memory usage in Docker. It discusses monitoring techniques (Docker stats, Performance Schema, external tools) and configuration strategies. These include Docker memory limits, swapping, and cgroups, alongside

This article addresses MySQL's "unable to open shared library" error. The issue stems from MySQL's inability to locate necessary shared libraries (.so/.dll files). Solutions involve verifying library installation via the system's package m

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

This article compares installing MySQL on Linux directly versus using Podman containers, with/without phpMyAdmin. It details installation steps for each method, emphasizing Podman's advantages in isolation, portability, and reproducibility, but also

This article provides a comprehensive overview of SQLite, a self-contained, serverless relational database. It details SQLite's advantages (simplicity, portability, ease of use) and disadvantages (concurrency limitations, scalability challenges). C

This guide demonstrates installing and managing multiple MySQL versions on macOS using Homebrew. It emphasizes using Homebrew to isolate installations, preventing conflicts. The article details installation, starting/stopping services, and best pra

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]
