How to find next AUTO_INCREMENT value of a table in MYSQL 8

Question

I was using the query below to find the next AUTO_INCREMENT value of table in MYSQL 5.7

SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TABLENAME'
AND TABLE_SCHEMA = DATABASE( ); 

But, the same query in MYSQL 8 is returning NULL. I have used above query in my application which works fine with MYSQL 5.7 but behaves strangely with MYSQL 8.

Can anyone suggest how to find next AUTO_INCREMENT value in MYSQL 8.

Also, if there is a common query which works in both (MYSQL 5.7 and 8) then it would be very helpful.

Answer 1

try to use

ALTER TABLE %1
ADD COLUMN id BIGINT NOT NULL AUTO_INCREMENT UNIQUE FIRST;

to create column, then query

SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'TABLENAME'
AND TABLE_SCHEMA = DATABASE( );

should return correct value.