# Using AUTO_INCREMENT in TiDB

### Overview
+ In these exercises, you gain some experience on the column attribute `AUTO_INCREMENT` in TiDB. You build a table with `AUTO_INCREMENT` attribute on the primary key and query from it.
+ The impact of the distributed nature of TiDB on the behavior of the `AUTO_INCREMENT` attribute is easily noticeable.

 <img src="./diagram/2013E041_en_001_20230407.png" width="60%" align="top"/>

### Notes
+ Some of the steps require that you type commands in two different terminal windows. You might find it convenient to launch two terminals and keep them open for the duration of the exercises.
+ Your environment may be different with these exercises, including but not limited to `IP`, `port`, `username`, `password` and `directory`.
+ The "`$`" in your terminal window is an OS prompt.
+ The "`tidb>`" or "`mysql>`" in your terminal window is SQL command prompt.
+ These exercises can be run on a single machine, such as your laptop.
+ This exercise is exclusively meant for TiDB Playground cluster. It is imperative that we have complete control over which TiDB server instance that we connect to, to execute the demo script. In contrast, in TiDB Cloud, the front-end load balancer service handles this aspect.

### Assumptions
+ You are using macOS or Linux.
+ You have cloned the [lab repository](https://github.com/pingcap/tidb-course-201-lab.git).
+ You have completed the Exercises - Creating a TiDB Cluster for Testing Purposes.

### Duration
+ This exercise should take you approximately 15 minutes to complete.

### Tasks
1. Review and understand the demo scripts.
2. Create two new tables in which the `AUTO_INCREMENT` attribute is set to increment by `AUTO_ID_CACHE` of 300 on the primary key.
3. Then, insert some values into these tables from multiple TiDB server instances, and subsequently query the inserted values from these instances.

### Solution
1. Review and understand the demo scripts.

   Script `07-demo-auto-increment-01-setup.sh` invokes `07-demo-auto-increment-01-setup.sql`, which creates two tables, `t1` and `t2`, with identical configurations.
      ```
      $ cd tidb-course-201-lab/scripts
      $ cat ./07-demo-auto-increment-01-setup.sql
      ```
      ```sql
      /* source 07-demo-auto-increment-01-setup.sql */

      /* Setup: The schema for attribute AUTO_INCREMENT */
      DROP TABLE IF EXISTS test.t1;
      CREATE TABLE test.t1 (
         id INT PRIMARY KEY AUTO_INCREMENT,
         from_port CHAR(4))
         AUTO_ID_CACHE 300;

      DROP TABLE IF EXISTS test.t2;
      CREATE TABLE test.t2 (
         id INT PRIMARY KEY AUTO_INCREMENT,
         from_port CHAR(4))
         AUTO_ID_CACHE 300;
      ```

   Script `07-demo-auto-increment-02-show.sh` invokes `07-demo-auto-increment-02-show.sql` to insert four rows into `t1` from the same TiDB server instance (port 4000), and then verify the result.
      ```
      $ cat ./07-demo-auto-increment-02-show.sql
      ```
      ```sql
      /* source 07-demo-auto-increment-02-show.sql */

      /* Populate test.t1 */
      INSERT INTO test.t1 (from_port) VALUES ('4000');
      INSERT INTO test.t1 (from_port) VALUES ('4000');
      INSERT INTO test.t1 (from_port) VALUES ('4000');
      INSERT INTO test.t1 (from_port) VALUES ('4000');

      /* Query 1: Check */
      SELECT id, from_port FROM test.t1;
      ```

   Script `07-demo-auto-increment-03-show.sh` invokes following scripts to insert four rows into `t2` from two TiDB server instances (on port 4000 and 4001), and then verify the result.
      ```
      $ cat ./07-demo-auto-increment-03-show.sh
      #!/bin/bash

      # ./07-demo-auto-increment-03-show.sh

      echo INSERT via TiDB server 4000
      mysql -h 127.0.0.1 -P 4000 -u root < misc/insert-into-t2-4000-default.sql
      echo INSERT via TiDB server 4001
      mysql -h 127.0.0.1 -P 4001 -u root < misc/insert-into-t2-4001-default.sql
      mysql -h 127.0.0.1 -P 4000 -u root < misc/select-from-t2.sql
      ```
      ```
      $ cat ./misc/insert-into-t2-4000-default.sql
      ```
      ```sql
      INSERT INTO test.t2 (from_port) VALUES ('4000');
      INSERT INTO test.t2 (from_port) VALUES ('4000');
      ```
      ```
      $ cat ./misc/insert-into-t2-4001-default.sql
      ```
      ```sql
      INSERT INTO test.t2 (from_port) VALUES ('4001');
      INSERT INTO test.t2 (from_port) VALUES ('4001');
      ```
      ```
      $ cat misc/select-from-t2.sql
      ```
      ```sql
      SELECT * FROM test.t2;
      ```

2. Run the demo scripts and observe the results.

   Run the setup script to create two tables with identical `AUTO_INCREMENT` configurations.
      ```
      $ ./07-demo-auto-increment-01-setup.sh
      ```

   Run the script `07-demo-auto-increment-02-show.sh` to query table `test.t1`, and receive the results shown. Because the previous INSERT statements on `test.t1` were executed on the same TiDB server instance, the auto-incremented ID is continuous.
      ```
      $ ./07-demo-auto-increment-02-show.sh
      INSERT via TiDB server 4000
      id      from_port
      1       4000
      2       4000
      3       4000
      4       4000
      ```

   Run the third script `07-demo-auto-increment-03-show.sh`, and receive the results shown. Because the previous `INSERT` statements on `test.t2` were executed on different TiDB server instances, the auto-incremented ID is not consecutive.
     ```
     $ ./07-demo-auto-increment-03-show.sh
     INSERT via TiDB server 4000
     INSERT via TiDB server 4001
     id      from_port
     1       4000
     2       4000
     301     4001
     302     4001
     ```

3. Recall that the attribute `AUTO_ID_CACHE` in the example is 300, which means each TiDB server instance will be responsible for a batch of 300 auto-increment IDs.

## End of Exercises