how to get sequence from mysql in cluster env

If you have a cluster of application servers running and you would like to get a unique id across the cluster, the best way is to get the id from Database. Oracle has the sequence function, but MySQL does not have the function.

Here are several ways to work around the issue in MySQL.

Retrieving AUTO_INCREMENT column values using Statement.getGeneratedKeys()

   Statement stmt = null;
  ResultSet rs = null;

  try {

   //
   // Create a Statement instance that we can use for
   // 'normal' result sets assuming you have a
   // Connection 'conn' to a MySQL database already
   // available

   stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
                               java.sql.ResultSet.CONCUR_UPDATABLE);

   //
   // Issue the DDL queries for the table for this example
   //

   stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
   stmt.executeUpdate(
           "CREATE TABLE autoIncTutorial ("
           + "priKey INT NOT NULL AUTO_INCREMENT, "
           + "dataField VARCHAR(64), PRIMARY KEY (priKey))");

   //
   // Insert one row that will generate an AUTO INCREMENT
   // key in the 'priKey' field
   //

   stmt.executeUpdate(
           "INSERT INTO autoIncTutorial (dataField) "
           + "values ('Can I Get the Auto Increment Field?')",
           Statement.RETURN_GENERATED_KEYS);

   //
   // Example of using Statement.getGeneratedKeys()
   // to retrieve the value of an auto-increment
   // value
   //

   int autoIncKeyFromApi = -1;

   rs = stmt.getGeneratedKeys();

   if (rs.next()) {
       autoIncKeyFromApi = rs.getInt(1);
   } else {

       // throw an exception from here
   }

   rs.close();

   rs = null;

   System.out.println("Key returned from getGeneratedKeys():"
       + autoIncKeyFromApi);
} finally {

   if (rs != null) {
       try {
           rs.close();
       } catch (SQLException ex) {
           // ignore
       }
   }

   if (stmt != null) {
       try {
           stmt.close();
       } catch (SQLException ex) {
           // ignore
       }
   }

Retrieving AUTO_INCREMENT column values using
             SELECT LAST_INSERT_ID()
  Statement stmt = null;
  ResultSet rs = null;

  try {

   //
   // Create a Statement instance that we can use for
   // 'normal' result sets.

   stmt = conn.createStatement();

   //
   // Issue the DDL queries for the table for this example
   //

   stmt.executeUpdate("DROP TABLE IF EXISTS autoIncTutorial");
   stmt.executeUpdate(
           "CREATE TABLE autoIncTutorial ("
           + "priKey INT NOT NULL AUTO_INCREMENT, "
           + "dataField VARCHAR(64), PRIMARY KEY (priKey))");

   //
   // Insert one row that will generate an AUTO INCREMENT
   // key in the 'priKey' field
   //

   stmt.executeUpdate(
           "INSERT INTO autoIncTutorial (dataField) "
           + "values ('Can I Get the Auto Increment Field?')");

   //
   // Use the MySQL LAST_INSERT_ID()
   // function to do the same thing as getGeneratedKeys()
   //

   int autoIncKeyFromFunc = -1;
   rs = stmt.executeQuery("SELECT LAST_INSERT_ID()");

   if (rs.next()) {
       autoIncKeyFromFunc = rs.getInt(1);
   } else {
       // throw an exception from here
   }

   rs.close();

   System.out.println("Key returned from " +
                      "'SELECT LAST_INSERT_ID()': " +
                      autoIncKeyFromFunc);

} finally {

   if (rs != null) {
       try {
           rs.close();
       } catch (SQLException ex) {
           // ignore
       }
   }

   if (stmt != null) {
       try {
           stmt.close();
       } catch (SQLException ex) {
           // ignore
       }
   }
}
 
}

What happes if you need multiple sequences in your app. The you may create sequence talbe several columns.

update sequence set id1=