The ALTER TABLE command is commonly implemented in the table structure, including four steps: adding new columns, modifying existing columns, deleting columns, and renaming columns or tables. Adding a new column uses ADD COLUMN and you can specify the default value and location, such as ALTER TABLE users ADD COLUMN email VARCHAR(255); To modify an existing column, you need to select MODIFY or ALTER COLUMN according to the database type... TYPE, such as MySQL uses ALTER TABLE users MODIFY age INT; Deleting a column is done through DROP COLUMN and irreversible, such as ALTER TABLE users DROP COLUMN phone; Rename a column or table using RENAME COLUMN and RENAME TO, respectively, such as MySQL's ALTER TABLE users RENAME COLUMN user_name TO username; Before the operation, you need to confirm data processing and backup, and update the code logic and documents to avoid subsequent problems.

Table structure is a very common requirement in database maintenance, especially during project iteration, where there are insufficient fields or the types are not appropriate, the ALTER TABLE command must be used. SQL provides a number of flexible options that allow you to adjust the structure without deleting tables.

Add a new column: add a field to the table

The most common situation is to find that there is a missing field, such as the user table did not have an email address at the beginning, but later I wanted to fill it in. At this time, you can use ADD COLUMN :

 ALTER TABLE users ADD COLUMN email VARCHAR(255);

This statement will automatically add the new column to the end. If you want to specify a location, some databases (such as MySQL) support AFTER existing_column , but not all systems support this writing method, it depends on the specific database documentation.

The default values ??can also be added together:

 ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

In this way, the newly added fields will have default values ??and will not be all NULL.

Modify existing columns: change type or constraint

Sometimes the field type is not suitable. For example, the original age was used to store TINYINT , but it was found that it was not enough and needed to change it to INT . You can use MODIFY or ALTER COLUMN ... TYPE , and the specific syntax varies from database to database.

MySQL writing method:

 ALTER TABLE users MODIFY age INT;

PostgreSQL is different:

 ALTER TABLE users ALTER COLUMN age TYPE INT;

Note: If the data already exists when modifying the type, the database may try to convert automatically, but if it cannot be converted, an error will be reported. For example, it is easy to cause problems when converting from a string to a number. In this case, it is best to deal with the data first and then change the type.

Delete column: This field is not used

Some fields are no longer used and can be deleted. Although sometimes it is retained and marked as abandoned for historical reasons, if it is sure that it will no longer be used, you can delete it directly:

 ALTER TABLE users DROP COLUMN phone;

The deletion operation is irreversible. You must confirm it clearly before execution, especially in the production environment. It is recommended to back up the data first.

Rename column or table: The name is written incorrectly

If the field name is misspelled or the naming style has changed, you can use RENAME to change the name.

MySQL re-listed name:

 ALTER TABLE users RENAME COLUMN user_name TO username;

PostgreSQL prefers to use separate commands:

 ALTER TABLE users RENAME COLUMN user_name TO username;

Some databases also support table name revision:

 ALTER TABLE users RENAME TO user_profiles;

However, the syntax of different databases may vary greatly, remember to check the documents.

Basically these common operations. Although ALTER TABLE is powerful, changing the structure can sometimes affect performance. Especially when operating on large tables, it is best to choose a low peak period. In addition, don’t forget to update the corresponding code logic and documents before changing the structure, otherwise it will be easy to get stuck when others use it next time.

The above is the detailed content of Modifying Table Structures Using SQL ALTER TABLE Commands. For more information, please follow other related articles on the PHP Chinese website!