MySQL table Structure Modification Operation Command summary, mysql table structure command

MySQL table Structure Modification Operation Command summary, mysql table structure command

The table structure is as follows:

Copy codeThe Code is as follows:
Mysql> show create table person;
| Person | create table 'person '(
'Number' int (11) default null,
'Name' varchar (255) default null,
'Birthday' date DEFAULT NULL
) ENGINE = MyISAM default charset = utf8 |

Delete column:

Copy codeThe Code is as follows:
Alter table person drop column birthday;

Add column:

Copy codeThe Code is as follows:
Alter table person add column birthday datetime;

Modify the column number to bigint:

Copy codeThe Code is as follows:
Alter table person MODIFY number bigint not null;

Or change the number to id and the type is bigint:

Copy codeThe Code is as follows:
Alter table person CHANGE number id BIGINT;

Add primary key:

Copy codeThe Code is as follows:
Alter table person add primary key (id );

Delete primary key:

Copy codeThe Code is as follows:
Alter table person drop primary key;

Add a unique index:

Copy codeThe Code is as follows:
Alter table person add unique name_unique_index ('name ');

A unique index is created for the name column. The index name is name_unique_index.

Add a common index:

Copy codeThe Code is as follows:
Alter table person add index birthday_index ('birthday ');

Delete An index:

Copy codeThe Code is as follows:
Alter table person drop index birthday_index;
Alter table person drop index name_unique_index;

Disable non-unique Indexes

Copy codeThe Code is as follows:
Alter table person disable keys;

Alter table... disable keys: Stop MySQL from updating non-unique indexes in the MyISAM TABLE.

Activate a non-unique index

Copy codeThe Code is as follows:
Alter table person enable keys;

Alter table... enable keys to recreate the lost index.

Change the default character set and all character columns (CHAR, VARCHAR, TEXT) of the table to the new character set:

Copy codeThe Code is as follows:
Alter table person convert to character set utf8;

Modifies the encoding of a column in a table.

Copy codeThe Code is as follows:
Alter table person CHANGE name varchar (255) character set utf8;

Only change the default character set of a table

Copy codeThe Code is as follows:
Alter table person default character set utf8;

Modify Table Name

Copy codeThe Code is as follows:
Rename table person TO person_other;

Move a table to another database

Copy codeThe Code is as follows:
Rename table current_db.tbl_name TO other_db.tbl_name;