我如何获得当前的AUTO_INCREMENT值为一个表在MySQL?
当前回答
如果你只是想知道这个数字,而不是在查询中得到它,那么你可以使用:
SHOW CREATE TABLE tablename;
您应该在底部看到auto_increment
其他回答
你可以使用这个查询来获取所有的表数据:
SHOW TABLE STATUS FROM `DatabaseName` WHERE `name` LIKE 'TableName' ;
你可以通过使用这个查询来获得确切的信息:
SELECT `AUTO_INCREMENT`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName'
AND TABLE_NAME = 'TableName';
查询一个给定模式下的所有表的AUTO_INCREMENT的“使用率”百分比(bigint类型unsigned列除外):
SELECT
c.TABLE_NAME,
c.COLUMN_TYPE,
c.MAX_VALUE,
t.AUTO_INCREMENT,
IF (c.MAX_VALUE > 0, ROUND(100 * t.AUTO_INCREMENT / c.MAX_VALUE, 2), -1) AS "Usage (%)"
FROM
(SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_TYPE,
CASE
WHEN COLUMN_TYPE LIKE 'tinyint(1)' THEN 127
WHEN COLUMN_TYPE LIKE 'tinyint(1) unsigned' THEN 255
WHEN COLUMN_TYPE LIKE 'smallint(%)' THEN 32767
WHEN COLUMN_TYPE LIKE 'smallint(%) unsigned' THEN 65535
WHEN COLUMN_TYPE LIKE 'mediumint(%)' THEN 8388607
WHEN COLUMN_TYPE LIKE 'mediumint(%) unsigned' THEN 16777215
WHEN COLUMN_TYPE LIKE 'int(%)' THEN 2147483647
WHEN COLUMN_TYPE LIKE 'int(%) unsigned' THEN 4294967295
WHEN COLUMN_TYPE LIKE 'bigint(%)' THEN 9223372036854775807
WHEN COLUMN_TYPE LIKE 'bigint(%) unsigned' THEN 0
ELSE 0
END AS "MAX_VALUE"
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE EXTRA LIKE '%auto_increment%'
) c
JOIN INFORMATION_SCHEMA.TABLES t ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME)
WHERE
c.TABLE_SCHEMA = 'YOUR_SCHEMA'
ORDER BY
`Usage (%)` DESC;
我相信你正在寻找MySQL的LAST_INSERT_ID()函数。如果在命令行中,只需执行以下命令:
LAST_INSERT_ID();
你也可以通过SELECT查询得到这个值:
SELECT LAST_INSERT_ID();
尽管如果手动运行查询,methai的答案是正确的,但当2个并发事务/连接在生产环境(例如)的运行时实际执行此查询时,就会出现问题。
刚刚在MySQL工作台中手动尝试了2个连接同时打开:
CREATE TABLE translation (
id BIGINT PRIMARY KEY AUTO_INCREMENT
);
# Suppose we have already 20 entries, we execute 2 new inserts:
事务1:
21 = SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName' AND TABLE_NAME = 'translation';
insert into translation (id) values (21);
事务2:
21 = SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'DatabaseName' AND TABLE_NAME = 'translation';
insert into translation (id) values (21);
# commit transaction 1;
# commit transaction 2;
插入事务1是可以的 事务2插入错误:错误代码:1062。重复输入'21'键'主'。
一个好的解决方案是jvdub的答案,因为每个事务/连接的2个插入将是:
事务1:
insert into translation (id) values (null);
21 = SELECT LAST_INSERT_ID();
事务2:
insert into translation (id) values (null);
22 = SELECT LAST_INSERT_ID();
# commit transaction 1;
# commit transaction 2;
但是我们必须在插入之后执行last_insert_id() ! 并且我们可以重用该id,以便在需要外键的其他表中插入它!
同样,我们不能执行下面的2个查询:
insert into translation (id) values ((SELECT AUTO_INCREMENT FROM
information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE()
AND TABLE_NAME='translation'));
因为我们实际上感兴趣的是在其他表中抓取/重用ID或返回!
编辑:02/08/2021,检索值
要实际检索应用程序中的值(使用MyBatis在Java中完成的示例):
@Mapper
public interface ClientMapper {
@Select("select last_insert_id()")
Long getLastInsertedId();
@Insert({...})
void insertClientInfo(ClientInfo client);
}
然后在存储库中
public void insertClientInfo(ClientInfo clientInfo) {
mapper.insertClientInfo(clientInfo);
Long pk = mapper.getLastInsertedId();
clientInfo.setId(pk);
}
如果你只是想知道这个数字,而不是在查询中得到它,那么你可以使用:
SHOW CREATE TABLE tablename;
您应该在底部看到auto_increment