MySQL 表数据清空、删除与结构修改操作详解

一、清除表中所有数据

在 MySQL 中，若需移除表内的所有内容，主要使用 DELETE 和 TRUNCATE 两种命令。两者虽结果相同，但机制与后果存在显著差异：

1. DELETE FROM 表名;：逐行删除记录。该操作会记录 SQL 日志（Binlog），因此数据理论上可恢复，但执行速度较慢，尤其是面对大数据量时。
2. TRUNCATE TABLE 表名;：直接重置表结构，相当于删除旧表并重建一张新表。此操作不记录单条删除日志，效率远高于 DELETE，但删除后的数据不可恢复，且自增ID通常会重置为1。

二、删除特定条件记录

若只需移除满足特定条件的行，应使用带有 WHERE 子句的 DELETE 命令。

DELETE FROM 表名 WHERE 表达式;

例如，删除 MyClass 表中 id 为 1 的记录：

mysql> DELETE FROM MyClass WHERE id=1;

三、修改表结构与字段

在进行任何高风险的结构变更前，建议先备份数据库。以下以命令行方式使用 ALTER TABLE 进行演示：

第1步：基础环境准备

首先选择目标数据库，并确认表结构是否存在：

-- 选择数据库
USE 数据库名;
-- 查看所有表
SHOW TABLES;
-- 查看指定表的结构
DESC 表名称;

第2步：修改字段类型与非空约束

如需调整某列的数据类型或是否允许为空，可使用 CHANGE 或 MODIFY。

• 仅修改类型或非空性：

ALTER TABLE 表名称 MODIFY 字段名称 字段类型 [是否允许非空];

• 修改字段名称同时调整类型/非空性：

ALTER TABLE 表名称 CHANGE 字段原名称 字段新名称 字段类型 [是否允许非空];

示例：将 expert_info 表的 birth 字段修改为 varchar(20) 且允许为空：

ALTER TABLE expert_info CHANGE birth birth VARCHAR(20) NULL;

第3步：增加新字段

向现有表中添加新的一列：

ALTER TABLE table_name ADD COLUMN column_name type DEFAULT value;

示例：在 mybook 表中添加出版商字段，默认为空字符串：

ALTER TABLE mybook ADD COLUMN publish_house VARCHAR(10) DEFAULT '';

第4步：更改字段名称

除了改类型，CHANGE 也可用于重命名字段：

ALTER TABLE table_name CHANGE sorce_col_name dest_col_name type DEFAULT value;

示例：将 Board_Info 中的 IsMobile 改为 IsTelphone，类型为 int：

ALTER TABLE Board_Info CHANGE IsMobile IsTelphone INT(3) UNSIGNED DEFAULT 1;

第5步：修改字段默认值

仅改变某列的默认数值，而不涉及类型或名称：

ALTER TABLE table_name ALTER column_name SET DEFAULT value;

示例：设置 book 表中 flag 字段的默认值为 '0'：

ALTER TABLE book ALTER flag SET DEFAULT '0';

第6步：修改字段数据类型

若不改名，仅改变类型，仍需使用 CHANGE 或 MODIFY：

ALTER TABLE table_name CHANGE column column_name new_type;
-- 或者
ALTER TABLE table_name MODIFY column_name new_type;

第7步：增加主键

为表新增一个自增主键列：

ALTER TABLE table_name ADD COLUMN column_name type AUTO_INCREMENT PRIMARY KEY;

示例：给 book 表添加自增 ID 主键：

ALTER TABLE book ADD COLUMN id INT(10) AUTO_INCREMENT PRIMARY KEY;

第8步：删除字段

移除表中不需要的列：

ALTER TABLE form1 DROP COLUMN 列名;

四、数据备份、导入与高级操作

部分操作需在服务器命令行终端执行，而非直接在 MySQL 客户端中运行。

1. 表数据备份 (mysqldump)

在系统命令行中使用 mysqldump 工具导出表结构及数据：

mysqldump -u root -p database_name table_name > bak_file_name

示例：

mysqldump -u root -p f_info user_info > user_info.dat

2. 导出数据 (SELECT INTO OUTFILE)

在 MySQL 客户端中将查询结果导出为文件：

SELECT field_list FROM table_name INTO OUTFILE 'dest_file';

注意：导出路径需确保 MySQL 服务进程有写入权限。

SELECT cooperatecode, createtime FROM publish LIMIT 10 INTO OUTFILE '/home/mzc/temp/tempbad.txt';

3. 导入数据 (LOAD DATA INFILE)

将外部文件快速加载到表中：

LOAD DATA INFILE 'file_name' INTO TABLE table_name;

示例：

LOAD DATA INFILE '/home/mzc/temp/tempbad.txt' INTO TABLE pad;

4. 跨表数据拼接插入

从不同表取值拼接后插入目标表：

INSERT INTO tx SELECT t1.com1, CONCAT(t1.com2, t2.com1) FROM t1, t2;

此操作需谨慎，确保源表匹配逻辑符合预期，避免产生错误数据。

总结

以上是 MySQL 表数据清空、删除及结构修改的详细内容。在实际生产环境中，执行 DROP 或 TRUNCATE 等不可逆操作前，务必确认备份已就绪。更多关于 MySQL 进阶优化的资料请关注本站其它相关文章！