Realization method of adding deleting and modifying columns in MySQL database

  • 2021-11-14 07:22:23
  • OfStack

This paper describes the implementation method of adding, deleting and modifying columns in MySQL database with examples. Share it for your reference, as follows:

New table user_info:


CREATE TABLE user_info(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
username CHAR(20) NOT NULL DEFAULT '',
gender TINYINT UNSIGNED NOT NULL DEFAULT 0,
weight TINYINT UNSIGNED NOT NULL DEFAULT 0
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

The new column defaults to the last column of the table

Syntax: alter table Table Name add Column Name Name Type Column Property


alter table user_info add height tinyint unsigned not null default 0;

Delete column

Syntax: alter table Table Name drop Column Name


alter table user_info drop height;

Adds a column after the specified column

Syntax: alter table Table Name add Column Name Type Attribute [Default] after Specify Column Name


alter table user_info add height tinyint not null default 0 after username;

Modify the specified column name

Syntax: alter table Table Name change Old Column Name New Column Name Type Attribute Default


alter table user_info change height shengao smallint not null default 0;

modify Modify columns, but not column names

Syntax: alter table Table Name modify Column Name Type Attribute Default


alter table user_info modify shengao tinyint not null default 0;

More readers interested in MySQL can see the topics on this site: "MySQL Common Functions Summary", "MySQL Log Operation Skills Collection", "MySQL Transaction Operation Skills Collection", "MySQL Stored Procedure Skills Collection" and "MySQL Database Lock Related Skills Collection"

I hope this article is helpful to everyone's MySQL database.


Related articles: