Thursday, December 18, 2014

Autoincrement MySQL increase to allow clone to insert new without collisions (for a time)

-- this will allow you to update a schema(s) to approx double the max autoincrement value of the data_type (int, smallint, mediumint...)  works well for dev servers that are replicating live data and you want to add data.
-- select the awesomeness of information schema

use information_schema;
-- create alter table statements into an outfile
SELECT concat ("ALTER TABLE `",table_schema,"`.`",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 IN ('schema name...')
INTO OUTFILE '/tmp/auto.sql';
-- source the outfile
source /tmp/auto.sql;