Create code to update Schema

1

I use Mysql WorkBench to manipulate my MySQL database and tables.

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

    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;

Checks whether the field already exists, and if not, create the field in the table. So when I make changes to my local database, it becomes easier to update on the clients' banks, thus avoiding upgrade failures.

    
asked by anonymous 02.04.2017 / 01:45

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'll be waiting before selecting this answer.

    
02.04.2017 / 17:10