How to make second column as a auto increment after creating first column in mysql?-CodePudding

I just want to produce primary key with auto increment column in mysql table

Tried: customer_id(PK, AI) first_name last_name balance

1 prawin kumar 1000 2 sunny leone 1000000 3 emily clarke 10000

Expected: customer_id(PK, AI) first_name last_name balance

00001 prawin kumar 1000 00002 sunny leone 1000000 00003 emily clarke 10000

PK - Primary key , AI - Auto Increment

CodePudding user response：

You can achieve this using ZEROFILL

CREATE TABLE `sampledb`.`yourtable` (
    `customer_id` INTEGER(5) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(45) NULL,
    `last_name` VARCHAR(45) NULL,
    `balance` INT NULL,
    PRIMARY KEY (`customer_id`)
);

Input (Insert) :

INSERT INTO `sampledb`.`yourtable` (`customer_id`, `first_name`, `last_name`, `balance`) VALUES ('1', 'prawin', 'kumar', '1000');
INSERT INTO `sampledb`.`yourtable` (`customer_id`, `first_name`, `last_name`, `balance`) VALUES ('2', 'sunny', 'leone', '1000000 ');
INSERT INTO `sampledb`.`yourtable` (`customer_id`, `first_name`, `last_name`, `balance`) VALUES ('3', 'emily', 'clarke ', '10000');

Output :

select * from yourtable;