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

Source: Internet
Author: User

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.