What is the significance of the SHOW CREATE TABLE result showing engine=innodb? I don't quite understand

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: SHOW CREATE TABLE结果显示engine=innodb的意义是什么，不太理解

| username: TiDBer_sJAqARVl

Test Environment
Server version: 5.7.25-TiDB-v6.1.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

tidb [test]>show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Isn’t TiDB’s underlying structure LSM? So what is the purpose of showing ENGINE=InnoDB here? I don’t quite understand.

Additionally, if you try to delete the PK of table t1 at this point, you will get the following error message:

tidb [test]>alter table t1 drop primary key;
ERROR 8200 (HY000): Unsupported drop primary key when the table's pkIsHandle is true

The phrase “when the table’s pkIsHandle is true” is also not easy to understand literally as to why deletion is not allowed.
Thank you.

2
| username: Jiawei | Original post link

First, let’s talk about why primary keys are not supported for deletion.
From the perspective of TiDB’s underlying data storage format,
the data is stored in a KV structure.
For CLUSTERED tables, the Key is the primary key, and the value is other columns. If you want to delete the primary key, the data cannot be stored.
Therefore, CLUSTERED tables do not support deleting primary keys.
Then for engine=innodb, I understand it is to be compatible with those metadata tables of innodb, such as the tables in the information_schema database, performance, and other such libraries.

3
| username: WalterWj | Original post link

Only compatible with MySQL display

4
| username: 近墨者zyl | Original post link

The official documentation on clustered indexes, you’ll understand after reading it.

TiDB is compatible with MySQL, and the purpose of displaying ENGINE=InnoDB is for compatibility with the MySQL server layer.

5
| username: TiDBer_sJAqARVl | Original post link
  1. After I delete the CLUSTERED attribute of the PK, can it automatically convert to a NON CLUSTERED PK?
  2. Is it necessary for MySQL compatibility to display engine=innodb? Wouldn’t it be more compatible to just remove engine=xx directly?
6
| username: 胡杨树旁 | Original post link

Create NON CLUSTERED. You need to specify it when creating the table.

7
| username: Jiawei | Original post link

For point 1, if it automatically converts, that would be equivalent to rebuilding the table and re-planning the data format, which would be too costly in terms of impact.
For point 2, I’m a bit unclear, but simply put, it means you can use TiDB in the same way you operate MySQL.

8
| username: alfred | Original post link

It’s a more powerful MySQL, for example, there’s no need to use sharding middleware anymore.

9
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.