CODEWITHSUNDEEP
mysqlsql
php_nub_qq
php_nub_qq

Reputation: 16015

Alter database based on create table statement

I have this table

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`) USING BTREE,
  FULLTEXT KEY `dgd` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8;

Then a newer version of the table is available 

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `password` varchar(250) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`) USING BTREE,
  FULLTEXT KEY `dgd` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8;

Is there any way to generate ALTER statements to adapt to the new changes? I want to be able to upgrade to the newer version automatically but keep table data.

I found this which seems to be an oracle equivalent of exactly what I'm looking for - CREATE OR REPLACE TABLE

Upvotes: 0

Views: 74

Answers (2)

vkrishna
vkrishna

Reputation: 804

The above answer definitely works, but it might lock your table and make your table unavailable if your table is too large. If you don't want downtime, you can use pt-online-schema to do this.

make a shell script out of the below script and execute it

    #!/bin/bash

    DBUSER=username
    DBPASS=password
    DBSCHEMA=db_name
    TABLE=table_name

    ALTER_SQL="modify column destination_url text;" #what you want to modify or add

    pt-online-schema-change \
            --alter "$ALTER_SQL" \
            --charset utf8 \
            --nocheck-replication-filters \
            --chunk-size 10000 \
            --critical-load Threads_running=10000 \
            --nodrop-new-table \
            --max-load Threads_running=50 \
            --progress time,60 \
            --recursion-method none \
            --statistics \
            --pid $HOST.pid \
            --host $HOST \
            --execute \
            u=$DBUSER,p=$DBPASS,D=$DBSCHEMA,t=$TABLE >> output.log 2>&1 &

Upvotes: 0

JanR
JanR

Reputation: 6132

You can alter tables after creation, in your case to create a new column:

ALTER TABLE users ADD COLUMN Name VARCHAR(250) NOT NULL

Upvotes: 1

Related Questions