MYSQL【表操作】

【MySQL 笔记】表操作DDL - Data Definition Language详解MySQL 中对表的操作主要属于 DDL数据定义语言包括创建表、修改表、删除表、重命名表、复制表、查看表结构等。下面按常用程度和重要性排序逐一讲解。一、核心表操作命令速查表操作命令主要用途是否可回滚事务内是否锁表大表时常用场景创建表CREATE TABLE定义新表结构否否新建业务表修改表结构ALTER TABLE加/删/改列、改约束、改引擎、改字符集否是多数操作需求变更、字段调整删除表DROP TABLE永久删除表及数据否短暂清理测试表、卸载模块清空表数据TRUNCATE TABLE快速清空所有数据保留表结构否是重置自增、清理大表重命名表RENAME TABLE / ALTER TABLE … RENAME修改表名否短暂表名规范化、版本迁移复制表结构数据CREATE TABLE … LIKE / SELECT快速复制表否视数据量备份、分表、测试环境查看表结构SHOW CREATE TABLE / DESC / EXPLAIN查看建表语句或列信息—否开发、运维、排查二、详细语法 实战示例1. 创建表CREATE TABLE最完整写法推荐生产使用CREATETABLEIFNOTEXISTSorder_items(idBIGINTUNSIGNEDAUTO_INCREMENTCOMMENT自增主键,order_idBIGINTUNSIGNEDNOTNULLCOMMENT订单ID,product_idBIGINTUNSIGNEDNOTNULLCOMMENT商品ID,sku_idBIGINTUNSIGNEDDEFAULTNULLCOMMENTSKU ID,quantityINTUNSIGNEDNOTNULLDEFAULT1COMMENT购买数量,unit_priceDECIMAL(10,2)NOTNULLCOMMENT单价,subtotalDECIMAL(10,2)GENERATED ALWAYSAS(quantity*unit_price)STOREDCOMMENT小计计算列,created_atDATETIME(3)NOTNULLDEFAULTCURRENT_TIMESTAMP(3)COMMENT创建时间,updated_atDATETIME(3)NOTNULLDEFAULTCURRENT_TIMESTAMP(3)ONUPDATECURRENT_TIMESTAMP(3)COMMENT更新时间,PRIMARYKEY(id),INDEXidx_order_id(order_id),INDEXidx_product_id(product_id),CONSTRAINTfk_order_items_orderFOREIGNKEY(order_id)REFERENCESorders(id)ONDELETECASCADEONUPDATECASCADE,CONSTRAINTfk_order_items_productFOREIGNKEY(product_id)REFERENCESproducts(id)ONDELETERESTRICTONUPDATECASCADE)ENGINEInnoDBDEFAULTCHARSETutf8mb4COLLATEutf8mb4_unicode_ciCOMMENT订单明细表;常用变体-- 只复制结构不带数据CREATETABLEusers_bakLIKEusers;-- 复制结构 数据可加 WHERE 过滤CREATETABLEusers_2025ASSELECT*FROMusersWHEREcreated_at2025-01-01;2. 修改表ALTER TABLE—— 最复杂的操作-- 1. 添加列ALTERTABLEusersADDCOLUMNlast_login_ipVARCHAR(45)DEFAULTNULLAFTERlast_login_time,ADDCOLUMNis_vipTINYINT(1)DEFAULT0AFTERstatus;-- 2. 修改列定义类型、默认值、注释等ALTERTABLEproductsMODIFYCOLUMNpriceDECIMAL(12,2)NOTNULLDEFAULT0.00COMMENT售价升级精度;-- 3. 重命名列ALTERTABLEorders CHANGECOLUMNamt amountDECIMAL(10,2)NOTNULLCOMMENT订单金额;-- 4. 删除列不可恢复ALTERTABLEtemp_tableDROPCOLUMNtemp_field;-- 5. 添加/删除索引ALTERTABLEusersADDINDEXidx_mobile(mobile);ALTERTABLEusersDROPINDEXidx_mobile;-- 6. 添加/删除主键极少用大表慎重ALTERTABLEold_tableADDPRIMARYKEY(id);ALTERTABLEold_tableDROPPRIMARYKEY;-- 7. 修改表引擎 / 字符集常见迁移操作ALTERTABLEusersENGINEInnoDB;ALTERTABLElogsCONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;-- 8. 修改表名两种写法等价RENAMETABLEusersTOusers_v2;ALTERTABLEusers_v2RENAMETOusers;大表 ALTER TABLE 注意事项2026 年视角MySQL 8.0 大部分 ALTER 操作支持在线 DDLinplace但仍可能短暂锁表。推荐做法用 pt-online-schema-change / gh-ost 等工具做无锁变更。永远先在测试环境验证 ALTER 语句的执行时间和锁情况。3. 删除表 清空表-- 删除表不可恢复DROPTABLEIFEXISTStemp_table;-- 删除多张表DROPTABLEt1,t2,t3;-- 快速清空数据保留表结构自增重置为1TRUNCATETABLElog_temp;-- 对比DELETE FROM 不重置自增且可 WHERE可回滚DELETEFROMlog_tempWHEREcreate_time2025-01-01;4. 查看表信息开发/运维必备-- 1. 查看建表语句最完整SHOWCREATETABLEusers\G-- 2. 查看列信息简洁DESCusers;DESCRIBEusers;-- 3. 查看所有表SHOWTABLES;SHOWTABLESLIKEorder%;-- 4. 查看表状态行数、数据大小、索引大小等SHOWTABLESTATUSLIKEusers;-- 5. 查看表分区如果用了分区表SHOWCREATETABLEsales PARTITIONS;三、生产中表操作最佳实践总结建表必须写COMMENT字段注释 表注释统一字符集utf8mb4_unicode_ci2026 年主流主键优先用BIGINT UNSIGNED AUTO_INCREMENT字段命名下划线命名法见名知意重要表加created_at / updated_at自动维护避免频繁 ALTER TABLE 大表提前规划好字段测试环境先跑 DROP / TRUNCATE / ALTER确认无误核心业务表建议加软删除is_deleted deleted_at下一期预告建议MySQL 索引全家桶上—— 索引类型 创建时机 复合索引最左匹配原则有哪种表结构变更、复制表、或 ALTER 操作你觉得特别麻烦或者项目里常见的表操作模板欢迎留言讨论祝大家表操作稳准狠不乱改结构️