My guess from the documentation would be that merely increasing the length constraint on a varchar would not cause the same trouble as adding a column:įor some operations, an in-place ALTER TABLE is possible that does notīut that seems to be contradicted in the comments on this SO question.Īt least on 5.0, I think I can confirm that increasing the length does indeed require a temporary table (or some other equally expensive operation): Script 3 can be executed on a master or anywhere else.Script 2 can be executed on any slave that does have binary.Script 1 can be executed on any slave that do not have binary logs.INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTableOld # and preventing new data from entering into the old tableĬREATE TABLE WorkingTableNew LIKE WorkingTableOld
#Mysql add column huge table code
Let alter this code a little to prevent entering new data in that respect: # INSERT INTO WorkingTableNew SELECT SQL_NO_CACHE * FROM WorkingTable īut wait !!! What about any new data that comes in while processing these commands ? Renaming the table in the beginning of the operation should do the trick. # while preventing it from replicating to slaves Simply shut off binary logging in the session before doing the ALTER TABLE stuff: # Simple: Don't send the SQL into the master's binary logs. What about the master ? How do you prevent this from replicating to the slaves. INSERT INTO WorkingTableNew SELECT * FROM WorkingTable ĪLTER TABLE WorkingTable RENAME WorkingTableOld ĪLTER TABLE WorkingTableNew RENAME WorkingTable # Alter table structure of a single column of a large tableĬREATE TABLE WorkingTableNew LIKE WorkingTable ĪLTER TABLE WorkingTableNew MODIFY BigColumn VARCHAR(50) You could perform the changes in stages like this: #
Suppose the table you want to change is called WorkingTable. If you are a little adventurous, you could take matters into your hands by performing the ALTER TABLE in stages you can see.
0 kommentar(er)
