MySQL replacing column type on table creation

Question

I'm running a MySQL server, all is fine and well, but now I have an application running that is creating tables every now and then.

The application is creating tables in which one (or more) columns are defined as tinyint(3). For legacy purposes, I'd like to have these tables defined as smallint(5) instead.

As a workaround Im running a script every minute now, that replaces a single instance of a tinyint(3) column with a smallint(5) column.

    select @a := CONCAT('ALTER TABLE myDatabase.`', table_name, '` CHANGE `', column_name, '` `', column_name, '` SMALLINT( 5 ) UNSIGNED NOT NULL;') qry from information_schema.columns
    where table_schema = 'myDatabase'
      and column_type like '%tinyint%'
    order by table_name,ordinal_position
    ;
    PREPARE stmt FROM @a;
    EXECUTE stmt;

One of the downsides is that this script only modifies a single column at a time, and produces an error if there is no tinyint column.

Is there not some more elegant way of replacing the tinyint(3) columns with smallint(5) columns?

With kind regards, Marcel

Answer 1

You can at least fix the errors:

set @a := '';
select @a := CONCAT('ALTER TABLE myDatabase.`', table_name, '` CHANGE `', column_name, '` `', 
                     column_name, '` SMALLINT( 5 ) UNSIGNED NOT NULL;') qry
from information_schema.columns
where table_schema = 'myDatabase'
  and column_type like '%tinyint%'
order by table_name, ordinal_position
limit 1;

if @a <> ''
then
    PREPARE stmt FROM @a;
    EXECUTE stmt;
end if;

With a bit more work, you could change all the columns in a single table with one alter statement, but that effort may not be worth it.