Creating a mysql table if it does not exist, otherwise add missing columns?

Question

I have a PHP app that gets updated frequently and deployed to our users. The problem is that sometimes an update adds new tables or columns which the user's local table does not have.

How can one dynamically create a table it is missing, or add missing columns to an existing table? I'm looking to make this process semi-automated, since tables and columns will always change in the future.

Here's my code to create a table

CREATE TABLE `comments` (
  `comment_id` int(11) NOT NULL AUTO_INCREMENT,
  `content_id` int(11) DEFAULT '0',
  `body` text,
  `name` text,
  `creation_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `delta` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`comment_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

This is more of a comment than a suggestion .. but this is why I use `Laravel` framework -- Their database `migrations` via `artisan` make this seamless in a multi-developer situation. Because the database table creation, altering, etc etc happen in a file-based structure, it ends up being a *sort* of `versioning` — Zak, May 24 '18 at 18:45
You can use `CREATE TABLE IF NOT EXISTS` to table creation and/or use some procedure to check if columns exist and then add to table. Look this https://stackoverflow.com/a/29428841/1706450 — PiLHA, May 24 '18 at 18:56

score 0 · Answer 1 · answered May 24 '18 at 18:50

0

Put your code in one block and check from the data dictionary whether your table exists or not. If not, execute the DDL via dynamic SQL.

It will ensure that your program will not error out if table exists.

answered May 24 '18 at 18:50

Piyushkumar Kachhadiya

151
2
4

Uueerdo · Accepted Answer · 2018-05-29T16:36:27.290

Ideally, you should be deploying a script to update your standard schema when you deploy application updates that require those changes. But, such can be integrated into your application...

"Tables" in information_schema can be queried to find information on tables and columns; you can check whether they exist, and get their current parameters (data type of column for example). You can create a startup routine that your application calls to normalize/update the schema.

Pseudo-code:

If sometable not found in the information schema, create it
else 
  if it doesn't have some column, add the column
  if it doesn't have some other column, add the column
  if it doesn't have an index on x, add an index on x
  and so on...

Another option is to have a version table in your schema...

if version < 1 then run version1 upgrade script
if version < 2 then run version2 upgrade script
and so on ...
update version to last in current script (so updated script knows where to start)

Creating a mysql table if it does not exist, otherwise add missing columns?

2 Answers2