mysql_insert_id 线程安全_关于MySQL提供的Last_insert_id()函数使用的理解

Last_insert_id()是MYSQL提供的返回当前客户端最后一个insert或update查询中设置为AUTO_INCREMENT列的值

Last_insert_id()不受其他客户端影响，所以是线程安全的，当前客户端只能拿到当前客户端的最新值，不需加锁处理

mybatis解析配置文件，执行SQL，转换结果的过程：

SqlSessionBuilder --> SqlSessionFacotry --> SqlSession --> Executor --> StatementHandler --> ResultSetHandler

其中userGeneratedKeys="true" keyProperty="id"获取自增id是在执行SQL的这一步(StatementHandle)来做的：

类图：

可以看到，最终执行的SQL都是交给SimpleStatementHandler，PreparedStatementHandler，CallableStatementHandler中的某一个，这三个类都有一个构造方法， 里边都是执行super()，会调用到BaseStatementHandler的构造方法，如上所示，在此构造方法中会先生成自增主键(如果selectkey配置的是before)，然后得到SQL语句，并设置parameterHandler和resultSetHandler等。部分源码如下：

BaseStatementHandler:

1 protectedBaseStatementHandler(Executor executor, MappedStatement mappedStatement, Object parameterObject, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) {2 this.configuration =mappedStatement.getConfiguration();3 this.executor =executor;4 this.mappedStatement =mappedStatement;5 this.rowBounds =rowBounds;6

7 this.typeHandlerRegistry =configuration.getTypeHandlerRegistry();8 this.objectFactory =configuration.getObjectFactory();9

10 if (boundSql == null) { //issue #435, get the key before calculating the statement

11 generateKeys(parameterObject);12 boundSql =mappedStatement.getBoundSql(parameterObject);13 }14

15 this.boundSql =boundSql;16

17 this.parameterHandler =configuration.newParameterHandler(mappedStatement, parameterObject, boundSql);18 this.resultSetHandler =configuration.newResultSetHandler(executor, mappedStatement, rowBounds, parameterHandler, resultHandler, boundSql);19 }20

21 protected voidgenerateKeys(Object parameter) {22 KeyGenerator keyGenerator =mappedStatement.getKeyGenerator();23 ErrorContext.instance().store();24 keyGenerator.processBefore(executor, mappedStatement, null, parameter);25 ErrorContext.instance().recall();26 }

SimpleStatementHandler:

1 public intupdate(Statement statement)2 throwsSQLException {3 String sql =boundSql.getSql();4 Object parameterObject =boundSql.getParameterObject();5 KeyGenerator keyGenerator =mappedStatement.getKeyGenerator();6 introws;7 if (keyGenerator instanceofJdbc3KeyGenerator) { //使用Jdbc3KeyGenerator类型得到自增主键8 statement.execute(sql, Statement.RETURN_GENERATED_KEYS);9 rows =statement.getUpdateCount();10 keyGenerator.processAfter(executor, mappedStatement, statement, parameterObject);11 } else if (keyGenerator instanceofSelectKeyGenerator) { //使用selectKeyGenerator()， 对于MYSQL则使用 select LAST_INSERT_ID() 来实现获取自增主键ID12 statement.execute(sql);13 rows =statement.getUpdateCount();14 keyGenerator.processAfter(executor, mappedStatement, statement, parameterObject);15 } else{16 statement.execute(sql);17 rows =statement.getUpdateCount();18 }19 returnrows;20 }

1 public Listquery(Statement statement, ResultHandler resultHandler)2 throwsSQLException {3 String sql =boundSql.getSql();4 statement.execute(sql);5 return resultSetHandler.handleResultSets(statement);6 }

PreparedStatementHandler:

1 public int update(Statement statement) throwsSQLException {2 PreparedStatement ps =(PreparedStatement) statement;3 ps.execute();4 int rows =ps.getUpdateCount();5 Object parameterObject =boundSql.getParameterObject();6 KeyGenerator keyGenerator =mappedStatement.getKeyGenerator();7 keyGenerator.processAfter(executor, mappedStatement, ps, parameterObject);8 returnrows;9 }

1 public List query(Statement statement, ResultHandler resultHandler) throwsSQLException {2 PreparedStatement ps =(PreparedStatement) statement;3 ps.execute();4 return resultSetHandler. handleResultSets(ps);5 }

可以看到，对于自增主键的操作可能发生在执行SQL前，或者执行SQL后，主要通过KeyGenerator接口子类来实现,类图：

KeyGenerator接口只有两个方法，processBefore()是在执行SQL之前执行， processAfter()是在执行SQL语句之后执行

1. JDBC3KeyGenerator(返回表中自动生成的自增主键)

1 public voidprocessBefore(Executor executor, MappedStatement ms, Statement stmt, Object parameter) {2 //do nothing

3 }

1 public voidprocessAfter(Executor executor, MappedStatement ms, Statement stmt, Object parameter) {2 List parameters = new ArrayList();3 parameters.add(parameter);4 processBatch(ms, stmt, parameters);5 }6

7 public void processBatch(MappedStatement ms, Statement stmt, Listparameters) {8 ResultSet rs = null;9 try{10 rs = stmt.getGeneratedKeys();11 final Configuration configuration =ms.getConfiguration();12 final TypeHandlerRegistry typeHandlerRegistry =configuration.getTypeHandlerRegistry();13 final String[] keyProperties =ms.getKeyProperties();14 final ResultSetMetaData rsmd =rs.getMetaData();15 TypeHandler>[] typeHandlers = null;16 if (keyProperties != null && rsmd.getColumnCount() >=keyProperties.length) {17 for(Object parameter : parameters) {18 if (!rs.next()) break; //there should be one row for each statement (also one for each parameter)

19 final MetaObject metaParam =configuration.newMetaObject(parameter);20 if (typeHandlers == null) typeHandlers =getTypeHandlers(typeHandlerRegistry, metaParam, keyProperties);21 populateKeys(rs, metaParam, keyProperties, typeHandlers); //将生成的自增主键，可能是多个，设置到对象相对应的属性中22 }23 }24 } catch(Exception e) {25 throw new ExecutorException("Error getting generated key or setting result to parameter object. Cause: " +e, e);26 } finally{27 if (rs != null) {28 try{29 rs.close();30 } catch(Exception e) {31 //ignore

32 }33 }34 }35 }

2.selectKeyGenerator(一般在不支持自动生成自增主键的数据库如oracle里使用，可以在执行之前使用，可以在执行之后使用)

1 public voidprocessBefore(Executor executor, MappedStatement ms, Statement stmt, Object parameter) {2 if(executeBefore) {3 processGeneratedKeys(executor, ms, parameter);4 }5 }

1 public voidprocessAfter(Executor executor, MappedStatement ms, Statement stmt, Object parameter) {2 if (!executeBefore) {3 processGeneratedKeys(executor, ms, parameter);4 }5 }6

7 private voidprocessGeneratedKeys(Executor executor, MappedStatement ms, Object parameter) {8 try{9 if (parameter != null && keyStatement != null && keyStatement.getKeyProperties() != null) {10 String[] keyProperties =keyStatement.getKeyProperties();11 final Configuration configuration =ms.getConfiguration();12 final MetaObject metaParam =configuration.newMetaObject(parameter);13 if (keyProperties != null) {14 //Do not close keyExecutor.15 //The transaction will be closed by parent executor.

16 Executor keyExecutor = configuration.newExecutor(executor.getTransaction(), ExecutorType.SIMPLE);17 List values = keyExecutor.query(keyStatement, parameter, RowBounds.DEFAULT, Executor.NO_RESULT_HANDLER); //若是selectKey，则执行selectkey中的SQL语句查询，对于MySQL一般是select LAST_INSERT_ID()18 if (values.size() == 0) {19 throw new ExecutorException("SelectKey returned no data.");20 } else if (values.size() > 1) {21 throw new ExecutorException("SelectKey returned more than one value.");22 } else{23 MetaObject metaResult = configuration.newMetaObject(values.get(0));24 if (keyProperties.length == 1) {25 if (metaResult.hasGetter(keyProperties[0])) {26 setValue(metaParam, keyProperties[0], metaResult.getValue(keyProperties[0]));27 } else{28 //no getter for the property - maybe just a single value object29 //so try that

30 setValue(metaParam, keyProperties[0], values.get(0));31 }32 } else{33 handleMultipleProperties(keyProperties, metaParam, metaResult);34 }35 }36 }37 }38 } catch(ExecutorException e) {39 throwe;40 } catch(Exception e) {41 throw new ExecutorException("Error selecting key or setting result to parameter object. Cause: " +e, e);42 }43 }

从上变可以看出：

1. 不支持自动生成主键的数据库(如oracle等)，需在执行SQL语句前，先生成主键，然后再执行SQL语句插入记录

2. 需要在执行SQL语句之前生成自增主键的，只能使用SelectKeyGenerator，并且在配置时需要配置属性order="Before"

3.JDBC3KeyGenerator的processBefore()方法什么也不干，所以不能在不支持自动生成自增主键的数据库中使用， MySQL中一般是使用此KeyGenerator来处理主键

4.NoKeyGenerator里边是两个空方法