How to change id column type already registered in the database-CodePudding

I'm trying to change my Id column type from int to bigInt because the records are too big and I'm getting an error if I try to store more records but I'm not sure if it's safe to just alter the table like this:

 alter table the_table
     alter the_column type bigint;

I'm using Java Springboot to create the entities and Hibernate for persistence. What's the best way to solve this issue in a safe way since there are very important records inside the DB?

CodePudding user response：

Why can't you just do this?

ALTER TABLE tableName ALTER COLUMN ID bigint

I guess try it in a test environment first but this always works for me

CodePudding user response：

Proper SQL code for MySQL would be:

ALTER TABLE the_table MODIFY COLUMN the_column BIGINT;

It will take some time for big tables (as it needs to copy data); if you want to make it without an outage on the table, you can try to use pt-online-schema-change command from percona tools (https://www.percona.com/doc/percona-toolkit/3.0/pt-online-schema-change.html) or if you want to do it more safely (but with an outage on data)

RENAME TABLE the_table TO the_table_old
CREATE TABLE the_table LIKE the_table_old
REPLACE INTO the_table select * from the_table_old limit 0,1000
REPLACE INTO the_table select * from the_table_old limit 1000,1000
REPLACE INTO the_table select * from the_table_old limit 2000,1000
DROP TABLE the_table_old

But answering your question - It's a safe way to do alter on the table; it just can take some time and that table will be locked to updates/inserts if you just run alter table