3.表的操作【由浅入深-MySQL】

文章目录表的创建与基础查看1.1 建表语句CREATE TABLE的完整形态1.1.1 列定义与注释1.1.2 表选项字符集、校验规则与存储引擎1.1.3 IF NOT EXISTS 的作用1.2 查看表结构DESC 命令1.3 SHOW CREATE TABLE被优化过的真实定义1.3.1 优化表现一语法标准化1.3.2 优化表现二默认值的显式补充1.3.3 优化表现三注释与索引的规范化1.3.4 实际输出示例模拟1.4 修改表ALTER TABLE 的完整变迁操作1.4.1 表的重命名RENAME1.4.2 新增列ADD COLUMN历史数据的填充逻辑1.4.3 修改列定义MODIFY / CHANGE覆盖式更新的深度陷阱1.4.4 删除列DROP COLUMN不可逆操作的风险警示1.5 删除表DROP TABLE 的彻底清理表的创建与基础查看在 MySQL 中表是数据存储的核心容器。本章将从最基础的建表语句出发逐步讲解如何查看表结构并深入剖析SHOW CREATE TABLE的输出特性。掌握这些内容是后续所有数据操作的前提。1.1 建表语句CREATE TABLE的完整形态一个标准的建表语句不仅需要定义列名和数据类型还应考虑字符集、校验规则、存储引擎等物理属性。语法CREATETABLEtable_name(field1 datatype,field2 datatype,field3 datatype)characterset字符集collate校验规则engine存储引擎;下面以两个实际例子展开CREATETABLEIFNOTEXISTSuser1(idINT,nameVARCHAR(20)COMMENT用户名,passwordCHAR(32)COMMENT用户的密码,birthdayDATECOMMENT用户的生日)CHARACTERSETutf8COLLATEutf8_general_ciENGINEMyISAM;CREATETABLEIFNOTEXISTSuser2(idINT,nameVARCHAR(20)COMMENT用户名,passwordCHAR(32)COMMENT用户的密码,birthdayDATECOMMENT用户的生日)CHARSETutf8COLLATEutf8_general_ciENGINEInnoDB;不同的存储引擎创建表的文件不一样。users 表存储引擎是 MyISAM 在数据目中有三个不同的文件分别是users.frm表结构users.MYD表数据users.MYI表索引1.1.1 列定义与注释每个列定义由列名 数据类型构成可选COMMENT用于添加描述性注释。注释应简明扼要例如用户名直接说明字段含义便于后期维护和文档生成。COMMENT 是数据库对象的说明信息保存在 MySQL 元数据中主要用于 SHOW CREATE TABLE、SHOW FULL COLUMNS 和数据库管理工具查看查询数据时不会显示也不会影响 SQL 执行。INT整型默认长度为 11显示宽度存储范围为 -2147483648 ~ 2147483647。VARCHAR(20)可变长字符串最大长度为 20 个字符注意MySQL 中VARCHAR长度指字符数而非字节数实际占用空间取决于字符集。CHAR(32)定长字符串总是占用 32 个字符的存储空间不足时补空格适合存储固定长度的数据如 MD5 加密后的密码。DATE日期类型格式为YYYY-MM-DD范围从1000-01-01到9999-12-31。1.1.2 表选项字符集、校验规则与存储引擎表选项位于)之后用于定义表的全局属性。字符集CHARACTER SET / CHARSET指定表中字符列使用的编码。utf8是通用的 UTF-8 编码支持大部分语言字符。注意 MySQL 中的utf8实际为三字节编码utf8mb3若需完整 Emoji 等四字节字符应使用utf8mb4。校验规则COLLATE决定字符比较和排序的规则。utf8_general_ci是不区分大小写的通用校验规则ci即case insensitive。不同的校验规则会影响查询时的ORDER BY和WHERE条件中的字符串比较结果。存储引擎ENGINE指定表的物理存储机制。示例中分别使用了MyISAM和InnoDB。MyISAM早期默认引擎不支持事务和外键但查询速度快适合读多写少的场景。InnoDB当前 MySQL 默认引擎支持事务、行级锁、外键约束具备崩溃恢复能力是绝大多数生产环境的首选。写法差异CHARACTER SET utf8与CHARSET utf8等价ENGINE MyISAM与ENGINE InnoDB等价。等号可有可无但推荐统一风格以增强可读性。1.1.3IF NOT EXISTS的作用该子句用于避免重复建表时报错。如果表已存在则语句不会执行创建操作也不会返回错误仅显示一个警告可通过SHOW WARNINGS查看。这在脚本化部署中非常实用。1.2 查看表结构DESC 命令建表完成后最常用的查看命令是DESC或DESCRIBE它返回表的列信息包括字段名、类型、是否允许 NULL、键类型、默认值和额外属性。DESC表名;Null列显示YES表示该列允许存储NULL值若不指定NOT NULL默认可为 NULL。Key列指示索引类型PRI主键、UNI唯一键、MUL非唯一索引。Default列显示默认值未显式指定时默认为NULL除非字段有NOT NULL且无默认值此时行为取决于严格模式。Extra列包含额外信息如auto_increment、on update CURRENT_TIMESTAMP等。DESC仅展示结构骨架不显示字符集、存储引擎等表级选项。若要获取更完整的元数据需使用SHOW CREATE TABLE。1.3 SHOW CREATE TABLE被优化过的真实定义SHOW CREATE TABLE是查看表完整定义的最佳工具它返回一条重建该表的CREATE TABLE语句。但在使用时需注意该语句返回的建表语句并非你原始书写的文本而是经过 MySQL 解析和优化后的标准化版本。SHOWCREATETABLEuser1\G使用\G替代分号可将结果以垂直列形式展示避免字段过长导致折行便于阅读。1.3.1 优化表现一语法标准化MySQL 会对关键字、选项写法进行统一。例如你原始写CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE MyISAM返回的语句可能变为ENGINEMyISAM DEFAULT CHARSETutf8 COLLATEutf8_general_ci。MySQL 会调整顺序统一使用赋值并补充DEFAULT关键字但语义不变。1.3.2 优化表现二默认值的显式补充如果建表时未显式指定某些选项MySQL 会补上当前会话或全局的默认值。例如若未指定ROW_FORMAT返回的语句可能包含ROW_FORMATDYNAMICInnoDB 默认。字符集和校验规则若未指定也会继承数据库级别的设置并在SHOW CREATE TABLE中明确写出。1.3.3 优化表现三注释与索引的规范化列注释原样保留但会使用单引号包裹。若存在索引如PRIMARY KEYMySQL 会将其整合到CREATE TABLE中并统一使用USING BTREE等显式索引类型。表选项如AUTO_INCREMENT当前值也会被记录但该值可能随插入操作变化。1.3.4 实际输出示例模拟CREATE TABLE user1 ( id int(11) DEFAULT NULL, name varchar(20) DEFAULT NULL COMMENT 用户名, password char(32) DEFAULT NULL COMMENT 用户的密码, birthday date DEFAULT NULL COMMENT 用户的生日 ) ENGINEMyISAM DEFAULT CHARSETutf8 COLLATEutf8_general_ci观察发现所有列都被添加了DEFAULT NULL因为我们建表时未指定NOT NULLMySQL 显式补全了默认值。表名和列名被反引号包裹以兼容特殊关键字或保留字。存储引擎和字符集被调整为标准格式。重要提醒SHOW CREATE TABLE输出的语句是 MySQL 内部存储的真实定义当你需要迁移表或重建表时应优先使用该输出而非依赖原始手写脚本因为它反映了当前表的实际物理状态包括自动添加的选项和默认值。1.4 修改表ALTER TABLE 的完整变迁操作ALTER TABLE是 MySQL 中最强大的结构变更命令。它可以同时包含多个操作以逗号分隔但为了清晰演示我们通常分步执行。本节将从表重命名开始逐一深入到列级的增删改操作。1.4.1 表的重命名RENAME在业务初建时表名可能不够规范或需要将测试表转为正式表此时必须修改表名。MySQL 提供了两种完全等价的重命名语法。语法一标准 ALTER 方式ALTERTABLE旧表名RENAMETO新表名;语法二专用 RENAME 方式RENAMETABLE旧表名TO新表名;例如将前文中的user表更名为users这一操作将为后续修改列名的示例做好铺垫ALTERTABLEuserRENAMETOusers;-- 或 RENAME TABLE user TO users;操作要点与底层机制RENAME操作仅修改数据字典系统表中的表名映射不涉及物理数据文件的移动MyISAM 引擎会重命名.frm、.MYD、.MYI文件InnoDB 则仅更新表空间内的元数据因此执行速度极快几乎不阻塞并发查询在 5.7 及更高版本中大多数引擎支持原子性重命名。权限继承重命名后的表会保留原表的所有权限设置GRANT赋予的权限不受影响。跨数据库重命名RENAME TABLE db1.old_name TO db2.new_name;可以实现表在不同数据库间的移动但要求两个数据库位于同一文件系统且涉及 InnoDB 时需注意表空间文件位置。1.4.2 新增列ADD COLUMN历史数据的填充逻辑当产品需要增加新属性时ADD子句派上用场。以下是一段典型的字段新增操作ALTERTABLEuserADDimage_pathVARCHAR(128)COMMENT这个是用户的头像路径AFTERbirthday;执行后查询结果中两条历史记录的image_path均显示为NULL。语法全量解析ALTERTABLE表名ADD[COLUMN]列名 数据类型[约束][COMMENT注释][FIRST|AFTER已有列名];COLUMN关键字为可选写上可增强可读性。FIRST将新列添加为表的第一列。AFTER 列名将新列插入到指定列的后面。若不指定FIRST或AFTER新列默认追加到表的最后一列。核心知识点新增列时历史数据的处理机制重点这是一个极易被忽视的关键行为。当执行ADD COLUMN且未显式指定DEFAULT默认值时如果新列允许NULL值即未指定NOT NULLMySQL 会将所有已存在的行中该列的值填充为NULL。截图中的image_path全部为NULL正是此因。如果新列定义为NOT NULL且未指定默认值MySQL 的行为取决于sql_mode是否启用严格模式严格模式下STRICT_TRANS_TABLES会直接报错拒绝执行要求必须提供DEFAULT默认值。非严格模式下MySQL 会默认填充该数据类型的“隐式默认值”如数值型为0字符串为日期为0000-00-00并产生一个警告。高效操作建议在大表千万级数据中新增列时建议使用ALGORITHMINPLACEInnoDB 支持并加上DEFAULT值避免因NULL填充导致的重建表锁表时间过长。如果必须添加NOT NULL列应分步执行先添加允许NULL的列更新完业务数据后再执行MODIFY改为NOT NULL。1.4.3 修改列定义MODIFY / CHANGE覆盖式更新的深度陷阱修改列存在两种不同的命令语法分别应对“不改名只改类型/约束”和“改名同时改定义”的需求。场景一使用 MODIFY仅修改属性不改列名ALTERTABLEuserMODIFYnameVARCHAR(60);这条语句将name列的最大长度从 20 扩展到 60。完整语法ALTER TABLE 表名 MODIFY [COLUMN] 列名 数据类型 [约束] [FIRST | AFTER 列名];场景二使用 CHANGE修改列名同时可修改属性ALTERTABLEusers CHANGE name xingmingVARCHAR(60)DEFAULTNULL;这条语句将name列更名为xingming同时将数据类型改为VARCHAR(60)并显式设置了默认值为NULL。完整语法ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 数据类型 [约束] [FIRST | AFTER 列名];⚠️ 极度重要的“覆盖”特性Caveat无论是MODIFY还是CHANGE它们在执行时都遵循完全覆盖的逻辑而非“增量修改”。这意味着你写在MODIFY/CHANGE子句中的内容将完整替换该列的现有定义。未在语句中明确写出的属性将丢失并被重置为默认值。举例说明假设原列定义为name VARCHAR(20) NOT NULL COMMENT 用户名。如果执行ALTER TABLE user MODIFY name VARCHAR(60);注意这里没写NOT NULL也没写COMMENT该列会变成VARCHAR(60) DEFAULT NULL原有的NOT NULL约束和COMMENT注释将彻底消失。这就是“覆盖”的含义。最佳实践在执行MODIFY或CHANGE之前务必先执行SHOW CREATE TABLE 表名\G获取完整的当前列定义包括注释、默认值、是否可为空然后在修改语句中原样保留所有不想变更的属性仅修改目标部分。例如保留注释和NOT NULL的正确写法为ALTERTABLEuserMODIFYnameVARCHAR(60)NOTNULLCOMMENT用户名;1.4.4 删除列DROP COLUMN不可逆操作的风险警示当业务下线某些功能时我们需要移除不再使用的字段。ALTERTABLEuserDROPpassword;语法ALTER TABLE 表名 DROP [COLUMN] 列名;执行机制与风险DROP COLUMN会从表的每一行中移除该列数据并在数据字典中删除该列定义。对于 InnoDB 引擎此操作会重建表除非使用ALGORITHMINSTANT且 MySQL 8.0 支持某些即时删除场景期间会占用额外的磁盘空间和锁表时间。此操作为永久性、不可逆操作。一旦执行该列的数据将物理删除除非在备份或 Binlog 中留有记录。在生产环境中执行前务必备份数据或在测试环境验证。如果一个表包含大量数据DROP COLUMN可能触发大量的 I/O 负载建议在业务低峰期执行。1.5 删除表DROP TABLE 的彻底清理当整个业务模块被废弃时我们需要删除整张表及其所有数据。这属于最高级别的清理操作。语法DROPTABLE[IFEXISTS]表名1[,表名2,...];执行细节与恢复机制该语句会删除表的全部数据行、表结构定义以及与该表相关的触发器、索引、权限等元数据。对于 InnoDB 引擎DROP TABLE会立即释放表空间文件.ibd占用的磁盘空间操作系统级别立即回收。数据恢复DROP TABLE操作无法通过ROLLBACK回滚因为 DDL 具有隐式提交特性。唯一的恢复手段是依赖之前的物理备份如mysqldump或从 Binlog 中重建数据。IF EXISTS 子句的防护价值与建表时的IF NOT EXISTS对称IF EXISTS可以避免因表不存在而抛出错误ERROR 1051 (42S02)。它仅产生一个警告非常适合运行在自动化脚本中保证脚本的健壮性。例如DROPTABLEIFEXISTStemp_log;级联风险如果存在外键约束引用了该表例如其他表通过FOREIGN KEY指向本表的主键DROP TABLE会失败并报错。此时需要先删除相关的外键约束或使用SET FOREIGN_KEY_CHECKS 0;临时禁用检查务必谨慎禁用可能导致数据完整性被破坏。补充具体的后续章节讲解表中插入数据insertintostudent(id,name,gender)values(1,张三,男);insertintostudent(id,name,gender)values(2,李四,女);insertintostudent(id,name,gender)values(3,王五,男);查询表中的数据select*fromstudent;