How to modify the structure of a table?

Q: How to modify the structure of a table?

ALTER TABLE table_name [alter_option ...]; ALTER TABLE CUSTOMERS DROP ID; ALTER TABLE CUSTOMERS ADD ID INT; ALTER TABLE CUSTOMERS ADD ID INT FIRST; <-- repositions ID column ALTER TABLE CUSTOMERS ADD ID INT AFTER NAME; ALTER TABLE CUSTOMERS MODIFY NAME INT; ALTER TABLE CUSTOMERS MODIFY ID VARCHAR(20); ALTER TABLE CUSTOMERS ALTER NAME SET DEFAULT 1000; ALTER TABLE CUSTOMERS ALTER NAME DROP DEFAULT; ALTER TABLE CUSTOMERS RENAME TO BUYERS;

Difficulty: 4/10

ALTER TABLE syntax, Online DDL, Schema migration strategies

The MySQL ALTER command is used to modify the structure of an existing table. It allows you to make various changes, such as adding, deleting, or modify columns within the table. Additionally, the ALTER command is also used to add and drop different constraints associated with an existing table.

Query:

Scenario Questions

0-2 years experience

1How would you add a nullable `email` VARCHAR(255) column to an existing `users` table that already has 10k rows?
2What happens if you try to change a `VARCHAR(50)` column to `VARCHAR(30)` when some existing rows have 40-character values?
3You need to add an index on `created_at` for a reporting query. What's the exact command, and does it lock the table?

Follow-up Questions