MySQL 教程

MySQL 表的维护操作

🎉摘要：本文详细总结了MySQL中表的维护操作，包括如何修改表结构（添加、修改、删除列，添加删除索引，修改表名）、表的复制（复制结构、复制结构与数据）、清空表（DELETE与TRUNCATE的区别）以及查看表信息（表结构、建表语句、表状态、表大小）等实用知识点，并提供了具体的SQL示例代码。

前面介绍了如何去创建一张表，以及如何为表创建索引、指定存储过程、约束等知识点，下面将整理总结一下 MySQL 中表的维护操作：

mysql> CREATE TABLE users (
    ->     user_id INT AUTO_INCREMENT PRIMARY KEY,
    ->     username VARCHAR(50) NOT NULL,
    ->     email VARCHAR(100) NOT NULL,
    ->     phone VARCHAR(20),
    ->     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.01 sec)

后续操作将在上面的 users 表中进行。

修改表结构

添加列

给 users 表添加 age 和 nickname 列，如下：

-- 新增 age 列
mysql> ALTER TABLE users ADD COLUMN age INT AFTER username;  -- 添加在 username 的后面
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 新增 nickname 列
mysql> ALTER TABLE users ADD COLUMN nickname VARCHAR(50) FIRST;  -- 添加在第一的位置
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table users \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `nickname` varchar(50) DEFAULT NULL,  -- 这里
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `age` int DEFAULT NULL,  -- 这里
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

修改列

修改 users 表的 age 字段的类型和名称，如下：

-- 修改 age 字段类型为 tinyint
mysql> ALTER TABLE users MODIFY COLUMN age TINYINT UNSIGNED;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 将 age 字段修改为 user_age 字段
mysql> ALTER TABLE users CHANGE COLUMN age user_age SMALLINT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table users \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `nickname` varchar(50) DEFAULT NULL,
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `user_age` tinyint unsigned DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

删除列

删除 users 表中的 nickname 列，如下：

mysql> ALTER TABLE users DROP COLUMN nickname;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table users \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `user_age` tinyint unsigned DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

添加索引

为 users 表的 age 字段添加普通索引，如下：

mysql> ALTER TABLE users ADD INDEX idx_age (user_age);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除索引

删除 users 表中的 idx_age 索引，如下：

mysql> ALTER TABLE users DROP INDEX idx_age;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改表名

将 users 表改为 user_accounts，如下：

mysql> ALTER TABLE users RENAME TO user_accounts;
Query OK, 0 rows affected (0.01 sec)

-- 再使用 rename table 将名称改回来
mysql> RENAME TABLE user_accounts TO users;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from users;
Empty set (0.00 sec)

表的复制

为了演示，下面将继续使用users表，并且添加两条数据，如下：

mysql> show create table users \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `user_age` tinyint unsigned DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

-- 插入数据
mysql> insert users(username,email,phone) values('张三','zhangsan@example.com','1234567890');
Query OK, 1 row affected (0.00 sec)

mysql> insert users(username,email,phone) values('李四','lisi@example.com','0987654321');
Query OK, 1 row affected (0.00 sec)

mysql> select * from users;
+---------+----------+----------+----------------------+------------+---------------------+
| user_id | username | user_age | email                | phone      | created_at          |
+---------+----------+----------+----------------------+------------+---------------------+
|       1 | 张三     |     NULL | zhangsan@example.com | 1234567890 | 2026-07-10 14:01:59 |
|       2 | 李四     |     NULL | lisi@example.com     | 0987654321 | 2026-07-10 14:02:04 |
+---------+----------+----------+----------------------+------------+---------------------+
2 rows in set (0.00 sec)

方法1：复制表结构（不含数据、不含索引）

-- 复制 users 表为 users_new 新表
mysql> CREATE TABLE users_new LIKE users;
Query OK, 0 rows affected (0.03 sec)

mysql> show create table users_new \G
*************************** 1. row ***************************
       Table: users_new
Create Table: CREATE TABLE `users_new` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `user_age` tinyint unsigned DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> select * from users_new;
Empty set (0.00 sec)

或者

mysql> CREATE TABLE users_empty AS SELECT * FROM users WHERE 1=0;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from users_empty;
Empty set (0.00 sec)

方法2：复制表结构和数据（不含索引、约束）

-- 复制 users 的表结构和表数据
mysql> CREATE TABLE users_backup AS SELECT * FROM users;
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from users_backup;
+---------+----------+----------+----------------------+------------+---------------------+
| user_id | username | user_age | email                | phone      | created_at          |
+---------+----------+----------+----------------------+------------+---------------------+
|       1 | 张三     |     NULL | zhangsan@example.com | 1234567890 | 2026-07-10 14:01:59 |
|       2 | 李四     |     NULL | lisi@example.com     | 0987654321 | 2026-07-10 14:02:04 |
+---------+----------+----------+----------------------+------------+---------------------+
2 rows in set (0.00 sec)

清空表

清空表有两种方式：

（1）DELETE：逐行删除，可回滚，记录日志，慢

mysql> DELETE FROM users;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from users;
Empty set (0.00 sec)

（2）TRUNCATE：删除整个表后重建，不可回滚，速度快

mysql> TRUNCATE TABLE users;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from users;
Empty set (0.00 sec)

查看表信息

表结构

mysql> DESCRIBE users;
+------------+------------------+------+-----+-------------------+-------------------+
| Field      | Type             | Null | Key | Default           | Extra             |
+------------+------------------+------+-----+-------------------+-------------------+
| user_id    | int              | NO   | PRI | NULL              | auto_increment    |
| username   | varchar(50)      | NO   |     | NULL              |                   |
| user_age   | tinyint unsigned | YES  |     | NULL              |                   |
| email      | varchar(100)     | NO   |     | NULL              |                   |
| phone      | varchar(20)      | YES  |     | NULL              |                   |
| created_at | timestamp        | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+------------------+------+-----+-------------------+-------------------+
6 rows in set (0.00 sec)

mysql> SHOW COLUMNS FROM users;
+------------+------------------+------+-----+-------------------+-------------------+
| Field      | Type             | Null | Key | Default           | Extra             |
+------------+------------------+------+-----+-------------------+-------------------+
| user_id    | int              | NO   | PRI | NULL              | auto_increment    |
| username   | varchar(50)      | NO   |     | NULL              |                   |
| user_age   | tinyint unsigned | YES  |     | NULL              |                   |
| email      | varchar(100)     | NO   |     | NULL              |                   |
| phone      | varchar(20)      | YES  |     | NULL              |                   |
| created_at | timestamp        | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+------------------+------+-----+-------------------+-------------------+
6 rows in set (0.00 sec)

建表语句

mysql> SHOW CREATE TABLE users \G
*************************** 1. row ***************************
       Table: users
Create Table: CREATE TABLE `users` (
  `user_id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `user_age` tinyint unsigned DEFAULT NULL,
  `email` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

表状态（包括引擎、行数、数据大小等）

mysql> SHOW TABLE STATUS LIKE 'users' \G
*************************** 1. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 1
    Create_time: 2026-07-10 13:57:44
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

输出结果字段说明：

  • Name  表名，这里是 users。

  • Engine  表使用的存储引擎，本例 InnoDB。

  • Version  表文件格式版本，InnoDB 一般为 10，内部标识文件存储格式。

  • Row_format   行存储格式：

    • Dynamic：动态行，变长字段（VARCHAR/TEXT）溢出会存到溢出页，InnoDB 默认；

    • Compressed/Redundant/Compact 是其他行格式。

  • Rows  MySQL 估算的总行数，InnoDB 是统计采样估算值，不是精确值；本例 0：表内目前无数据。

  • Avg_row_length  平均单行占用字节数，无数据时为 0。

  • Data_length  表数据部分占用字节数。本例 16384 = 16KB，InnoDB 即使空表也会分配最小页面（默认页 16KB）。

  • Max_data_length  该存储引擎理论支持的最大数据容量，InnoDB 该值无意义，显示 0。

  • Index_length  所有索引占用总字节数，本例 0 代表本表除主键外无二级索引（主键聚簇索引存在 Data_length 里）。

  • Data_free  表中空闲碎片空间（字节），大量删除数据后此处会有数值；0 代表无空闲碎片。

  • Auto_increment  下一条插入的自增 ID，这里是 1，说明从未插入过数据。

  • Create_time  表创建时间：2026-07-10 13:57:44。

  • Update_time  表数据最后修改时间，无数据更新则为 NULL。

  • Check_time  最后一次执行 CHECK TABLE 检查表的时间，未执行过则 NULL。

  • Collation  表默认字符集排序规则：utf8mb4_0900_ai_ci，支持完整 emoji。

  • Checksum  是否开启表校验和，NULL = 未开启。

  • Create_options  建表时附加选项（分区、压缩等），这里为空。

  • Comment  表注释，当前无注释。

查看表大小（MB）

mysql> SELECT
    ->     table_name,
    ->     ROUND(data_length/1024/1024, 2) AS data_size_mb,
    ->     ROUND(index_length/1024/1024, 2) AS index_size_mb,
    ->     ROUND((data_length+index_length)/1024/1024, 2) AS total_size_mb
    -> FROM information_schema.tables
    -> WHERE table_schema = 'shop' AND table_name = 'users';
+------------+--------------+---------------+---------------+
| TABLE_NAME | data_size_mb | index_size_mb | total_size_mb |
+------------+--------------+---------------+---------------+
| users      |         0.02 |          0.00 |          0.02 |
+------------+--------------+---------------+---------------+
1 row in set (0.00 sec)

字段计算说明：

存储单位底层是 Byte（字节）

换算：

  • /1024 → KB

  • 再 /1024 → MB

ROUND(xxx,2)：四舍五入保留 2 位小数

AS data_size_mb：给计算列起别名，方便查看

同理：

  • index_size_mb：索引总大小（MB）

  • total_size_mb：数据 + 索引合计大小（MB）

注意，其中 shop 是数据库名称。

  

上一章：MySQL 表的存储引擎
说说我的看法
全部评论（）
没有评论
关于
本网站专注于 Java、数据库（MySQL、Oracle）、Linux、软件架构及大数据等多领域技术知识分享。涵盖丰富的原创与精选技术文章，助力技术传播与交流。无论是技术新手渴望入门，还是资深开发者寻求进阶，这里都能为您提供深度见解与实用经验，让复杂编码变得轻松易懂，携手共赴技术提升新高度。如有侵权，请来信告知：hxstrive@outlook.com
链接
其他应用
公众号
©2018-2020 hxstrive.com 版权所有 ICP证：蜀ICP备14031461号-2   川公网安备51010802032098