Use mysql’s `replace into` safely!

MySQL

MySQL’s popular replace into destination_database.destination_table (fields) select fields from source_database.source_table; query can be very dangerous if not used properly.

Say, you have schema’s like:

create table source_table
(
  field1           bigint                             not null,
  field2           int                                not null,
  field3           varchar(50) default '0'            not null,
  primary key (field1, field2)
);
create table destination_table
(
  field1           bigint                             not null,
  field2           int                                not null,
  field3           varchar(50) default '0'            not null,
  primary key (field1, field2)
);

Now, if you want to replace some data from source table to the destination table, you can do something like:

replace into destination_database.destination_table select * from source_database.source_table where <some_condition>;

This will work perfectly fine. No issues at all.

But if you specify your columns while replacing (replacing only the desired fields), it’ll replace the unspecified columns with it’s default value.

Let’s see how!

replace into destination_database.destination_table (field1, field2) select field1, field2 from source_database.source_table where <some_condition>;

When you execute this, it’ll replace field3 with it’s default value for all the entries being replaced.

Here’s the current state for both the tables, source and destination.

source_table:
field1, field2, field3
10,20,1
15,25,0
20,30,1
25,35,2
destination_table:
field1, field2, field3
20,30,1
25,35,2

Now I want to replace field1 and field2 from source_table to destination_table with a given condition.

replace into destination_database.destination_table (field1, field2) select field1, field2 from source_database.source_table where field1 >= 20;

This will result in:

destination_table:
field1, field2, field3
20,30,0              # field3 modified
25,35,0              # field3 modified

You can see the change in field3. It got replaced with it’s default value.