MySQL create or alter table

Question

I am using MySQL 5.1 for my database and I'm sending the commands via a Java program (JBDC). Is there a MySQL command for creating or altering a table? Let's say I have a following table:

+----------+----------+
| column_a | column_b |
+----------+----------+
| value_a  | value_b  |
+----------+----------+

Now I want to use a command, that would add a column "column_c" if it didn't exist. That would result in:

+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+
| value_a  | value_b  |          |
+----------+----------+----------+

If the table didn't exist, it would create a new table with specified columns:

+----------+----------+----------+
| column_a | column_b | column_c |
+----------+----------+----------+

And finally, if the table had columns that weren't specified in the command, it would leave them untouched.

There's no `ALTER TABLE IF` as far as I know, perhaps you should take a look at tools like "liquibase" or "flyway" — , Jan 05 '14 at 16:13
I don't think using those programs would be necessary for just a single command, @RC.. Is there no other way? — Limeth, Jan 05 '14 at 16:26
inspect database metadata and create the column if needed is possible — , Jan 05 '14 at 16:54

escull638 · Answer 1 · 2014-01-05T16:42:13.690

here is code in Java to create a table called Coffees:

/*Making the connection*/
try {//if any statements within the try block cause problems, rather than the program failing
         //an exception is thrown which will be caught in the catch block
        con = DriverManager.getConnection(url, "your username", "your password");

        //create a statement object
        stmt = con.createStatement();
        //supply the statement object with a string to execute                          
        stmt.executeUpdate("create table COFFEES (COF_NAME varchar(32), " +
                            "SUP_ID int, PRICE double, SALES int, TOTAL int, " +
                            "primary key(COF_NAME))");

        //close the statement and connection
        stmt.close();
        con.close();

    } catch(SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
    }

Explanation: -In this example the java program interacts with a database that is located on a server, so we have to firstly we set the url of where the server is located and also sign in username and password, you may not be using the same method that I used. -These need to be declared at the top of your java program:

String url = "jdbc:mysql://www.yoururlexample.co.uk"; 
Connection con;    
Statement stmt

Hopefully this helps, you will then be able to insert data into the database and execute queries.

Edit:

This can be used in the executeUpdate statement if you want a table to be created if none exists with the name "COFFEES":

create table if not exists COFFEES

I think you missed the question, I was asking for the command to use in MySQL, not for the Java connection. _Is there a command for creating or altering a table?_ — Limeth, Jan 05 '14 at 16:52
Hmm I'm not entirely sure, but shouldn't adding these to do what you wanted?: stmt.executeUpdate(CREATE TABLE IF NOT EXISTS .....); stmt.executeUpdate(ALTER TABLE .....); — escull638, Jan 05 '14 at 18:32

score 0 · Answer 2 · answered Jan 05 '14 at 16:46

0

/*Making the connection*/
try {
         //an exception is thrown which will be caught in the catch block
        con = DriverManager.getConnection("jdbc:mysql:exaple.com", "username", "pass");

        //create a statement object
        stmt = con.createStatement();
        //supply the statement object with a string to execute                          
        stmt.executeUpdate("ALTER TABLE table_name ADD column_name datatype");

        //close the statement and connection
        stmt.close();
        con.close();

    } catch(SQLException ex) {
        System.err.println("SQLException: " + ex.getMessage());
    }

I think that this is going to work for you.

answered Jan 05 '14 at 16:46

Thanos Pappas

167
1
1
9

I think you missed the question, I was asking for the command to use in MySQL, not for the Java connection. _Is there a command for creating or altering a table?_ – Limeth Jan 05 '14 at 17:01
yes the command is the executeUpadate. ALTER TABLE table_name ADD column_name datatype i hope this will work for you – Thanos Pappas Jan 05 '14 at 17:05
The problem is, that if the table doesn't exist, it won't create it. Also, there's no way in your example to detect missing columns. – Limeth Jan 05 '14 at 17:07
Please check these 2 links: http://stackoverflow.com/questions/6520999/create-table-if-not-exists-equivalent-in-sql-server http://stackoverflow.com/questions/8870802/add-a-column-to-a-table-if-it-does-not-already-exist – Thanos Pappas Jan 05 '14 at 17:16

score 0 · Answer 3 · answered Jan 05 '14 at 17:09

Something like that might be a solution (this need at least one record in the table to work):

package com.stackoverflow.so20935793;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class App {

    // complete this:
    private static final String JDBC_URL = ".....";
    private static final String JDBC_USER = ".....";
    private static final String JDBC_PASS = ".....";

    public static void main(final String[] args) {
        Connection con = null;
        PreparedStatement stm = null;
        ResultSet rs = null;

        try {
            con = DriverManager.getConnection(JDBC_URL, JDBC_USER, JDBC_PASS);
            stm = con.prepareStatement("SELECT * FROM your_table LIMIT 1");
            rs = stm.executeQuery();
            if (rs.next() && rs.getMetaData().getColumnCount() == 2) {
                // add your new column
            }
        } catch (final SQLException ex) {
            // handle exception
        } finally {
            closeQuietly(rs);
            closeQuietly(stm);
            closeQuietly(con);
        }
    }

    private static void closeQuietly(final AutoCloseable what) {
        if (what == null) {
            return;
        }

        try {
            what.close();
        } catch (final Exception ex) {
            // ignore
        }
    }
}

(not tested)

MySQL create or alter table

3 Answers3