0

We started with ID as int(11). Then we switched to Hibernate column auto generation and the ID became bigint(20). So, many foreign keys stopped working unless I change the join column definition from bigint(20) to int (11). Is it possible to write a script that changes all int(11) to bigint (20) for all columns?

The query to find all columns to change is as follows:

select table_name, column_name, column_type 
from information_schema.columns
where table_schema = 'rentoptimum' 
  and column_type='int(11)'
order by table_name, ordinal_position;

Can I update data in the information_schema.columns or I should write the alter script for every table?

Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
Alex
  • 7,007
  • 18
  • 69
  • 114
  • do you mean create `alter table` statements? You can check [**information schema**](http://stackoverflow.com/questions/1526688/get-table-column-names-in-mysql) and create dynamic query. – Juan Carlos Oropeza Feb 08 '17 at 13:48
  • That's what I thought, how? – Alex Feb 08 '17 at 14:20
  • But what is the problem? Query the information schema or create the dynamic query. Tell me more to guide you on the right direction. – Juan Carlos Oropeza Feb 08 '17 at 14:37
  • Please, see the edit to my post, it has a question – Alex Feb 08 '17 at 14:49
  • Well you like to take shortcuts, dont you? The true is I dont know, I wouldnt take the risk and write the proper `ALTER` query instead. Maybe you can get away with change the type, but not sure if the db check for the conversion constraint. – Juan Carlos Oropeza Feb 08 '17 at 14:57
  • @JuanCarlosOropeza information_schema does not have tables, those are system views and are not updateable. Proper alter table statements are the only way out. – Shadow Feb 08 '17 at 15:13

1 Answers1

2

You can query INFORMATION_SCHEMA to dynamically generate a script with the ALTER TABLE statements needed to modify these column data types.

You need to be very careful to only modify the data type, and not any other attributes of the column (nullability, auto_increment, signed/unsigned, etc).

Here is an example to get you started. This will generate one ALTER TABLE statement per table in a given schema, even if it has multiple columns to modify. It will replace int(11) with bigint(11), which is slightly different than what you asked for, but the (11) doesn't affect the actual data type. You can tweak that if you want.

select concat('alter table `',t.table_schema,'`.`',t.table_name,'`',
  group_concat(' modify column `',c.column_name,'` ',replace(c.column_type,'int','bigint'),
  if(c.is_nullable='yes',' null',' not null '),c.extra),';') as the_ddl 
into outfile '/tmp/so42114820.sql'
from information_schema.columns c 
  inner join information_schema.tables t on t.table_schema = c.table_schema and t.table_name = c.table_name
where t.table_schema = 'your_schema' 
  and t.table_type = 'BASE TABLE'
  and c.data_type = 'int' 
group by t.table_schema,t.table_name;

set @foreign_key_checks = @@foreign_key_checks;
set foreign_key_checks = 0;

\. /tmp/so42114820.sql

set foreign_key_checks = @foreign_key_checks;
Ike Walker
  • 64,401
  • 14
  • 110
  • 109