This MySQL tutorial explains how to use the DELETE LIMIT statement in MySQL with syntax and examples.
Description
The MySQL DELETE LIMIT statement is used to delete records from a table in MySQL and limit the number of records deleted based on a limit value.
Syntax
The syntax for the DELETE LIMIT statement in MySQL is:
DELETE FROM table [WHERE conditions] [ORDER BY expression [ ASC | DESC ]] LIMIT row_count;Parameters or Arguments
tableThe table that you wish to delete records from.WHERE conditionsOptional. The conditions that must be met for the records to be deleted.ORDER BY expressionOptional. It is used in the DELETE LIMIT statement so that you can order the results and target those records that you wish to delete.LIMIT row_countIt specifies a limited number of rows in the result set to delete based on row_count. For example, LIMIT 10 would delete the first 10 rows matching the delete criteria. This is where sort order matters so be sure to use an ORDER BY clause appropriately.Note
- You do not need to list fields in the MySQL DELETE LIMIT statement since you are deleting the entire row from the table.
Example
Let's look at how to use a DELETE statement with a LIMIT clause in MySQL.
For example:
DELETE FROM contacts WHERE website = 'TechOnTheNet.com' ORDER BY contact_id DESC LIMIT 2;This DELETE LIMIT example would delete the first 2 records from the contacts table where the website is 'TechOnTheNet.com'. Note that the results are sorted by contact_id in descending order so this means that the 2 largest contact_id values will be deleted by the DELETE LIMIT statement.
If there are other records in the contacts table that have a website value of 'TechOnTheNet.com', they will not be deleted by the DELETE LIMIT statement in MySQL.
If we wanted to delete the smallest contact_id values instead of the largest two, we could change the sort order as follows:
DELETE FROM contacts WHERE website = 'TechOnTheNet.com' ORDER BY contact_id ASC LIMIT 2;Now the results would be sorted by contact_id in ascending order, so the first two smallest contact_id records that have a website of 'TechOnTheNet.com' would be deleted by this DELETE LIMIT statement. No other records would be affected.
Link //dev.mysql.com/doc/refman/5.7/en/column-count-limit.html
Row Size Limits
The maximum row size for a given table is determined by several factors:
The internal representation of a MySQL table has a maximum row size limit of 65,535 bytes, even if the storage engine is capable of supporting larger rows. BLOB and TEXT columns only contribute 9 to 12 bytes toward the row size limit because their contents are stored separately from the rest of the row.
The maximum row size for an InnoDB table, which applies to data stored locally within a database page, is slightly less than half a page for 4KB, 8KB, 16KB, and 32KB innodb_page_size settings. For example, the maximum row size is slightly less than 8KB for the default 16KB InnoDB page size. For 64KB pages, the maximum row size is slightly less than 16KB. See Section 15.8.8, “Limits on InnoDB Tables”.
If a row containing variable-length columns exceeds the InnoDB maximum row size, InnoDB selects variable-length columns for external off-page storage until the row fits within the InnoDB row size limit. The amount of data stored locally for variable-length columns that are stored off-page differs by row format. For more information, see Section 15.11, “InnoDB Row Storage and Row Formats”.
Different storage formats use different amounts of page header and trailer data, which affects the amount of storage available for rows.
For information about InnoDB row formats, see Section 15.11, “InnoDB Row Storage and Row Formats”, and Section 15.8.3, “Physical Row Structure of InnoDB Tables”.
For information about MyISAM storage formats, see Section 16.2.3, “MyISAM Table Storage Formats”.
//dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
Limit Data Selections From a MySQL Database
MySQL provides a LIMIT clause that is used to specify the number of records to return.
The LIMIT clause makes it easy to code multi page results or pagination with SQL, and is very useful on large tables. Returning a large number of records can impact on performance.
Assume we wish to select all records from 1 - 30 (inclusive) from a table called "Orders". The SQL query would then look like this:
$sql = "SELECT * FROM Orders LIMIT 30";
When the SQL query above is run, it will return the first 30 records.
What if we want to select records 16 - 25 (inclusive)?
Mysql also provides a way to handle this: by using OFFSET.
The SQL query below says "return only 10 records, start on record 16 (OFFSET 15)":
$sql = "SELECT * FROM Orders LIMIT 10 OFFSET 15";
You could also use a shorter syntax to achieve the same result:
$sql = "SELECT * FROM Orders LIMIT 15, 10";
Notice that the numbers are reversed when you use a comma.