Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: TIDB对表增加字段或者索引，如何判断已经存在就不增加
When using MySQL, stored procedures were used, and fields or indexes were added in shell scripts with the following syntax:
IF NOT EXISTS(SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=‘$IADB’ AND TABLE_NAME=‘t_xxx_receipt’ AND COLUMN_NAME=‘price_pay’) THEN
ALTER TABLE t_xxx_receipt ADD price_pay INT UNSIGNED after usd_amount;
END IF;
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?
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.
Okay, then you need to write a shell function to add an index or field yourself.
The SQL judgment is not very good. It is recommended to rewrite the judgment logic in the application.
Well, it would be best if it is supported. If not, you can only implement a function with similar functionality on your own.
Currently, we have two approaches:
- When using the company’s platform script execution tool, the tool only logs errors and continues execution without terminating.
- When using the query analyzer ourselves, we generally execute in a way that continues despite errors.
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: TiDBer_oqrCNpbV
|
username: 
|
username: 
|
username: 
|
username: 