I need to run an installer which can also be an updater. The installer needs to be able to end up having a certain scheme/structure of the mysql database, regardless if some of the tables existed, missed a few columns, or need not to be changed because their structure is up to date.
How can I make an elegant combination of ALTER
and CREATE
?
I was thinking there must be something like "ADD... IF... Duplicate"
Say I have table A. In one client the table has one column -A1, and another client has the same table but with column A1 and column A2.
I want my sql command to make both clients' table A hold three columns : A1, A2, and A3.
Again, my script is a sql file that I dump to mysql.
How do I do it? Thanks :-)
When you try to create a table with an already existing table name, you will receive an error message, and no table will be modified or created.
MySQL INFORMATION_SCHEMA
database to the rescue:
-- First check if the table exists IF EXISTS(SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'db_name' AND table_name LIKE 'wild') -- If exists, retreive columns information from that table THEN SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name' AND table_schema = 'db_name'; -- do some action, i.e. ALTER TABLE if some columns are missing ALTER TABLE ... -- Table does not exist, create a new table ELSE CREATE TABLE .... END IF;
More information:
UPDATE:
Another option, possibly easier, is to drop the existing table and re-create it again with the new schema. To do this, you need:
So, in SQL code:
CREATE TABLE old_table_copy LIKE old_table; INSERT INTO old_table_copy SELECT * FROM old_table; DROP TABLE old_table; CREATE TABLE new_table (...new values...); INSERT INTO new_table ([... column names from old table ...]) SELECT [...column names from old table ...] FROM old_table_copy; DROP TABLE old_table_copy;
Actually the last step, "Drop temporary table.", you could skip for a while. Just in case, you would want to have some sort of backup of the old table, "just-in-case".
More information:
Well if you are using a coding language do:
SHOW TABLES LIKE 'myTable'
if it return a value call the alter else call the create
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With