Create code to update Schema

Asked

Viewed 57 times

1

Use Mysql Workbench to manipulate my Mysql database and tables.

When I export the table, it generates a code similar to the code below:

    CREATE TABLE `entrada` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `idProduto` int(11) NOT NULL,
  `data` date NOT NULL,
  `quant` int(11) NOT NULL,
  `chave` varchar(100) NOT NULL,
  `online` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=303 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

What I want is to create a code +- like this:

if !idProduto then create column idProduto int not null;
if !data then create column data date not null;

Check if the field already exists and, if not, create it in the table. Thus, when I make modifications to my local database, it is easier to update in the customer databases, thus avoiding update failures.

mysql

1 answer

0


I found the following code:

IF NOT EXISTS( SELECT NULL
            FROM INFORMATION_SCHEMA.COLUMNS
           WHERE table_name = 'tablename'
             AND table_schema = 'db_name'
             AND column_name = 'columnname')  THEN

  ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0';

END IF;

If anyone knows of a more practical solution, I wait before selecting this answer.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.