# AUTO_INCREMENT Attribute

__*AUTO_INCREMENT*__
__*auto_increment_increment*__
__*auto_increment_offset*__
__*tidb_allow_remove_auto_inc*__

+ `AUTO_INCREMENT` is the attribute used to automatically populate the default column value
  + For performance reasons, `AUTO_INCREMENT` numbers are assigned to each TiDB server instance in batches
  + Cache batch size can be controlled by the `AUTO_ID_CACHE` table option
  + To get the value used in the last insert operation from function `LAST_INSERT_ID()`
  + To get the next value from the `auto_increment` column of table `information_schema.tables`
```
tidb> SHOW VARIABLES LIKE 'auto_increment_%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| ~!@auto_increment_increment@!~ | 1     |
| ~!@auto_increment_offset@!~    | 1     |
+--------------------------+-------+
```
```
tidb> SHOW VARIABLES LIKE 'tidb_allow_remove_auto_inc';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| tidb_allow_remove_auto_inc | OFF   |
+----------------------------+-------+
1 row in set (0.01 sec)
```
----------------------------------------------------------------------------------------------------------
Class demo scripts:

`https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/07-demo-auto-increment-01-show.sql`

`https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/07-demo-auto-increment-02-show.sh`

`https://github.com/pingcap/tidb-course-201-lab/blob/master/scripts/07-demo-auto-increment-03-show.sh`

Try the following statements and obverse the details.
```
tidb:4000> DROP TABLE IF EXISTS test.ar;
Query OK, 0 rows affected (1.08 sec)

tidb:4000> 
tidb:4000> CREATE TABLE test.ar (id BIGINT PRIMARY KEY AUTO_INCREMENT, name CHAR(30));
Query OK, 0 rows affected (0.58 sec)

tidb:4000> 
tidb:4000> INSERT INTO test.ar (NAME) VALUES ('AAA');
Query OK, 1 row affected (0.01 sec)

tidb:4000> 
tidb:4000> INSERT INTO test.ar (NAME) VALUES ('BBB');
Query OK, 1 row affected (0.00 sec)

tidb:4000> 
tidb:4000> INSERT INTO test.ar (NAME) VALUES ('CCC');
Query OK, 1 row affected (0.00 sec)

tidb:4000> 
tidb:4000> SELECT * FROM test.ar;
+----+------+
| id | name |
+----+------+
|  1 | AAA  |
|  2 | BBB  |
|  3 | CCC  |
+----+------+
3 rows in set (0.00 sec)

tidb:4000> 
tidb:4000> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

tidb:4000> 
tidb:4000> SELECT auto_increment FROM information_schema.tables WHERE table_name='ar' AND table_schema='test'\G
*************************** 1. row ***************************
auto_increment: 4
1 row in set (0.01 sec)
```