mysql 获取自增值

1. select max(id) from tablename

2.SELECT LAST_INSERT_ID() 函数

LAST_INSERT_ID 是与table无关的,如果向表a插入数据后,再向表b插入数据,LAST_INSERT_ID会改变。

在多用户交替插入数据的情况下max(id)显然不能用。这时就该使用LAST_INSERT_ID了,因为LAST_INSERT_ID是基于Connection的,只要每个线程都使用独立的 Connection对象,LAST_INSERT_ID函数将返回该Connection对AUTO_INCREMENT列最新的insert or update 操作生成的第一个record的ID。这个值不能被其它客户端(Connection)影响,保证了你能够找回自己的 ID 而不用担心其它客户端的活动,而且不需要加锁。使用单INSERT语句插入多条记录, LAST_INSERT_ID返回一个列表。

3. select @@IDENTITY;

@@identity 是表示的是最近一次向具有identity属性(即自增列)的表插入数据时对应的自增列的值,是系统定义的全局变量。一般系统定义的全局变量都是以@@开头,用户自定义变量以@开头。

比如有个表A,它的自增列是id,当向A表插入一行数据后,如果插入数据后自增列的值自动增加至101,则通过select @@identity得到的值就是101。使用@@identity的前提是在进行insert操作后,执行select @@identity的时候连接没有关闭,否则得到的将是NULL值。

这个语句很特别,没有关联到特定的SQL语句,会 让人感觉迷糊,他到底是怎么获取值的。在并发情况下会不会获取其他线程执行后的值。

答案是有可能的,但是不用怕、是可控的。只有不当的编码才会导致取到其他线程的值。先来说一下原理:

?
1
2
SUMMARY
The Jet OLE DB version 4.0 provider supports the SELECT @@Identity query that allows you to retrieve the value of the auto-increment field generated on your connection . Auto-increment values used on other connections to your database do not affect the results of this specialized query. This feature works with Jet 4.0 databases but not with older formats.

大致意思是【SELECT @@IDENTITY】获取的是当前数据库连接的前一次执行的值。其他连接执行的值不会影响当前线程。时下流行的框架(如Spring-jdbc、mybatis、hibernate)的数据库连接都是存在ThreadLocal中的、是线程隔离的,所以不会获取到其他线程中的【SELECT @@IDENTITY】值。当多线程 编程 时、强制把 数据库 连接传给各个线程同时执行时才会取到其他线程的【SELECT @@IDENTITY】。

2、在MySql中模拟Sequence

第一步:创建--Sequence 管理表

?
1
2
3
4
5
6
7
DROP TABLE IF EXISTS sequence
CREATE TABLE WFO_SEQ( 
          name VARCHAR (50) NOT NULL
          current_value INT NOT NULL
          increment INT NOT NULL DEFAULT 1, 
          PRIMARY KEY ( name
) ENGINE=InnoDB;
第二步:创建--取当前值的函数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DROP FUNCTION IF EXISTS currval; 
DELIMITER $ 
CREATE FUNCTION currval (seq_name VARCHAR (50)) 
          RETURNS INTEGER 
          LANGUAGE SQL 
          DETERMINISTIC 
          CONTAINS SQL 
          SQL SECURITY DEFINER 
          COMMENT '' 
BEGIN 
          DECLARE value INTEGER
          SET value = 0; 
          SELECT current_value INTO value 
                    FROM WFO_SEQ
                    WHERE name = seq_name; 
          RETURN value; 
END 
DELIMITER ;
第三步:创建--取下一个值的函数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
DROP FUNCTION IF EXISTS nextval;   
DELIMITER $   
CREATE FUNCTION nextval (seq_name VARCHAR (50))   
          RETURNS INTEGER   
          LANGUAGE SQL   
          DETERMINISTIC   
          CONTAINS SQL   
          SQL SECURITY DEFINER   
          COMMENT ''   
BEGIN   
          DECLARE C_V INTEGER ;
          
          UPDATE WFO_SEQ SET CURRENT_VALUE = CURRENT_VALUE + INCREMENT WHERE NAME = SEQ_NAME;
          
          SET    C_V = CURRVAL(SEQ_NAME);
          IF     C_V = -1 THEN
            INSERT INTO WFO_SEQ( NAME ,  CURRENT_VALUE,  INCREMENT)
                 VALUES (SEQ_NAME, 1, 1);
            RETURN 1;
          END IF;
          RETURN C_V;
END   
$   
DELIMITER ;


第四步:创建--更新当前值的函数

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DROP FUNCTION IF EXISTS setval; 
DELIMITER $ 
CREATE FUNCTION setval (seq_name VARCHAR (50), value INTEGER
          RETURNS INTEGER 
          LANGUAGE SQL 
          DETERMINISTIC 
          CONTAINS SQL 
          SQL SECURITY DEFINER 
          COMMENT '' 
BEGIN 
          UPDATE WFO_SEQ
                    SET current_value = value 
                    WHERE name = seq_name; 
          RETURN currval(seq_name); 
END 
DELIMITER ;

第五步:测试函数功能

SELECT SETVAL('TestSeq', 10);---设置指定sequence的初始值
SELECT CURRVAL('TestSeq');--查询指定sequence的当前值
SELECT NEXTVAL('TestSeq');--查询指定sequence的下一个值


4. SHOW TABLE STATUS;

得出的结果里边对应表名记录中有个Auto_increment字段,里边有下一个自增ID的数值就是当前该表的最大自增ID.

### 创建带有自动递增字段的 MySQL 表 在 MySQL 中,可以通过 `AUTO_INCREMENT` 属性定义一个自动递增的列。此属性通常用于主键列,以确保每条记录都有唯一的标识符。下面是一个完整的示例: #### 定义表结构 ```sql CREATE TABLE users ( id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, user_name VARCHAR(15) NOT NULL ) AUTO_INCREMENT = 100; ``` 上述语句创建了一个名为 `users` 的表,其中包含两个字段:`id` 和 `user_name`。`id` 字段被设置为主键,并启用 `AUTO_INCREMENT` 功能[^1]。 #### 自动递增值的初始值设定 通过指定 `AUTO_INCREMENT` 参数,可以控制自动递增序列的起始值。例如,在上面的例子中,`AUTO_INCREMENT = 100` 将使第一个插入的记录具有 ID 值为 100 而不是默认的 1。 #### 查询当前自动递增值 为了获取某个特定表的当前自动递增值,可以查询 `INFORMATION_SCHEMA.TABLES` 数据库中的信息: ```bash autoIncrIndexValue=$(mysql --batch -u$MYSQL_USER -p$MYSQL_PWD -h$MYSQL_HOST mysql -e "SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$MYSQL_DBNAME' AND TABLE_NAME = '$T';" | sed -n '1!p' | awk '{print $1}') ``` 这段脚本展示了如何动态提取某张表的下一个可用自动递增值[^3]。 #### 注意事项 需要注意的是,MySQL 的 `AUTO_INCREMENT` 实现存在潜在风险。如果删除了一些记录并随后重启数据库服务,则新生成的 ID 可能会与之前已存在的 ID 发生冲突[^4]。因此建议谨慎处理基于自增 ID 构建的核心业务逻辑。 另外,当全局 GTID 模式开启时 (`@@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1`) ,部分操作如 `CREATE TABLE ... AS SELECT` 不再允许执行,因为它们可能导致复制环境下的数据一致性问题[^5]。 ### 总结 综上所述,利用 `AUTO_INCREMENT` 关键字可以在 MySQL 中轻松实现自动编号功能;但是实际应用过程中需注意其局限性和可能引发的风险。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值