To update a mysql large table or add a field

Mysql large table update or add field method MYSQL large table modify structure Postedon2012-12-2815: 06 snake Wolf read (43) Comment (0) EDIT favorite reference principle basis: dev.mysql.comdocrefman5.1zhsql-syntax.html # alter-tableALTERTABLE temporary replication will be performed on the original table at run time, on the copy

Mysql large table update or add field method MYSQL large table modification structure Posted on snake Wolf read (43) Comment (0) EDIT favorite reference principle basis: A temporary copy of the original TABLE is performed at the http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#alter-table alter table Runtime on the replica

Mysql large table update or add Field Method
Modify the structure of a MYSQL large table
Posted on Views (43) Comments (0) EDIT favorites
Reference principles:

Http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#alter-table

During the alter table operation, the original TABLE is temporarily copied, changed on the copy, deleted, and renamed. When you execute alter table, other users can read the original TABLE, but the update and modification operations on the TABLE will be delayed until the new TABLE is generated. After a new table is generated, the update and modification information is automatically transferred to the new table.

Note: If you use an option other than RENAME when executing alter table, MySQL creates a temporary TABLE. MySQL performs this operation even if the data does not need to be copied (for example, when you change the column name. For MyISAM tables, you can set the myisam_sort_buffer_size system variable to a high value to speed up index re-creation (this operation is the slowest part of the change process.

Actually, adding a field to a table with hundreds of millions of data records

Actual Operation:

#####
Create table ppy_myfishinstanceone_bak like ppy_myfishinstanceone;

Alter table ppy_myfishinstanceone_bak add column 'top _ num' int (10) DEFAULT 0;

Insert into ppy_myfishinstanceone_bak (member_id, my_fish_tank_id, fish_id, hungry, life, exp, last_calculated, last_potion_used)
Select member_id, my_fish_tank_id, fish_id, hungry, life, exp, last_calculated, last_potion_used
From ppy_myfishinstanceone;

Drop table ppy_myfishinstanceone;

Rename table ppy_myfishinstanceone_bak to ppy_myfishinstanceone;
#####