Change MySQL column to AUTO_INCRMENT?

Suppose we have a table and now need to add AUTO_INCRMENT to the column name. To do this, use the MODIFY command.

Here, we first create a demo table.

mysql>  create table AddingAutoIncrement
   -> (
   -> Id int,
   -> Name varchar(200),
   -> Primary key(Id)
   -> );
Query OK, 0 rows affected (0.47 sec)

We have created a table above, now let us change the table to add AUTO_INCRMENT on the column name "Id". The syntax is as follows -

alter table yourTableNamet modify yourColumnName int AUTO_INCREMENT;

Add AUTO_INCRMENT using the above syntax. The query is as follows.

mysql>  ALTER table AddingAutoIncrement modify Id int AUTO_INCREMENT;
Query OK, 0 rows affected (1.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

Above, we added "AUTO_INCRMENT" to the column name "Id". Let us check it with the help of DESC command. The query is as follows -

mysql> desc AddingAutoIncrement;

Example output.

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| Id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(200) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

See the output above and the column name "Extra". In the column name "Extra", there is a keyword auto_increment. This in itself means that we have successfully added the keyword.

Now, I will insert the record and check if the row is incremented by one. The query is as follows -

mysql> insert into AddingAutoIncrement(Name) values('John');
Query OK, 1 row affected (0.20 sec)

mysql>  insert into AddingAutoIncrement(Name) values('Smith');
Query OK, 1 row affected (0.12 sec)

mysql>  insert into AddingAutoIncrement(Name) values('Bob');
Query OK, 1 row affected (0.10 sec)

Display all records with the help of SELECT statement.

mysql> select *from AddingAutoIncrement;

The following is the output.

+----+-------+
| Id | Name  |
+----+-------+
|  1 | John  |
|  2 | Smith |
|  3 | Bob   |
+----+-------+
3 rows in set (0.00 sec)

As you can see in the output above, the rows are increased by 1.

The above is the detailed content of Change MySQL column to AUTO_INCRMENT?. For more information, please follow other related articles on the PHP Chinese website!