How to query which tables have AUTO_ID_CACHE=1 through system tables?

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

Original topic: 如何通过系统表查询带有 AUTO_ID_CACHE=1 的表有哪些?

| username: TiDBer_mI4Xaqqj

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.0

[Encountered Problem: Problem Phenomenon and Impact]

Many tables in TiDB were set with AUTO_ID_CACHE = 1 when created. Now I want to query which specific tables have this setting through the system tables. In which system table can I find this information? I checked the tables and columns tables under information_schema, but neither has this metric.

| username: zhanggame1 | Original post link

I only know that you can use SHOW CREATE TABLE to check if the table creation statement has the AUTO_ID_CACHE comment.

| username: Soysauce520 | Original post link

I also couldn’t find it in any base table except for show create table.

| username: forever | Original post link

Didn’t find it, write a shell loop to get it.

| username: redgame | Original post link

Cannot be found.

| username: zhaokede | Original post link

Usually, I handle single tables one at a time, so I don’t have experience with this. I saw someone mentioned above that you can use a shell loop to query and then write to a file or print it out.

| username: wangccsy | Original post link

Is it possible to use stored procedures to achieve this?

| username: 小龙虾爱大龙虾 | Original post link

Write a script to call TiDB’s HTTP API.

| username: Jellybean | Original post link

As far as I remember, there is no unified record in the system table at the moment. You might consider writing a script to handle the processing.

| username: TIDB-Learner | Original post link

Isn’t this value configured to the default value? Has each table been changed to a different value?

| username: TiDBer_mI4Xaqqj | Original post link

Hello. Can this value be configured as the default? Is there a parameter that can be configured? How do I proceed? My version is 6.5.0, and it seems I haven’t found a way to modify the default value.

| username: ljluestc | Original post link

SELECT
    TABLE_SCHEMA,
    TABLE_NAME
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA', 'mysql')
    AND AUTO_INCREMENT IS NOT NULL
    AND AUTO_INCREMENT > 0;
| username: TiDBer_mI4Xaqqj | Original post link

No, I want to check if AUTO_ID_CACHE is 1, not the current auto-increment ID of the table.

| username: Soysauce520 | Original post link

You can also see the table structure exported by dumpling.

| username: pepezzzz | Original post link

#!/bin/bash

Execute the query and store the results in an array

result=($(mysql -h tidb-ip -P 4000 -u root -p’’ -N -e “select TIDB_TABLE_ID from information_schema.tables where TABLE_SCHEMA=‘your_schema’”))

Iterate through the array

for item in “${result[@]}”
do
AUTO_ID_CACHE=$(curl -s http://tidb-ip:status-port/schema?table_id=${item} 2>/dev/null | grep ‘“auto_id_cache”: 1,’)
echo $AUTO_ID_CACHE

Check if the value of auto_id_cache is 1, if so, print TABLE_SCHEMA and TABLE_NAME

if [[ ! -z $AUTO_ID_CACHE ]]; then
mysql -h tidb-ip -P 4000 -u root -p’’ -N -e “select TIDB_TABLE_ID,TABLE_SCHEMA,TABLE_NAME from information_schema.tables where TIDB_TABLE_ID=$item;”
fi
done

| username: zhanggame1 | Original post link

Very strong, not bad.

| username: FutureDB | Original post link

Interesting.

| username: lemonade010 | Original post link

Awesome, just give the answer directly.