MySQL auto incremented field to generate unique ID for the recordWe can get a unique auto-generated number from MySQL by creating an auto-incremented field. MySQL will generate a unique number by incrementing the last number of the table and will automatically add to the auto incremented field.This is the best way to generate a trouble ticket number for a help desk system. In a school, if a new student joins then all details of the student we can feed to student table and while using the insert query. MySQL will add one auto generated a unique number to this auto incremented field. We need not have to specify any data in our query while adding the other details ( except auto-incremented field data). Getting the unique IDAfter successfully adding the record we can get this unique number by using mysql_insert_id(). Read the details on how to get this number by visiting the tutorial here. Now let us try how to create such an auto incremented field.We have to make auto increment field integer ( obvious ) and also unique. The property set to auto increment. Here is the sql to create a student table with one auto increment field to assign one unique student ID.
Now we will add one record to this table using one insert command.
You will see automatically the student_id field will be added with 1 and for next record it will get the next incremented value 2.
This way we can generate auto increment id for each record in our table. Use lastInsertId() PHP PDO function Use insert_id() PHP MySQLI function Examples of uses of autoincrement field
Maximum value of inserted idThe maximum value of the inserted id depends on the type of numeric field we have selected. For TINYINT it will take value upto 127 for unsigned it is 255. After this the creation of auto increment ID will fail. So take care while creating the auto increment field to match your future requirements.Starting from higher numberChange the auto increment field like this
Pre defined value for a MySQL auto increment fieldWe can start an auto increment field from a pre-defined value. For example when we issue a student ID number, where student ID is an auto increment field we can generate IDs with values 100001 from the first record. This helps as we don't want to give a number like 1 to the first student and end with number 10004. This way we can have the first number as 100001 and last number 110005. This way all the students will have six-digit number.This we can take care while creating the table by adding auto increment value at the end. Here is the SQL for creating a student table with auto increment field is set to 100000
If you delete all records by truncate table sql command then while adding the first record the auto increment field will start from 1.
Alter table and make one numeric field as auto increment Read here on how to create one incremental auto generated record number in MSSQL table.
This article is written by plus2net.com team.
![]() ![]() | ||||||||||||
| ||||||||||||