1

I need to find all the tables in a MySQL database schema that have AUTO_INCREMENT columns.

I need to be able to set all these tables to a large AUTO_INCREMENT value so as not to overlap the base data that will be added to (million?).

I know that it is a bad idea, but I do not expect the base data to exceed 1000 items per table.

informatik01
  • 16,038
  • 10
  • 74
  • 104
Artistan
  • 1,982
  • 22
  • 33

3 Answers3

5
use information_schema;
select table_name 
from tables 
where auto_increment is not null and table_schema=...;

You can then set the auto-increment value as per Change auto increment starting number?

Or, in a single shot (assuming Unix shell):

mysql information_schema -e 
'select concat ("ALTER TABLE ",table_name," AUTO_INCREMENT=1000000") `-- sql` 
from tables 
where auto_increment is not null and table_schema="your-schema";
'|mysql your-schema
Community
  • 1
  • 1
jsalvata
  • 2,155
  • 15
  • 32
4

Partial answer... this will find all the columns with auto_increment

SELECT 
    * 
FROM 
    `information_schema`.`COLUMNS`
WHERE 
    `EXTRA` = 'auto_increment' AND 
    `TABLE_SCHEMA` = 'foochoo'
Artistan
  • 1,982
  • 22
  • 33
  • 1
    This is useful to find all auto_increment columns in all databases and tables removing the last part " AND TABLE_SCHEMA...." – dstonek Jan 18 '20 at 19:40
0

This is what I used, all executable from within mysql cli.

use information_schema;
SELECT concat ("ALTER TABLE `",table_name,"` AUTO_INCREMENT=",IF(DATA_TYPE='smallint',15000,IF(DATA_TYPE='tinyint',64,IF(DATA_TYPE='mediumint',4000000,IF(DATA_TYPE='int',1000000000,99999999999999)))),";")
FROM `COLUMNS` WHERE extra LIKE '%auto_increment%' and table_schema='myschema'
INTO OUTFILE '/tmp/auto.sql';
use izon;
source /tmp/auto.sql;

this allowed for a specific size per data_type so i could increase the length to "half way".

Artistan
  • 1,982
  • 22
  • 33