View the command to modify the MySQL table structure

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

Recommend

Detailed explanation of three ways to wrap text in el-table header

Table of contents Problem Description Rendering T...

Vue must learn knowledge points: the use of forEach()

Preface In front-end development, we often encoun...

Nginx load balancing configuration, automatic switching mode when downtime occurs

Strictly speaking, nginx does not have a health c...

Understanding and application of JavaScript ES6 destructuring operator

Table of contents Preface The role of deconstruct...

Detailed explanation of accessing MySQL database in Linux virtual machine under Windows environment

Linux virtual machine: VMware + Ubuntu 16.04.4 Wi...

Detailed explanation of tcpdump command examples in Linux

Preface To put it simply, tcpdump is a packet ana...

Example of how to upload a Docker image to a private repository

The image can be easily pushed directly to the Do...

How to write CSS elegantly with react

Table of contents 1. Inline styles 2. Use import ...

Goodbye Docker: How to Transform to Containerd in 5 Minutes

Docker is a very popular container technology. Th...

Robots.txt detailed introduction

Robots.txt is a plain text file in which website ...

Determine whether MySQL update will lock the table through examples

Two cases: 1. With index 2. Without index Prerequ...

MySQL 8.0.11 compressed version installation and configuration method graphic tutorial

MySQL 8.0 compressed package installation method,...

How to use nginx as a load balancer for mysql

Note: The nginx version must be 1.9 or above. Whe...

Complete steps for mounting a new data disk in CentOS7

Preface I just bought a new VPS. The data disk of...

MySQL SQL statement method to hide the middle four digits of the mobile phone number

First query table structure (sys_users): SELECT *...