|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| ||||||||||||||||||||||||||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Codewalkers Forums Sponsor:
|
|
|
|
#1
October 8th, 2007, 09:19 PM
|
|||
|
|||
|
MySQL 5 - Alter table structure conditional adding columns if they dont already exist
Hi,
A bit of background first. I have a laptop that I use for development. This has php, mysql and apache installed and working. I have a separate production box that has the same installation. When I make changes to the database structure, I have to do it twice. Once on the development box and again later on the production box when the code is sent across. At the table level, I can do a conditional creation. The sql looks like CREATE TABLE IF NOT EXISTS `item_master` ( `our_pn` varchar(20) default '', `description` varchar(100) default '', etc etc If the table already exists and of course is full of data, I only want to add a new column. then I can do that. The SQL looks like:- ALTER TABLE `item_master` ADD `sell_price` VARCHAR( 10 ) NULL DEFAULT '0' AFTER `description` ; THE QUESTION IS What I would like to do is write some sql to only add the column if it does not exist. Something like this ALTER TABLE `item_master` if not exists 'sell_price' then ADD `sell_price` VARCHAR( 10 ) NULL DEFAULT '0' AFTER `supply` ; end if But of course it is not as simple as this or I would not be asking he question here. Thanks
|
|
#2
October 10th, 2007, 05:36 AM
|
||||
|
||||
|
it really won't matter. if you run an ALTER TABLE statement and try to add a column that already exists in the table you will get an error, specifically error number 1060. so where is the problem?
as a side note, how would this situation ever arise? presumedly you have essentially identical versions of your project on both machines with the only difference being the actual data in the database. assuming that your workflow is not bi-directional (which it definitely should not be) there should never be a case that the production server sees a change before the development server.
|
|
#3
October 10th, 2007, 05:45 PM
|
||||
|
||||
|
Clarifications
Quote:
Errors are dangerous things. The danger is that if I allow errors then I may get used to seeing errors and mis an important error. Quote:
As I make changes to scripts on the development box, I would like to build a SQL file that I can run on the production box when I am ready to do the migration. On the development box this sql script would be run many times as fields were added. For example, lets say I decide to add a field. so I add a line to the sql script and run it on the development box. The I decide to add a second field. I add this to the script. The sql script now as two add field commands in it. Just what I need for the production box but it will generate an error when I run it on the development box because the first added field already exists in the mysql table on the development box. Hope all of this makes sense. Frank
|
|
#4
October 10th, 2007, 06:09 PM
|
||||
|
||||
|
There are a couple of ways of getting a list of columns and then searching the results.
One would be a SHOW COLUMNS query. Another would be the php mysql_fetch_field() statement following a SELECT * query. If you are adding columns frequently, either you are not spending enough time during the planning and definition phase or you are doing something like storing same information in columns that should in fact be stored in rows in its own table.
|
|
#5
October 10th, 2007, 09:23 PM
|
||||
|
||||
|
first of all i agree with what cwf said about you not spending enough time or though processes if you are continually making schema modifications.
i disagree with your thoughts about errors because the error messages with their corresponding error codes can be very useful in working with a database. for example if i have a requirement that every email is only allowed to be used one time i will add a unique index on that column. then when inserting a record, if there is an error and that error corresponds to error code 1062, i know there was a conflict with a duplicated value in a contrained column. this is an added level of data integrity. so while it is important that you don't simply dismiss errors in general it is equally important that you learn to handle errors gracefully and leverage errors to make the most of your applications. the simple truth is errors are going to happen. in regards to your development method is sounds like you need to work on your patching processing. a patch should only contain changes to code/schema that are relevant to the patch. so previous patch changes should not be included in the current patch's changes.
|
|
#6
August 22nd, 2008, 12:41 AM
|
|||
|
|||
|
Conditionally add columns to a table only if they don't already exist
Here's your answer. Enjoy.
Code:
delimiter '//' CREATE PROCEDURE addcol() BEGIN IF NOT EXISTS( SELECT * FROM information_schema.COLUMNS WHERE COLUMN_NAME='new_column' AND TABLE_NAME='the_table' AND TABLE_SCHEMA='the_schema' ) THEN ALTER TABLE `the_schema`.`the_table` ADD COLUMN `new_column` bigint(20) unsigned NOT NULL default 1; END IF; END; // delimiter ';' CALL addcol(); DROP PROCEDURE addcol;
|
|
#7
March 9th, 2009, 09:53 AM
|
|||
|
|||
|
thanks
"cowtung" thank you very much abaut the code you wrote its so usefull
|
|
| Viewing: Codewalkers Forums > Other Technologies > Database Help > MySQL 5 - Alter table structure conditional adding columns if they dont already exist |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
