Mysql Modify Table Structure Operation command Summary _mysql

The structure of the table is as follows:

Copy Code code as follows:

Mysql> Show create table person;
| person | CREATE TABLE ' person ' (
' Number ' int (one) DEFAULT NULL,
' Name ' varchar (255) DEFAULT NULL,
' Birthday ' date DEFAULT NULL
) Engine=myisam DEFAULT Charset=utf8 |

To delete a column:

Copy Code code as follows:

ALTER TABLE person DROP COLUMN birthday;

To add a column:

Copy Code code as follows:

ALTER TABLE person ADD COLUMN birthday datetime;

Modify the column to change the number to bigint:

Copy Code code as follows:

ALTER TABLE person MODIFY number BIGINT not NULL;

or change number to ID, type bigint:

Copy Code code as follows:

ALTER TABLE person change number ID BIGINT;

To add a primary key:

Copy Code code as follows:

ALTER TABLE person ADD PRIMARY KEY (ID);

Delete primary key:

Copy Code code as follows:

ALTER TABLE person DROP PRIMARY KEY;

To add a unique index:

Copy Code code as follows:

ALTER TABLE person ADD UNIQUE name_unique_index (' name ');

A unique index was created for the column name, which is named Name_unique_index.

To add a normal index:

Copy Code code as follows:

ALTER TABLE person ADD INDEX birthday_index (' birthday ');

To delete an index:

Copy Code code as follows:

ALTER TABLE person DROP INDEX Birthday_index;
ALTER TABLE person DROP INDEX Name_unique_index;

Disabling a non-unique index

Copy Code code as follows:

ALTER TABLE person DISABLE KEYS;

ALTER TABLE ... The DISABLE keys let MySQL stop updating the MyISAM index in the table.

Activate a non-unique index

Copy Code code as follows:

ALTER TABLE person to ENABLE the KEYS;

ALTER TABLE ... ENABLE the keys to recreate the missing indexes.

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

Copy Code code as follows:

ALTER TABLE person CONVERT to CHARACTER SET UTF8;

Modify the encoding of a column in a table

Copy Code code as follows:

ALTER TABLE Person Change name name varchar (255) CHARACTER SET UTF8;

Just change the default character set for a table

Copy Code code as follows:

ALTER TABLE person DEFAULT CHARACTER SET UTF8;

Modify Table Name

Copy Code code as follows:

RENAME TABLE person to Person_other;

Move a table to another database

Copy Code code as follows:

RENAME TABLE current_db.tbl_name to Other_db.tbl_name;