Replicating a table with a single query

We were working on modernizing a legacy PHP application, and part of the gradual upgrade process involved making a second copy of a few tables in another database.

To make it even more complicated, those tables were actually managed by another application, so we couldn't fully control the schema.

How could we programmatically copy the schema of those tables? MySQL offers a CREATE TABLE ... LIKE statement, which allows us to do exactly what we needed:

CREATE TABLE IF NOT EXISTS new_table LIKE old_table;

I like adding IF NOT EXISTS just to prevent an error if the table is already there and you try to recreate it.

But this approach is simpler compared to having to first query the schema of the source table and then replicate that with a CREATE TABLE statement. Here's the documentation if you're curious to read more.

Here to help,

Joel

P.S. Do you have an aging PHP application that you wish was running on Laravel? We've made the transition numerous times, and we'd love to help you too.