This article explains three MySQL INSERT variants—INSERT IGNORE, REPLACE INTO, and INSERT ... ON DUPLICATE KEY UPDATE—detailing their syntax, execution behavior, advantages, drawbacks, typical use cases, and practical comparison to help developers choose the most suitable method for handling duplicate or bulk data operations.
This article introduces the binary search tree (BST) data structure, explains its core concepts, provides Python implementations for node definition, search, insert, and delete operations, compares performance, and discusses practical applications and exercises.
This article investigates a deadlock that occurs when using INSERT … ON DUPLICATE KEY UPDATE in a multi‑threaded MySQL 8.0 environment, reproduces the issue, examines InnoDB lock logs, walks through the relevant source‑code call chain, and demonstrates how gap locks cause the deadlock.
MySQL 8 removes the VALUES(col) function, requiring developers to use an alias with INSERT INTO ... VALUES (...) AS alias and to reference columns via alias.col in ON DUPLICATE KEY UPDATE, while also changing AUTO_INCREMENT and auto‑update column behaviors.
This article examines how INSERT, REPLACE and INSERT … ON DUPLICATE KEY UPDATE statements can cause deadlocks in MySQL InnoDB under various scenarios, explains the underlying lock types such as GAP, NEXT‑KEY and INSERT‑INTENTION locks, and offers practical recommendations to avoid them.
This guide walks you through 13 practical MySQL techniques—from aggregating values with GROUP_CONCAT and measuring string length with CHAR_LENGTH, to locating substrings, replacing text, retrieving timestamps, bulk inserting data, handling duplicate keys, inspecting table structures, and using diagnostic commands like EXPLAIN and SHOW PROCESSLIST—complete with ready‑to‑run SQL examples and visual results.
This article walks through using the FunTester framework to benchmark MySQL insert and delete operations, showing Java code examples, test configurations, and JSON results that reveal a high QPS for inserts and a noticeable slowdown when deletions are performed.
Learn the essential MySQL Data Manipulation Language commands—INSERT, UPDATE, DELETE, and TRUNCATE—including syntax variations, best practices, and step‑by‑step command‑line examples that illustrate single‑row, batch, and conditional operations, plus key differences between delete and truncate.
This article examines MySQL's INSERT locking behavior, explains why phantom reads do not occur under REPEATABLE READ isolation, and walks through compiling the MySQL source, debugging lock acquisition, and the role of insert‑intention and gap locks using detailed code examples and execution traces.
This article compares MySQL's INSERT INTO and LOAD DATA INFILE statements, explains their syntax, shows practical examples, highlights when each should be used for bulk data loading, discusses error handling with IGNORE, and explains why LOAD DATA INFILE is typically faster.
This article explains how MySQL treats backslashes as escape characters in INSERT and SELECT queries, demonstrates the effect of different numbers of backslashes through practical tests, and clarifies the double‑escaping required when using LIKE patterns.
This article presents a detailed performance benchmark comparing MariaDB 10.4.12 and MySQL 8.0.19 on an i7 Windows 10 system, covering single and batch inserts, indexed and non-indexed queries, and analyzes speed and memory usage differences between the two databases.
This article experimentally demonstrates how increasing the number of secondary indexes on MySQL tables progressively degrades write operations—including INSERT, UPDATE, DELETE, and LOAD DATA—by measuring execution times across ten tables with one to ten indexes each.
This tutorial walks through MySQL join techniques—including WHERE, LEFT, and INNER joins—shows how to retrieve specific columns, and provides clear examples of inserting single or multiple rows, updating records, and deleting data, all illustrated with step‑by‑step SQL code and result screenshots.
This article explains how MySQL treats backslashes in INSERT and SELECT statements, demonstrates the resulting storage differences with practical examples, and clarifies the double‑escaping rules that developers must handle to avoid unexpected query results.
This article explains how to construct SQL INSERT statements for string, numeric, date, and boolean fields, demonstrates variable concatenation for dynamic values, and provides a comprehensive example that combines multiple data types into a single query.
This article explains the four frequently used MySQL data‑insertion statements—INSERT, INSERT SELECT, REPLACE INTO, and INSERT … ON DUPLICATE KEY UPDATE—detailing their syntax, options, behavior, and performance considerations for reliable database operations.
This article explains the complete disk‑write journey of a MySQL INSERT statement, detailing the roles and order of redo log, binlog, double‑write, insert buffer, and data files, and clarifying InnoDB’s buffer‑pool and flushing mechanisms.
An experiment using pt‑tools shows that a single MySQL INSERT triggers three fsync operations on the redo log and one fdatasync on the binlog, while highlighting that flush behavior can vary due to multiple internal mechanisms.
This article examines MySQL's insert locking behavior, including unique‑key checks, S Next‑Key locks, insert‑intention locks, gap locks, and the resulting deadlock scenarios, and provides practical recommendations for avoiding such lock conflicts in production environments.
This guide explains the factors that affect MySQL INSERT performance and presents a collection of practical techniques—including multi‑row INSERT, bulk_insert_buffer_size tuning, INSERT DELAYED, LOAD DATA INFILE, table creation with disabled keys, LOCK TABLES, and the proper use of IGNORE and ON DUPLICATE KEY UPDATE—to dramatically speed up large‑scale data loading.
This guide explains why MySQL INSERT operations can be slow, breaks down the time‑consuming factors, and provides a collection of practical techniques—such as multi‑row VALUES, LOAD DATA INFILE, table locking, and special modifiers like DELAYED, IGNORE, and ON DUPLICATE KEY UPDATE—to dramatically speed up bulk data insertion.
