How to add columns in MySQL?

The ALTER TABLE statement can be used to add new columns in MySQL. For small tables, just use ALTER TABLE. For large tables, you can use the pt-online-schema-change tool to modify without locking the table, or create a new table and copy data to reduce the impact on the business. Backing up a database is crucial to prevent data loss.

MySQL add column? This question seems simple, but it actually has a secret. Do you think it's just a simple ALTER TABLE sentence? Naive! In actual operation, there are many pitfalls. If you are not careful, the data will be inconsistent at the least, and the database will be crashed at the worst, making you cry without tears. This article will take you to understand easily and avoid those "reefs and dangerous shoals".

Let’s talk about the most basic ones. The ALTER TABLE statement can indeed add columns, but its efficiency and security depend on your operation method and the amount of data in the table. For small tables, directly ALTER TABLE your_table ADD COLUMN new_column INT DEFAULT 0; This command is enough, simple and crude, and done in one go. But for giant watches, don't do this! The database will lock the table, and the entire table will be unavailable. During this period, all read and write operations on the table will be blocked. Think about it, how does business paralysis feel?

So, for large tables, we need some strategies. One way is to use pt-online-schema-change tool, which can modify the table structure without locking the table. This tool is part of Percona Toolkit and is powerful, but you need to read the documentation carefully before using it and figure out its parameter settings, otherwise unexpected problems are likely to occur. For example, it requires extra storage space, and you need to estimate the space size to avoid insufficient space causing operation failure. Moreover, the performance of this tool is also affected by the network environment and hardware conditions, so it is also important to choose the right server configuration. I used to ignore network delay, which caused this tool to run for too long and I almost got fired by my boss.

Another way is to create a new table, contain new columns, then copy the data of the old table to the new table, finally delete the old table, and rename the new table to the old table's name. Although this approach may seem cumbersome, it minimizes the impact on the business as the entire process does not lock tables. However, this approach requires considering data consistency, and you need to ensure the integrity of the data replication process, otherwise it will cause data loss. In addition, this method requires additional storage space and needs to be planned in advance.

Code example, suppose your table is called users , you want to add a column named email , type VARCHAR(255) :

Method 1 (small table):

 <code class="sql">ALTER TABLE users ADD COLUMN email VARCHAR(255) DEFAULT NULL;</code>

Method 2 (large table, using pt-online-schema-change):

 <code class="bash">pt-online-schema-change --alter "ADD COLUMN email VARCHAR(255) DEFAULT NULL" D=your_database,t=users --execute ``` (记得替换`your_database`为你的数据库名) **方法三(大型表，创建新表):**</code>

CREATE TABLE users_new LIKE users;
ALTER TABLE users_new ADD COLUMN email VARCHAR(255) DEFAULT NULL;
INSERT INTO users_new SELECT * FROM users;
RENAME TABLE users TO users_old, users_new TO users;
DROP TABLE users_old;

<code>记住，选择哪种方法取决于你的实际情况。 没有绝对的好坏，只有适合与否。 别盲目跟风，要根据你的表大小、数据量、业务需求等因素综合考虑。 最后，别忘了备份你的数据库！ 这可是最重要的！ 数据库崩溃了，你哭都没地方哭去。 这可是血泪教训啊！</code>

The above is the detailed content of How to add columns in MySQL?. For more information, please follow other related articles on the PHP Chinese website!