How to quickly modify the table structure of MySQL table

Quickly modify the table structure of a MySQL table - excerpted from "MySQL Management"

ALTER TABLE table name MODIFY column name data type;

This command can modify the table structure.

In addition, you can also modify the table structure as follows:

First create a table as follows:

> create table t1 (id int,
    name varchar(5),
    rmb decimal(9,1));

If you want to modify the name column to varchar(10), you can do this:

alter table t1 modify name varchar(7);

You can also do the following:

1. View the table structure as follows:

> use test;
> desc t1;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(5) | YES | | NULL | |
| rmb | decimal(9,1) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2. Create a temporary table and set varchar to 10:

> create table t1_tmp (id int,
  name varchar(10),
  rmb decimal(9,1));

3. Replace the .frm table structure file

> flush tables with read lock; Lock the table first before opening it to avoid data loss.
 > system cp /usr/local/mariadb/var/test/t1_tmp.frm /usr/local/mariadb/var/test/t1.frm

4. Unlock

> unlock tables;

5. View the table structure

> show create table t1\G
*************************** 1. row ***************************
    Table: t1
Create Table: CREATE TABLE `t1` (
 `id` int(11) DEFAULT NULL,
 `name` varchar(10) DEFAULT NULL,
 `rmb` decimal(9,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

You can see the varchar(10) in the name column.

6. Try inserting a piece of data

> insert into t1 values(2,'hechuangyang',3.8); If no error is reported, the modification is successful.

The above is the details of how to quickly modify the table structure of MySQL. For more information about modifying the table structure of MySQL, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

Basic operations of MySQL data tables: table structure operations, field operation example analysis
How to compare two database table structures in mysql
Detailed explanation of MYSQL database table structure optimization method
3 methods to restore table structure from frm file in mysql [recommended]
Detailed explanation of Linux mysqldump exporting database, data, and table structure
MySQL uses procedure analyse() function to optimize table structure
A simple method to export table structure script using Navicat for MySQL
Mysql method of copying table structure and table data
Some things to note when modifying the table structure in MySQL
Summary of MySQL table structure modification commands

<<: WeChat applet implements waterfall flow paging scrolling loading

>>: Front-end JavaScript thoroughly understands function currying