Alter table if exists or create if doesn’t

by

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:

  1. Create temporary table, an exact copy of the existing table
  2. Populate the temporary table with the data from the old table
  3. Drop the old table
  4. Create the new table with new schema
  5. Populate the new table with the information from the temporary table
  6. Drop temporary table.

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:

More Related Contents:

  1. Create a temporary table in a SELECT statement without a separate CREATE TABLE
  2. add column to mysql table if it does not exist
  3. Mysql: Setup the format of DATETIME to ‘DD-MM-YYYY HH:MM:SS’ when creating a table
  4. MySQL: ALTER TABLE if column not exists
  5. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  6. Adding multiple columns AFTER a specific column in MySQL
  7. How can I modify the size of column in a MySQL table?
  8. Error renaming a column in MySQL
  9. ALTER TABLE to add a composite primary key
  10. How to add not null constraint to existing column in MySQL
  11. Alter MySQL table to add comments on columns
  12. Field ‘id’ doesn’t have a default value?
  13. Create an index on a huge MySQL production table without table locking
  14. MySQL: ALTER IGNORE TABLE ADD UNIQUE, what will be truncated?
  15. ALTER table – adding AUTOINCREMENT in MySQL
  16. How to change MySQL column definition?
  17. `MODIFY COLUMN` vs `CHANGE COLUMN`
  18. How do I add more members to my ENUM-type column in MySQL?
  19. Optimizing MySQL for ALTER TABLE of InnoDB
  20. Default sort-ordering in MySQL (ALTER TABLE … ORDER BY …;)
  21. Rank function in MySQL
  22. Include headers when using SELECT INTO OUTFILE?
  23. ERROR 1130 (HY000): Host ” is not allowed to connect to this MySQL server [duplicate]
  24. ON DUPLICATE KEY + AUTO INCREMENT issue mysql
  25. MySQL monthly Sale of last 12 months including months with no Sale
  26. How to fuzzy match email or telephone by Elasticsearch?
  27. Calculating a Moving Average MySQL?
  28. Check if a column exists in a table with SQL
  29. MySQL Recursive get all child from parent
  30. is it possible to call a sql script from a stored procedure in another sql script?

Leave a Comment