Creating a column in MySql with id such as CHV18000002

Question

i could do this in Microsoft sql to create a column incrementing such as CHV180000001, CHV180000002 but trying to do that in MySql. I have tried but getting error: incorrect syntax. Any guide to achieve this: This is my code:

CREATE TABLE Candidates (ID INT AUTO_INCREMENT NOT NULL Primary Key,
[ApplicationID]  AS ('CHV18'+right('000000'+CONVERT([varchar](6),[ID]),(6))),
    [FirstName] [varchar](100) NOT NULL,
    [MiddleName] [varchar](100) NOT NULL,
    [LastName] [varchar](100) NOT NULL,
    [DateOfBirth] [date] NOT NULL,
    [Gender] [nchar](1) NOT NULL

Answer 1

Try this to create the table

CREATE TABLE Candidates (ID INT(11) AUTO_INCREMENT NOT NULL Primary Key,
    ApplicationID varchar(6),
    FirstName varchar(100) NOT NULL,
    MiddleName varchar(100) NOT NULL,
    LastName varchar(100) NOT NULL,
    DateOfBirth date NOT NULL,
    Gender varchar(1) NOT NULL);