Search code examples
mysqlsqlcountauto-increment

MySQL get next ZERO-FILLED auto incremented value

I created a table with a primary key column that is zero-filled and having auto-incremented value. 

CREATE TABLE IF NOT EXISTS `TestingDB`.`table1` (
  `id` INT(8) ZEROFILL NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

To get the next auto incremented value I use the following statement.

SELECT `AUTO_INCREMENT`
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TestingDB'
AND   TABLE_NAME   = 'table1';

but this only get me the next value without the zero-filled format, is there a way to show it in zero-filled format just like what the primary key does?

Solution

  • Try LPAD(str,len,padstr):

    SELECT lpad(`AUTO_INCREMENT`,8,'0')
FROM  INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'TestingDB'
AND   TABLE_NAME   = 'table1';