正文
【MySQL】MySQL的Sequence
小程序：扫一扫查出行【扫一扫了解最新限行尾号】
复制小程序
Oracle的Sequence用爽了，发现MySQL没有Sequence，那么，自己写一个呗。
> 最简单的实现
先建一个表存储当前值：
CREATE TABLE `t_sequence` (
`sequence_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '序列名称' ,
`value` int(11) NULL DEFAULT NULL COMMENT '当前值' ,
PRIMARY KEY (`sequence_name`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
;
获取当前值的函数：
CREATE DEFINER = `root`@`localhost` FUNCTION `currval`(sequence_name varchar(64))
RETURNS int(11)
BEGIN
declare current integer;
set current = 0;
select t.value into current from t_sequence t where t.sequence_name = sequence_name;
return current;
end;
获取下一个值：
CREATE DEFINER = `root`@`localhost` FUNCTION `nextval`(sequence_name varchar(64))
RETURNS int(11)
BEGIN
declare current integer;
set current = 0; update t_sequence t set t.value = t.value + 1 where t.sequence_name = sequence_name;
select t.value into current from t_sequence t where t.sequence_name = sequence_name; return current;
end;
写一个多线程程序测试下并发：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map; public class JDBCTools { public static void main(String[] args) throws Exception {
JDBCTools.execute("update t_balance t set t.balance = 300 where t.user_id = 2");
JDBCTools.query("select * from t_balance t");
} public static String HOST = "localhost";
public static String PORT = "3306";
public static String DATABASE_NAME = "demo";
public static String USER_NAME = "root";
public static String PASSWORD = "123456"; /**
* 获取数据库连接
* @return 数据库连接
*/
public static Connection getConn() throws Exception {
Class.forName("com.mysql.jdbc.Driver"); String url = "jdbc:mysql://" + HOST + ":" + PORT + "/" + DATABASE_NAME + "?user=" + USER_NAME + "&password=" + PASSWORD + "&useUnicode=true&characterEncoding=UTF8";
Connection connection = DriverManager.getConnection(url);
return connection;
} /**
* 关闭资源
*/
public static void closeResource(Connection conn, Statement st, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
// TODO 处理异常
e.printStackTrace();
}
}
if (st != null) {
try {
st.close();
} catch (SQLException e) {
// TODO 处理异常
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
// TODO 处理异常
e.printStackTrace();
}
}
} /**
* 查询SQL
* @param sql 查询语句
* @return 数据集合
* @throws SQLException
*/
public static List<Map<String, String>> query(String sql) throws Exception {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
List<Map<String, String>> resultList = null; try {
connection = JDBCTools.getConn(); statement = connection.createStatement();
resultSet = statement.executeQuery(sql); ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
int columnCount = resultSetMetaData.getColumnCount();
String[] columnNames = new String[columnCount + 1];
for (int i = 1; i <= columnCount; i++) {
columnNames[i] = resultSetMetaData.getColumnName(i);
} resultList = new ArrayList<Map<String, String>>();
Map<String, String> resultMap = new HashMap<String, String>();
resultSet.beforeFirst();
while (resultSet.next()) {
for (int i = 1; i <= columnCount; i++) {
resultMap.put(columnNames[i], resultSet.getString(i));
}
resultList.add(resultMap);
}
// System.out.println("成功查询数据库，查得数据：" + resultList);
} catch(Throwable t) {
// TODO 处理异常
t.printStackTrace();
} finally {
JDBCTools.closeResource(connection, statement, resultSet);
} return resultList;
} /**
* 执行SQL
* @param sql 执行的SQL
* @return 操作条数
*/
public static int execute(String sql) throws Exception {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
int num = 0; try {
connection = JDBCTools.getConn(); statement = connection.createStatement();
num = statement.executeUpdate(sql); } catch(Throwable t) {
// TODO 处理异常
t.printStackTrace();
} finally {
JDBCTools.closeResource(connection, statement, resultSet);
} return num;
} }
import java.util.List;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors; public class Call { public static ConcurrentHashMap<String, String> map = new ConcurrentHashMap<String, String>(); public static void main(String[] args) {
ExecutorService es = Executors.newCachedThreadPool(); for (int i = 0; i < 100; i++) {
es.execute(new Runnable() { @Override
public void run() {
String key = getSequence();
if (!map.contains(key)) {
System.out.println("put " + key);
map.put(key, key);
} else {
System.out.println(key + " is exists.");
}
}
});
} } public static String getSequence() {
List<Map<String, String>> list = null;
try {
list = JDBCTools.query("select nextval('user_code') as current");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String temp = list.get(0).get("current");
return temp;
} }
> 用排他锁的方式
上面的程序虽然测试并无发现问题（有可能测试程序写得不好），理论上高并发会出现重复值，用排他锁重写了一下，不过效率会比较低：
CREATE DEFINER = `root`@`localhost` FUNCTION `nextval_safe`(sequence_name varchar(64))
RETURNS int(11)
BEGIN
declare current integer;
set current = 0; select t.value into current from t_sequence t where t.sequence_name = sequence_name for update;
update t_sequence t set t.value = t.value + 1 where t.sequence_name = sequence_name;
set current = current + 1; return current;
end;
