Methods for tracking database schema changes in MySQL?

Whenever a table is present in a project with a single database, we can do database schema changes using schema version or migration. It aims to keep track of database schema changes or structural changes.

The table creation to keep track of schema changes.

mysql> create table SchemaDatabaseMethodDemo
   -> (
   -> `WhenTime` timestamp not null default CURRENT_TIMESTAMP,
   -> `TheKey` varchar(200) not null,
   -> `Version` varchar(200),
   -> primary key(`TheKey`)
   -> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.45 sec)

Inserting records into the table.

mysql> insert into SchemaDatabaseMethodDemo values(now(),'1001','version 5.6.12');
Query OK, 1 row affected (0.17 sec)

To display records.

mysql> select *from SchemaDatabaseMethodDemo;

The following is the output.

+---------------------+--------+----------------+
| WhenTime            | TheKey | Version        |
+---------------------+--------+----------------+
| 2018-10-29 14:21:47 | 1001   | version 5.6.12 |
+---------------------+--------+----------------+
1 row in set (0.00 sec)

Note − Suppose we are executing a SQL script or migration then we need to add a row in the above table as well with the help of the INSERT statement at the beginning or end of the scripts.

George John

Published on 23-Nov-2018 15:35:49