How to Determine if a Field or Index Already Exists Before Adding it to a Table in TiDB

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

Original topic: TIDB对表增加字段或者索引,如何判断已经存在就不增加

| username: TiDBer_oqrCNpbV

When using MySQL, stored procedures were used, and fields or indexes were added in shell scripts with the following syntax:
ALTER TABLE t_xxx_receipt ADD price_pay INT UNSIGNED after usd_amount;

Since TiDB does not support stored procedures and such syntax, how can we determine in SQL whether a field or index already exists and avoid adding the field or index if it does? Adding duplicates in TiDB will result in an error, or how can we prevent TiDB from reporting an error when adding duplicates?

| username: ddhe9527 | Original post link

The metadata tables in TiDB are compatible with MySQL. You can check field information in information_schema.COLUMNS and index information in information_schema.STATISTICS or information_schema.TIDB_INDEXES. Therefore, to avoid errors when adding a column or index, you should first check the metadata tables and then proceed with the addition if it doesn’t already exist.

| username: TiDBer_oqrCNpbV | Original post link

Okay, then you need to write a shell function to add an index or field yourself.

| username: hey-hoho | Original post link

The SQL judgment is not very good. It is recommended to rewrite the judgment logic in the application.

| username: TiDBer_oqrCNpbV | Original post link

Well, it would be best if it is supported. If not, you can only implement a function with similar functionality on your own.

| username: Kongdom | Original post link

Currently, we have two approaches:

  1. When using the company’s platform script execution tool, the tool only logs errors and continues execution without terminating.
  2. When using the query analyzer ourselves, we generally execute in a way that continues despite errors.
| username: ShawnYan | Original post link

There’s no need to make it so complicated, just add if not exists directly.

TiDB [test] 15:04:01> alter table t1 add column if not exists c2 int;
Query OK, 0 rows affected, 1 warning (0.001 sec)

Note (Code 1060): Duplicate column name 'c2'

TiDB [test] 15:05:10> create index if not exists idx_t1_c2 on t1(c2);
Query OK, 0 rows affected (2.890 sec)

TiDB [test] 15:05:25> create index if not exists idx_t1_c2 on t1(c2);
Query OK, 0 rows affected, 1 warning (0.002 sec)

Note (Code 1061): index already exists idx_t1_c2
| username: Kongdom | Original post link

:+1::+1::+1: So fields and indexes can also be used, we only used them when creating the table.