# [FAQ] TiDB 在使用多列 UNIQUE INDEX 创建表时, TiDB(MUL) 与 MySQL(PRI) 的 information_schema.COLUMN_KEY 显示不兼容

## Key Words

MUL, PRI, 组合唯一索引, 多列唯一索引

## Description

1. 建表语句如下

    ```sql
    CREATE TABLE `company_info_tl`  (
    `company_id` bigint(20) NOT NULL COMMENT '表ID,主键,供其他表做外键',
    `company_short_name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '公司简称',
    `company_full_name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '公司全称',
    `lang` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '语言',
    `tenant_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '租户ID,hpfm_tenant.tenant_id',
    UNIQUE INDEX `company_info_tl_u1`(`company_id`, `lang`, `tenant_id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '公司附属信息多语言表' ROW_FORMAT = Dynamic;
    ```

2. TiDB 中显示如下

    ```sql
    mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_KEY from information_schema.columns where table_name='company_info_tl';                                                                                                                          +--------------+----------------------+--------------------+------------+                                                                                                                                                                              | TABLE_SCHEMA | TABLE_NAME           | COLUMN_NAME        | COLUMN_KEY |
    +--------------+----------------------+--------------------+------------+
    | test         | company_info_tl | company_id         | MUL        |
    | test         | company_info_tl | company_short_name |            |
    | test         | company_info_tl | company_full_name  |            |
    | test         | company_info_tl | lang               |            |
    | test         | company_info_tl | tenant_id          |            |
    +--------------+----------------------+--------------------+------------+
    5 rows in set (0.02 sec)

    mysql> desc company_info_tl;
    +--------------------+--------------+------+------+---------+-------+
    | Field              | Type         | Null | Key  | Default | Extra |
    +--------------------+--------------+------+------+---------+-------+
    | company_id         | bigint(20)   | NO   | MUL  | NULL    |       |
    | company_short_name | varchar(150) | NO   |      | NULL    |       |
    | company_full_name  | varchar(150) | NO   |      | NULL    |       |
    | lang               | varchar(30)  | NO   |      | NULL    |       |
    | tenant_id          | bigint(20)   | NO   |      | 0       |       |
    +--------------------+--------------+------+------+---------+-------+
    5 rows in set (0.01 sec)
    ```

3. MySQL 中显示如下

    ```sql
    mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_KEY from information_schema.columns where table_name='company_info_tl';
    +--------------+----------------------+--------------------+------------+
    | TABLE_SCHEMA | TABLE_NAME           | COLUMN_NAME        | COLUMN_KEY |
    +--------------+----------------------+--------------------+------------+
    | test         | company_info_tl | company_id         | PRI        |
    | test         | company_info_tl | company_short_name |            |
    | test         | company_info_tl | company_full_name  |            |
    | test         | company_info_tl | lang               | PRI        |
    | test         | company_info_tl | tenant_id          | PRI        |
    +--------------+----------------------+--------------------+------------+
    5 rows in set (0.06 sec)

    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 5.7.26-1  |
    +-----------+
    1 row in set (0.05 sec)


    mysql> select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_KEY from information_schema.columns where table_name='company_info_tl';                                                                                                                          +--------------+----------------------+--------------------+------------+
    | TABLE_SCHEMA | TABLE_NAME           | COLUMN_NAME        | COLUMN_KEY |
    +--------------+----------------------+--------------------+------------+
    | test         | company_info_tl | company_full_name  |            |
    | test         | company_info_tl | company_id         | PRI        |
    | test         | company_info_tl | company_short_name |            |
    | test         | company_info_tl | lang               | PRI        |
    | test         | company_info_tl | tenant_id          | PRI        |
    +--------------+----------------------+--------------------+------------+
    5 rows in set (0.02 sec)

    mysql> select version();
    +-----------+
    | version() |
    +-----------+
    | 8.0.32    |
    +-----------+
    1 row in set (0.00 sec)
    ```

综上, MySQL 在使用多列 UNIQUE INDEX 创建表成功后, 每一列在 information_schema.COLUMN_KEY 字段显示为 PRI, 而 TiDB 则只会将首列显示为 MUL 其他列不显示

## Content

如上所述，MySQL 将使用唯一索引作为隐式主键(_rowid)，如

```sql
mysql> create table t(a int not null, b int, unique key(a));
Query OK, 0 rows affected (0.01 sec)

mysql> desc t;
+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| a     | int  | NO   | PRI | NULL    |       |
| b     | int  | YES  |     | NULL    |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)

mysql> insert into t values (1, 2), (2, 3), (5, 6), (7, 8);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select *, _rowid from t;
+---+------+--------+
| a | b    | _rowid |
+---+------+--------+
| 1 |    2 |      1 |
| 2 |    3 |      2 |
| 5 |    6 |      5 |
| 7 |    8 |      7 |
+---+------+--------+
4 rows in set (0.00 sec)
```

但在 TiDB 中的实现方式并不一样, TiDB 有一个名为 _tidb_rowid 的列作为没有 clustered primary key 的表, 详情[参考 Issue 介绍](https://github.com/pingcap/tidb/issues/44190)。

```sql
mysql> create table t(a int not null, b int, unique key(a));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values (1, 2), (2, 3), (5, 6), (7, 8);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select *, _tidb_rowid from t;
+---+------+-------------+
| a | b    | _tidb_rowid |
+---+------+-------------+
| 1 |    2 |           1 |
| 2 |    3 |           2 |
| 5 |    6 |           3 |
| 7 |    8 |           4 |
+---+------+-------------+
4 rows in set (0.00 sec)
```

## Affected Versions

ALL