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

MySQL series 9 MySQL query cache and index

Table of contents Tutorial Series 1. MySQL Archit...

Detailed explanation of how to use Vue to load weather components

This article shares with you how to use Vue to lo...

MySQL 8.0.12 Quick Installation Tutorial

The installation of MySQL 8.0.12 took two days an...

VMware Workstation 14 Pro installs CentOS 7.0

The specific method of installing CentOS 7.0 on V...

CSS Problems with Using Position:fixed and Margin-top Together on Same-Level Elements

Problem Description I want to use CSS to achieve ...

Secondary encapsulation of element el-table table (with table height adaptation)

Preface During my internship at the company, I us...

Create a virtual environment using venv in python3 in Ubuntu

1. Virtual environment follows the project, creat...

Practical explanation of editing files, saving and exiting in linux

How to save and exit after editing a file in Linu...

Solution to the inconsistency between crontab execution time and system time

Preface In LINUX, periodic tasks are usually hand...

Detailed explanation of Linux inotify real-time backup implementation method

Real-time replication is the most important way t...

MySQL 8.0.14 installation and configuration method graphic tutorial (general)

MySQL service 8.0.14 installation (general), for ...

Detailed explanation of CSS margin collapsing

Previous This is a classic old question. Since a ...

Convert XHTML CSS pages to printer pages

<br />In the past, creating a printer-friend...

Examples of adding, modifying, deleting and clearing values ​​in MySQL database

3. MySQL Data Management The first method: not re...

Implementation code for automatically adapting the width of the web page to the width of the mobile phone screen (viewport)

The general way of writing is as follows: XML/HTM...