View the command to modify the MySQL table structure

Brief description

The editor often encounters some problems with incorrect database encoding, which is a headache. Here is a record for your reference.

Modify the database character set:

ALTER DATABASE db_name DEFAULT CHARACTER SET character_name [COLLATE ...];

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

ALTER TABLE tbl_name DEFAULT CHARACTER SET character_name [COLLATE…];

For example: ALTER TABLE myAPP_cont1 DEFAULT CHARACTER SET utf8;

To modify the character set of a field:

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE …];

like:

ALTER TABLE logtest CHANGE title title VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE myAPP_cont1 CHANGE titles titles VARCHAR(100) CHARACTER SET utf8;

View the database encoding:

SHOW CREATE DATABASE db_name;

View table encoding:

SHOW CREATE TABLE tbl_name;

View the field code:

SHOW FULL COLUMNS FROM tbl_name;

Adding a primary key or index

1. Add a primary key

ALTER TABLE table name ADD PRIMARY KEY (field name);

alter table table_a ADD PRIMARY KEY (id);

2. Delete the primary key

ALTER TABLE table name DROP PRIMARY KEY;

alter table table_a DROP PRIMARY KEY;

3. Add a unique index

ALTER TABLE table name ADD UNIQUE index name (field name);

alter table table_a ADD UNIQUE column_a_unique_index (column_a);

4. Add a normal index

ALTER TABLE table name ADD INDEX index name (field name);

alter table table_a ADD INDEX column_a_index (column_a);

5. Delete the index

ALTER TABLE table name DROP INDEX index name;

alter table table_a DROP INDEX column_a_index;

This is the end of this article about viewing and modifying MySQL table structure commands. For more relevant content on modifying MySQL table structure commands, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

mysql mysqldump only exports table structure or only exports data implementation method
Summary of methods for copying table structure in MySQL
Summary of MySQL table structure modification commands
Mysql method of copying table structure and table data
How to compare two database table structures in mysql
Detailed example of how to modify the table structure in MySQL
Some things to note when modifying the table structure in MySQL
Tutorial on mapping MySQL table structures to objects in Python
mysql alter table command to modify table structure example
MySQL data source table structure diagram

<<: HTML table markup tutorial (2): table border attributes BORDER

>>: Solve the problem of spring boot + jar packaging deployment tomcat 404 error