Mitigating Practices for updating the table structure of big Data

Scene

A few days ago, a problem was encountered, to add a new field to a table with a larger amount of data in the online database, and to assign a default value,
It takes a long time to execute, and if SQL is executed directly on the basis of the original table, it is feared that the table or even the database will be deadlocked.
Chat with the team brother to find a way, do not know also can draw on.

Workaround create a temporary table based on the source table

CREATE TABLE t_bdcards_temp like t_bdcards;

Add a field to a temporary table

ALTER TABLE T_bdcards_temp
ADD COLUMN bookId BIGINT NULL
DEFAULT NULL
COMMENT ' corresponding book ID ' after user_id;

Inserting data from a source table into a temporary table

INSERT into T_bdcards_temp (
Card_num,
STATUS,
CTime
USER_ID,
BookId
) SELECT
Card_num,
STATUS,
CTime
USER_ID,
100031
From
T_bdcards;

Verify that the data is correct

SELECT
COUNT (ID)
From
T_bdcards_temp;

SELECT
COUNT (ID)
From
T_bdcards;

Change the name of the source table to another noun

RENAME TABLE t_bdcards to T_bdcards_blank;

Modify the temporary table to the name of the source table

RENAME TABLE t_bdcards_temp to T_bdcards;

Mitigating Practices for updating the table structure of big Data