Mysql Append table to add columns

Question

I like to append a table to add column but without using alert table command

e.g.

This is the table which is missing some columns.

CREATE TABLE IF NOT EXISTS `admin` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(20) NOT NULL,
  `passwd` varchar(40) NOT NULL,
  `isActive` tinyint(1) NOT NULL default '1',
  `lastVisit` datetime NOT NULL default '0000-00-00 00:00:00',
  `modifyAt` datetime NOT NULL,
  `createdAt` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

So if i run this query then it should automatically add missing columns into my tables

CREATE TABLE IF NOT EXISTS `admin` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(20) NOT NULL,
  `passwd` varchar(40) NOT NULL,
  `name` varchar(100) NOT NULL,
  `originalUser` tinyint(1) NOT NULL default '0',
  `isActive` tinyint(1) NOT NULL default '1',
  `lastVisit` datetime NOT NULL default '0000-00-00 00:00:00',
  `modifyAt` datetime NOT NULL,
  `createdAt` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Can this be possible to do without using alert table command ?

Bjoern · Accepted Answer · 2013-06-08 17:13:31Z

2

I understand your question as you want to add some columns to your table. Please be informed that the term row is usually related to the actual data in your table, not the columns itself. If my assumption is wrong, please clarify your question.

You cannot use CREATE TABLE for altering a table. It is there to create table, and if it cannot create it, it will in most cases throw an error like you described. Another command exists for that reason: ALTER TABLE.

You might do it something like this.

(1) Create your table with your CREATE TABLE syntax above:

CREATE TABLE IF NOT EXISTS `admin` (
  `id` int(11) NOT NULL auto_increment,
  `username` varchar(20) NOT NULL,
  `passwd` varchar(40) NOT NULL,
  `isActive` tinyint(1) NOT NULL default '1',
  `lastVisit` datetime NOT NULL default '0000-00-00 00:00:00',
  `modifyAt` datetime NOT NULL,
  `createdAt` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

(2) Use ALTER TABLE like this to make the modifications I think you want to have in your second statement (two more columns):

ALTER TABLE
ADD COLUMN `name` varchar(100) NOT NULL AFTER `passwd`,
ADD COLUMN `originalUser` tinyint(1) NOT NULL default '0' AFTER `name`;

Not related to your question, but I'd avoid column names like name, because if you don't escape them properly it'll throw you other errors (see reserved words).

answered Jun 8, 2013 at 17:13

Bjoern

16.3k4 gold badges47 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user1967132 Over a year ago

I have a database with lot of tables where I add new columns but they are too many to go through each one of them to add it as alert table. i was hoping i can import the .sql file into database and it can think that these column are missing so i can add them in for you. what about somthing like replace table command

ypercubeᵀᴹ Over a year ago

How many are too many? You can use dynamic SQL I guess, to go through all tables in a database and do some actions (like ALTER TABLE.)

Bjoern Over a year ago

If you're missing columns, you have to add them like I described. There is no replace table command.

user1967132 Over a year ago

thank you for all this information i was just wondering if this can be possible.

Collectives™ on Stack Overflow

Mysql Append table to add columns

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related