Mysql获取最大自增ID（auto_increment）的五种方式及其特点

Mysql获取最大自增ID（auto_increment）的五种方式及其特点

目录：

在关系型数据库的表结构中，一般情况下，都会定义一个具有‘AUTO_INCREMENT’扩展属性的‘ID’字段，以确保数据表的每一条记录都有一个唯一标识。

　　而实际应用中，获取到最近最大的ID值是必修课之一，针对于该问题，实践整理如下：

1、新建测试数据表get_max_id

mysql>CREATETABLE `get_max_id` (   `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '业务主键',   `content` char(25) DEFAULT NULL COMMENT '业务内容',   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

2、未初始化表获取最大自增ID

创建完数据表之后，我们知道，表中的内容暂时为空，此时，查询max(id)获取到的内容将是NULL；

方式1 - max(id)：

该方式的优点是简单粗暴，直奔菊花，啊不是(￣m￣），直奔主题；

　　　　同时，它无视其它客户端连接（db_connection）的影响，可以直奔第3点位置；

mysql>select max(id) from get_max_id; +---------+ | max(id) | +---------+ |    NULL | +---------+row in set (0.00 sec)

方式2 - LAST_INSERT_ID()函数：

LAST_INERT_ID()，返回最后一个INSERT或 UPDATE 查询中， AUTO_INCREMENT列设置的第一个表的值。

　　　　这玩意儿的使用还是有些限制的：

　　　　　1、同一个Connection连接对象(同一客户端)中，SELECT的结果为最后一次INSERT的AUTO_INCREMENT属性列的ID。这句话的重点在于“同一个”，即其他连接的客户端不对其查询的结果造成影响。假设客户端A和B，表ta原自增ID为3，在A中插入记录后产生自增ID为4，在客户端A中通过该函数查询的结果为4，但在客户端B中查询的结果值仍为3；(已验证)

　　　　　2、与表无关，即假设ta表和tb表，向ta插入记录后，再向tb插入记录，结果值为tb的max(id)值；(已验证)

　　　　　3、使用非魔术方法（’magic’）来INSERT或UPDATE一条记录时，即使用非0/非NULL值作为插入的字段，则LAST_INSERT_ID()返回值不会发生变化；(已验证)

　　　　　4、同一条INSERT语句中，传入多个VALUES值，则LAST_INSERT_ID()返回值为该查询第一条记录的ID；(已验证)

　　　　 5、在进阶方面，可运用作分表ID的唯一性。

　　　　初始化查询的结果，得到的是0，这点和max(id)还是有区别的；

mysql>select LAST_INSERT_ID(); +------------------+ | LAST_INSERT_ID() | +------------------+ |                0 | +------------------+row in set (0.00 sec)

方式3 - 查看表状态show table status

该方式提供了当前DB（use db_name;）下每个表的基本信息；可以通过where条件获取到Auto_increment属性的值；

　　　　下述提供的结果值，为下一个自增ID的数值。

mysql> show table status where Name='get_max_id';+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+| Name       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options| Comment |+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+| get_max_id | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |  10485760 |              1 | 2015-04-20 11:49:07 | NULL        | NULL       | utf8_general_ci |     NULL |     |         |+------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+row in set (0.00 sec)

方式4 - information_schema.tables

提供关于数据库中的表（包括视图）的信息。详细描述了某个表属于哪个schema，表类型，表引擎等等信息；

　　　　下述提供的结果值，为下一个自增ID的数值。

mysql> select table_name, AUTO_INCREMENT from information_schema.tables where table_name="get_max_id";+------------+----------------+| table_name | AUTO_INCREMENT |+------------+----------------+| get_max_id |              1 |+------------+----------------+row in set (0.01 sec)

方式5 - @@IDENTITY全局变量

基础：以@@开头的变量为全局变量，而以@开头的变量为用户自定义的变量。

　　　　此处 @@IDENTITY表示最近一次向具有identity属性（auto_increment）的表INSERT数据时对应的自增列的值。此处得到的值是0。

　　　　1、类似于LAST_INSERT_ID()函数，该方式必须在同一个客户端内进行的INSERT与SELECT，且不受其他客户端影响；(已验证)

　　　　2、与表无关；(已验证)

　　　　3、非魔术方法插入不影响结果值；(已验证)

　　　　4、同一INSERT插入多条记录，取第一条记录的ID值为结果；(已验证)

mysql> select @@IDENTITY;+------------+| @@IDENTITY |+------------+|          0 |+------------+row in set (0.00 sec)

转载请注明来源： http://www.cnblogs.com/w3chen/p/4441512.html

转载请注明出处：http://www.cnblogs.com/w3chen/

转载请注明出处：http://www.cnblogs.com/w3chen/p/4441512.html

xml文件配置

<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" ><mapper namespace="com.demo.controller.mapper.TTableInfoMapper" >    <resultMap id="BaseResultMap" type="com.demo.models.TableInfo" >        <result column="auto_increment" property="autoIncrement" jdbcType="INTEGER" />        <result column="table_name" property="tableName" jdbcType="VARCHAR" />    </resultMap>    <select id="selectAutoIncrementByTableName" resultMap="BaseResultMap" >        select table_name, auto_increment            from information_schema.tables            where table_name=#{tableName,jdbcType=VARCHAR};    </select></mapper>

DAO Mapper interface

package com.demo.controller.mapper;import com.demo.models.TableInfo;/** * Created by jack Ma on 2017/1/12. */public interface  TTableInfoMapper  {    TableInfo  selectAutoIncrementByTableName(String tableName);}

Model

package com.demo.models;/** * Created by jack Ma on 2017/1/12. */public class TableInfo {    private String tableName;    private Integer autoIncrement;    public String getTableName() {        return tableName;    }    public void setTableName(String tableName) {        this.tableName = tableName;    }    public Integer getAutoIncrement() {        return autoIncrement;    }    public void setAutoIncrement(Integer autoIncrement) {        this.autoIncrement = autoIncrement;    }}

最后查询的tableInfo的自增长值就是下一次插入数据库的值

数据库自增主键可能产生的问题

在MySQL中经常会配置自增长属性的字段作为主键，特别是使用InnoDB存储引擎，

因为InnoDB的聚集索引的特性，使用自增长属性的字段当主键性能更好，但是使用自增主键也可能会带来一些问题。
举个例子，使用自增主键对数据库做分库分表，可能出现一些诸如主键重复等的问题，或者在数据库导入的时候，可能会因为主键出现一些问题。

主要业务表的主键应该配置一个合理的策略，尽量避免自增AUTO_INCREMENT。

针对主键自增可能产生的问题，下面这两篇文章有相关的讨论：

INNODB自增主键的一些问题
mysql自增列导致主键重复问题分析

针对主键增长方式的解决方案
来自知乎问题-高并发网站如何解决数据库主键自增的时候出现重复？

（1）设置主键自增为何不可取
这样的话，数据库本身是单点，不可拆库，因为id会重复。

（2）依赖数据库自增机制达到全局ID唯一
使用如下语句：
REPLACE INTO Tickets64 (stub) VALUES (‘a’);
SELECT LAST_INSERT_ID();
这样可以保证全局ID唯一，但这个Tickets64表依旧是个单点。

（3）依赖数据库自增机制达到全局ID唯一并消除单点
在2的基础上，部署两个（多个）数据库实例，
设置自增步长为2（多个则为实例数），即auto-increment-increment = 2
设置auto-increment-offset分别为1,2…..
这样第一台数据库服务器的自增id为 1 3 5 7 9
第二台为2 4 6 8 10

（4）解决每次请求全局ID都读库写库压力过大的问题
比如第一次启动业务服务，会请求一个唯一id为3559
如果是2、3的方法，则id为3559，这样每次都请求数据库，对数据库压力比较大
可以用3559 * 65536（举个例子，并不一定是65536）+ 内存自增变量来作为id
当内存自增变量到达65535时，从数据库重新获取一个自增id
这样即使有多台业务服务器，id也不会重复：
第一台 3559 * 65536 + 1,2,3…..65535
第二台 3560 * 65536 + 1,2,3…..65535
然后第一台到65535了，换一个数据库自增id，这时候可能是3561 * 65536 + 1,2,3….