MySQL Practical Guide: Elegantly Adding Preference Columns to a User Table

When developing projects like wiki.lib00.com, it is common to adjust existing database schemas as business requirements evolve. For instance, to enhance user experience, you might need to record personalized preferences (such as preferred language and UI theme) and the most recent login time in the `user` table. This article details how to elegantly implement this requirement using SQL statements and discusses related best practices. ## 1. Basic Implementation: Adding Multiple Columns Suppose we need to add the following three columns: - `preferred_language`: User's preferred language (zh-CN/en) - `preferred_theme`: User's preferred theme (light/dark/system) - `last_login_at`: Last login time We can use the `ALTER TABLE` statement combined with multiple `ADD COLUMN` clauses to modify the table in a single execution: ```sql ALTER TABLE `user` ADD COLUMN `preferred_language` VARCHAR(10) DEFAULT NULL COMMENT 'Preferred language: zh-CN/en', ADD COLUMN `preferred_theme` VARCHAR(10) DEFAULT 'system' COMMENT 'Preferred theme: light/dark/system', ADD COLUMN `last_login_at` DATETIME DEFAULT NULL COMMENT 'Last login time'; ``` --- ## 2. Advanced Technique: Controlling Column Position By default, MySQL appends newly added columns to the end of the table. However, in practical database design, to maintain a logical and readable schema, we often want to group related columns together. For example, we might want to place `preferred_language` immediately after an existing `status_id` column. In this case, you can use the `AFTER` keyword to specify the physical location of the columns: ```sql ALTER TABLE `user` ADD COLUMN `preferred_language` VARCHAR(10) DEFAULT NULL COMMENT 'Preferred language: zh-CN/en' AFTER `status_id`, ADD COLUMN `preferred_theme` VARCHAR(10) DEFAULT 'system' COMMENT 'Preferred theme: light/dark/system' AFTER `preferred_language`, ADD COLUMN `last_login_at` DATETIME DEFAULT NULL COMMENT 'Last login time' AFTER `preferred_theme`; ``` **Key Takeaways:** - **`AFTER status_id`**: Explicitly instructs MySQL to insert `preferred_language` right after `status_id`. - **Chained Positioning**: To keep the three new columns together logically, the subsequent `preferred_theme` and `last_login_at` columns also use the `AFTER` keyword, pointing to their respective predecessors. Without `AFTER`, MySQL defaults to adding them at the end of the table. --- ## 3. Performance and Scalability Tips Author DP@lib00 reminds you to be particularly cautious when executing DDL (Data Definition Language) operations in a production environment: 1. **Table Locking Risks**: If the `user` table contains a massive amount of data (e.g., millions of rows), executing `ALTER TABLE` directly might cause table locks, blocking live traffic. 2. **Online DDL Tools**: For large tables, it is highly recommended to perform these operations during off-peak hours or use lock-free online DDL tools like `pt-online-schema-change` (from Percona Toolkit) or GitHub's `gh-ost`. 3. **Data Type Selection**: While `VARCHAR(10)` is used here for limited options like language and theme, `ENUM` could also be considered in strictly constrained scenarios. However, `VARCHAR` provides more flexibility when expanding to new languages or themes in the future.