Better CRUDs with REPLACE INTO in MariaDB and MySQL

Many applications have dozens and sometimes even thousands of CRUD screens (Create, Read, Update, and Delete). A useful SQL trick to simplify these operations in MariaDB and MySQL is the REPLACE INTO statement. In this article I’ll show you how REPLACE INTO can help in CRUD operations, reducing the need for different SQL statements and hence simplifying the code and increasing maintainability.

How CRUDs are typically implemented

#

CRUD operations are the basic elements in database interactions:

• Create: Inserting new data into a table.
• Read: Querying data from a table.
• Update: Modifying existing data in a table.
• Delete: Removing data from a table.

Typically, saving new data involves the INSERT INTO statement, while updating data requires the UPDATE statement. This is usually handled by persistence frameworks like Hibernate in Java or SQLAlchemy in Python. However, more often than not, you’ll eventually might have to use SQL to implement CRUD operations. This could happen in small programs or scripts that don’t incorporate a full-fledged framework for database persistence. Or you might have to implement a requirement that requires more direct access to the underlying database by using native SQL. When this is the case, you’ll have to write (and maintain) different SQL statements for the Create and the Update operations in your CRUD.

Introducing REPLACE INTO

#

The REPLACE INTO statement in MariaDB offers a way to handle both insertions and updates with a single SQL command. The basic syntax is similar to INSERT INTO, but with an added twist: if a row with the specified primary key or unique index already exists, it is deleted and replaced with the new row which from the outside looks like an Update operation.

Syntax of REPLACE INTO

#

Here’s the basic syntax of a REPLACE INTO statement:

REPLACE INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

Example Use Case

#

Consider a table t with the following structure:

CREATE TABLE t (
    id INT PRIMARY KEY AUTO_INCREMENT,
    text VARCHAR(255)
);

To insert a new row into the table, you would typically use:

INSERT INTO t (text) VALUES ('One');

This statement adds a new row with the specified text, and the id column is auto-incremented.

To update an existing row, you would use:

UPDATE t SET text = 'One!!!' WHERE id = 1;

This statement modifies the text of the row where id equals 1.

With REPLACE INTO, you can achieve both insertion and update in a single statement:

REPLACE INTO t (id, text) VALUES (1, 'One!!!');

If a row with id 1 exists, it is deleted and replaced with the new data. If it doesn’t exist, a new row is created.

Advantages of Using REPLACE INTO

#

1. Simplified Code: Reduces the need to write separate INSERT and UPDATE statements.
2. Consistency: Ensures that the specified row is either inserted or updated without additional checks.
3. Flexibility: Useful in applications where the distinction between insertion and update is not clear or where the primary key is known.

Considerations and Caveats

#

While REPLACE INTO simplifies CRUD operations, it has some caveats:

• Performance: REPLACE INTO performs a delete operation followed by an insert, which can be slower than a straightforward update.
• Triggers: Triggers defined for DELETE and INSERT operations will be executed, which might not be desired in all cases.
• Auto-Increment Columns: If the primary key is an auto-increment column, the new row gets a new auto-incremented value, which might not be intended.

Conclusion

#

Using REPLACE INTO can simplify CRUD operations by combining insertion and updating into a single, versatile command. While it offers many benefits in terms of simplicity and code maintainability, it’s essential to consider its performance implications and behavior with triggers and auto-increment columns.

Now you have one more tool in your SQL arsenal.