Master MySQL Tricks: Upserts, Ignoring Errors, Joins, Pagination & More

Overview

The article revisits a previously created MySQL project and demonstrates several practical tricks that can improve data manipulation, query performance, and code simplicity when working with MySQL tables.

Insert Conflict Update (ON DUPLICATE KEY UPDATE)

When an INSERT fails due to a UNIQUE index conflict, MySQL’s ON DUPLICATE KEY UPDATE clause can automatically update the conflicting row instead of requiring a separate SELECT‑then‑UPDATE step.

INSERT INTO student(`no`,`name`) VALUES (3, "xiaoming");
INSERT INTO student(`no`,`name`) VALUES (1, "xiaoming"), (2, "xiaohong")
ON DUPLICATE KEY UPDATE `no` = VALUES(`no`);

This syntax updates the no column with the value supplied in the INSERT statement for any row that triggers a duplicate‑key error.

Ignore Errors in Batch Insert (INSERT IGNORE)

To continue a bulk insert even when some rows violate constraints, prepend IGNORE to the INSERT statement. Rows that cause errors are skipped while the rest are inserted.

INSERT INTO student(`no`,`name`) VALUES (1, "xiaoming");
INSERT IGNORE INTO student(`no`,`name`) VALUES 
  (1, "xiaoming"), (2, "xiaohong"), (3, "xiaowang");

Note: Using IGNORE can hide data‑quality problems; apply it only when the ignored rows are truly unimportant.

Replace Subqueries with JOIN

Subqueries in the WHERE … IN form can be inefficient because MySQL may execute the subquery for each outer row. Rewriting the logic with a JOIN often yields better performance.

SELECT * FROM student
WHERE no > (
  SELECT no FROM student WHERE `name`='xiaoming'
);

SELECT s.* FROM student s
JOIN (
  SELECT no FROM student WHERE `name`='xiaoming'
) t ON s.no > t.no;

LEFT JOIN Condition Pitfalls

When using LEFT JOIN, conditions placed in the ON clause do not filter rows from the left table; they only affect matching rows from the right table. To filter the result set, move the condition to a WHERE clause.

-- Condition in ON (does not filter left rows)
SELECT c.*, s.`name`
FROM class c
LEFT JOIN student s ON c.no = s.cls_no AND c.no < 4
ORDER BY c.no ASC;

-- Correct filtering with WHERE
SELECT c.*, s.`name`
FROM class c
LEFT JOIN student s ON c.no = s.cls_no
WHERE c.no < 4
ORDER BY c.no ASC;

The first query still returns rows where c.no = 4 because the left table rows are preserved regardless of the ON condition.

Pagination Query Optimization

Traditional pagination using LIMIT offset, count forces MySQL to scan and discard the preceding offset rows, which becomes costly for large offsets. A more efficient approach is to remember the last retrieved id and query based on that value.

-- Inefficient offset‑based pagination
SELECT * FROM student WHERE cls_no > 1
LIMIT 1000, 10 ORDER BY id;

-- Efficient key‑set pagination (client supplies last id)
SELECT * FROM student
WHERE id > 1000 AND cls_no > 1
LIMIT 10 ORDER BY id;

If IDs are not continuous, the key‑set method can be combined with a subquery that fetches the next batch of IDs via a JOIN:

SELECT s.* FROM student s
JOIN (
  SELECT id FROM student LIMIT 1000, 10
) t ON s.id = t.id;

This technique is useful only when the subquery can be executed without additional WHERE filters; otherwise it offers little benefit.

UPDATE/DELETE Across Multiple Tables Using JOIN

MySQL allows a single statement to modify rows in several related tables by joining them in the UPDATE or DELETE clause. The example moves all students from class 3 to class 5.

UPDATE student s
JOIN class c ON c.no = 3 AND c.no = s.cls_no
SET c.no = 5, s.cls_no = 5;

COUNT(*) vs COUNT(1) vs COUNT(column)

SELECT COUNT(*) follows the SQL‑92 standard and counts all rows, including those with NULL values in any column.

SELECT COUNT(1) also counts rows; MySQL implements it the same way as COUNT(*) , so there is no performance difference.

SELECT COUNT(col) counts only rows where col is not NULL.

MySQL’s documentation confirms that COUNT(*) and COUNT(1) have identical execution plans; prefer the standard COUNT(*) for readability.

Test Data Setup

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `no` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '编号',
  `name` varchar(30) NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_no` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `class` (
  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `no` int(10) unsigned NOT NULL COMMENT '编号',
  `name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_no` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `student`
  ADD COLUMN `cls_no` smallint(6) unsigned NOT NULL DEFAULT 0 AFTER `no`;

Result Illustrations

Below are screenshots of the query results for the LEFT JOIN examples.