Shulou(Shulou.com)06/01 Report--

This article will explain in detail how to add fields to the big data table in MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Preface

Adding fields should be familiar to all of you. You can write them out easily. Just add fields to a MySQL table and execute the following sql:

ALTER TABLE tbl_tpl ADD title (255) DEFAULT''COMMENT' title 'AFTER id

However, if there is a large amount of data in an online table, the table will be locked by adding fields, which may take a long time or even cause the service to crash, so the operation is very risky.

The idea of adding fields to MySQL big table

① creates a temporary new table, first copying the structure of the old table (including the index)

Create table new_table like old_table

② adds new fields to the new table

③ copies the data from the old table.

Insert into new_table (filed1,filed2 …) Select filed1,filed2,... From old_table

④ deletes the old table and renames the new table to the name of the old table

However, it should be noted that when the third step is performed, this process may also take time, when new data comes in, so it is best if the original table has a field to record the writing time of the data. you can find the data after performing this step and import it into the new table again and again until the data difference is small. But it is still possible to lose a very small amount of data.

Therefore, if the data of the table is very large, and at the same time to ensure the integrity of the data, it is best to stop the operation.

Another way.

Add fields in the slave library, and then switch between master and slave

Use third-party tools to modify fields online

In general, the amount of data of more than one hundred thousand can be added directly.

This is the end of the article on "how to add fields to the big data table in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.