分布式环境下生成唯一id

分布式环境下通过last_insert_id生成唯一id：

1、首先建一个表：

CREATE TABLE `t_sequence` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `table_name` varchar(255) DEFAULT NULL,
  `counter` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

package com.newnoa.framework.getprimaryid;

import javax.sql.DataSource;
import java.sql.*;
import java.util.concurrent.Executor;
import java.util.concurrent.Executors;

/**
 * 分布式环境下获取唯一性id
 */
public class SequenceUtil {


    /**
     * 分布式环境下获取唯一性id，
     * 限制条件：
     * 1、必须在同一个connection下
     * 2、如果多次插入，只能返回插入的第一条的id
     * @param dataSource
     * @param tableName
     * @return
     */
    public static  Integer getPrimaryId(DataSource dataSource,String tableName){
        Integer id  = null;
        try {
            //获取connection
            Connection connection = dataSource.getConnection();
            //先执行update，
            PreparedStatement statement =
                    connection.prepareStatement("update t_sequence set counter=last_insert_id(counter + 1) where table_name= ?");
            statement.setString(1,tableName);
            statement.execute();
            //接着执行select last_insert_id()
            statement = connection.prepareStatement("select last_insert_id()");
            //解析返回的ResultSet
            ResultSet resultSet = statement.executeQuery();
            if(resultSet != null){
                while (resultSet.next()){
                    id =  resultSet.getInt(1);
                }
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return id;
    }


    public static  void test(String tableName){
        try {
            //第一步：注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获得链接
            Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/demo?user=root&password=root&useSSL=false&serverTimezone=UTC");
            PreparedStatement statement =
                    connection.prepareStatement("update t_sequence set counter=last_insert_id(counter+1) where table_name= ?");
            statement.setString(1,tableName);
            statement.execute();
            statement = connection.prepareStatement("select last_insert_id()");
            ResultSet resultSet = statement.executeQuery();
            if(resultSet != null){
                while (resultSet.next()){
                    System.out.println(resultSet.getInt(1));
                }
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

这样在多线程下就能实现每次返回的id不会重复；