MySQL 表创建、修改与删除

表创建

1. 基本语法

sql

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraint],
    column2 datatype [constraint],
    ...
    [table_constraints]
) ENGINE = engine_type DEFAULT CHARSET = charset_name;

2. 数据类型选择

数值类型

类型	大小	范围	用途
TINYINT	1字节	-128 到 127	小整数值
SMALLINT	2字节	-32768 到 32767	大整数值
INT	4字节	-2147483648 到 2147483647	大整数值
BIGINT	8字节	-9223372036854775808 到 9223372036854775807	极大整数值
DECIMAL(M,D)	可变	依赖于M和D	精确小数

字符串类型

类型	大小	用途
CHAR(1-255)	固定长度	短字符串，如身份证号
VARCHAR(1-65535)	可变长度	变长字符串，如用户名
TEXT	65535字节	长文本数据
LONGTEXT	4GB	极大文本数据

日期时间类型

类型	大小	格式	用途
DATE	3字节	YYYY-MM-DD	日期值
TIME	3字节	HH:MM:SS	时间值
DATETIME	8字节	YYYY-MM-DD HH:MM:SS	日期和时间
TIMESTAMP	4字节	YYYY-MM-DD HH:MM:SS	时间戳，自动更新

3. 约束设置

主键约束

sql

-- 在列定义中设置
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

-- 表级约束
CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

外键约束

sql

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    order_date DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

唯一约束

sql

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE
);

非空约束

sql

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20)
);

4. 表创建最佳实践

sql

-- 完整的表创建示例
CREATE TABLE `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `username` VARCHAR(50) NOT NULL COMMENT '用户名',
  `email` VARCHAR(100) NOT NULL COMMENT '电子邮箱',
  `password_hash` VARCHAR(255) NOT NULL COMMENT '密码哈希',
  `status` TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态：1-活跃，2-禁用',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`),
  UNIQUE KEY `uk_email` (`email`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

表修改

1. 修改表名

sql

ALTER TABLE old_table_name RENAME TO new_table_name;

2. 添加列

sql

-- 添加单列
ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;

-- 添加多列
ALTER TABLE users 
ADD COLUMN address VARCHAR(255) NOT NULL DEFAULT '',
ADD COLUMN birthday DATE;

3. 修改列

sql

-- 修改数据类型
ALTER TABLE users MODIFY COLUMN phone VARCHAR(30);

-- 修改列名和数据类型
ALTER TABLE users CHANGE COLUMN old_column new_column VARCHAR(100) NOT NULL;

-- 修改默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 1;

4. 删除列

sql

-- 删除单列
ALTER TABLE users DROP COLUMN phone;

-- 删除多列
ALTER TABLE users DROP COLUMN address, DROP COLUMN birthday;

5. 修改约束

sql

-- 添加唯一约束
ALTER TABLE users ADD UNIQUE KEY uk_phone (phone);

-- 删除约束
ALTER TABLE users DROP INDEX uk_phone;

-- 添加外键约束
ALTER TABLE orders ADD FOREIGN KEY fk_user (user_id) REFERENCES users(id);

-- 删除外键约束
ALTER TABLE orders DROP FOREIGN KEY fk_user;

6. 修改存储引擎

sql

ALTER TABLE users ENGINE = InnoDB;

7. 修改字符集

sql

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

表删除

1. 基本语法

sql

-- 删除表
DROP TABLE [IF EXISTS] table_name;

-- 删除多个表
DROP TABLE [IF EXISTS] table1, table2, ...;

2. 临时表删除

sql

-- 创建临时表
CREATE TEMPORARY TABLE temp_users AS SELECT * FROM users WHERE status = 1;

-- 删除临时表
DROP TEMPORARY TABLE IF EXISTS temp_users;

性能考虑

1. 表创建性能

避免过多列：表列数不宜超过100列，过多列会影响查询性能
合理设置数据类型：选择最小且合适的数据类型，减少存储空间和I/O开销
适当添加索引：根据查询需求添加索引，但避免过度索引
使用分区表：对于大数据量表，考虑使用分区表提高查询性能

2. 表修改性能

在线DDL：使用InnoDB引擎的在线DDL功能，减少表锁定时间

sql

ALTER TABLE users ADD COLUMN phone VARCHAR(20), ALGORITHM=INPLACE, LOCK=NONE;

避免大表频繁修改：大表结构修改会消耗大量资源，建议在低峰期执行
考虑使用pt-online-schema-change：对于超大表，使用Percona Toolkit工具进行在线表结构变更

3. 表删除性能

删除大表：使用DROP TABLE删除大表会导致大量I/O，建议使用TRUNCATE TABLE先清空数据再删除
sql
```
TRUNCATE TABLE large_table;
DROP TABLE large_table;
```
注意外键约束：删除有外键引用的表会失败，需要先删除外键约束或引用表

最佳实践

1. 命名规范

表名：使用小写字母，下划线分隔，如 user_profile
列名：使用小写字母，下划线分隔，如 user_id
约束名：使用前缀+列名，如 pk_user_id（主键）、uk_username（唯一键）、fk_order_user（外键）
注释：为表和列添加清晰的注释，便于维护

2. 设计原则

遵循第一范式：确保每列原子性
合理使用范式：根据业务需求选择合适的范式级别
考虑查询需求：根据实际查询模式设计表结构和索引
预留扩展空间：为未来业务扩展预留适当的字段或使用JSON类型存储动态数据

3. 安全操作

使用IF NOT EXISTS：创建表时使用IF NOT EXISTS避免重复创建错误
使用IF EXISTS：删除表时使用IF EXISTS避免表不存在错误
备份重要数据：在修改或删除表前，备份相关数据
在测试环境验证：所有DDL操作先在测试环境验证，再在生产环境执行

常见问题与解决方案

1. 表创建失败

问题：创建表时提示"ERROR 1005 (HY000): Can't create table"

解决方案：

检查外键引用的表或列是否存在
检查外键数据类型是否与被引用列一致
检查存储引擎是否支持外键（如MyISAM不支持外键）
检查字符集和校对规则是否一致

2. 表修改卡住

问题：执行ALTER TABLE操作时卡住

解决方案：

检查是否有长事务在运行，导致表锁定
使用SHOW PROCESSLIST查看当前进程状态
使用SHOW ENGINE INNODB STATUS查看InnoDB状态
考虑使用在线DDL或pt-online-schema-change工具

3. 无法删除表

问题：执行DROP TABLE时提示"ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails"

解决方案：

先删除引用该表的外键约束
或先删除引用该表的子表
或使用SET FOREIGN_KEY_CHECKS=0临时禁用外键检查（谨慎使用）

4. 表空间不足

问题：创建或修改表时提示"ERROR 1114 (HY000): The table 'table_name' is full"

解决方案：

检查磁盘空间是否充足
检查表空间大小限制
对于InnoDB表，检查innodb_data_file_path配置
考虑清理无用数据或扩展磁盘空间

常见问题（FAQ）

Q1: CREATE TABLE和CREATE TABLE IF NOT EXISTS有什么区别？

A1: CREATE TABLE在表已存在时会报错，而CREATE TABLE IF NOT EXISTS在表已存在时会忽略创建操作，不会报错。建议在脚本中使用后者，提高脚本的健壮性。

Q2: 如何查看表的创建语句？

A2: 使用SHOW CREATE TABLE命令查看表的完整创建语句：

sql

SHOW CREATE TABLE table_name;

Q3: 什么是在线DDL？

A3: 在线DDL是InnoDB引擎的特性，允许在修改表结构时，不阻塞读写操作。可以通过ALGORITHM和LOCK参数控制DDL操作的行为。

Q4: TRUNCATE TABLE和DELETE FROM有什么区别？

A4:

TRUNCATE TABLE：删除表中所有数据，不记录日志，速度快，不能回滚
DELETE FROM：逐行删除数据，记录日志，速度慢，可以回滚
TRUNCATE TABLE会重置自增列，而DELETE不会

Q5: 如何修改表的自增列起始值？

A5: 使用ALTER TABLE命令修改自增列起始值：

sql

ALTER TABLE users AUTO_INCREMENT = 1000;

Q6: 如何复制表结构？

A6: 使用CREATE TABLE ... LIKE命令复制表结构（包括索引）：

sql

CREATE TABLE new_table LIKE old_table;

或使用CREATE TABLE ... SELECT命令复制表结构和数据：

sql

CREATE TABLE new_table SELECT * FROM old_table WHERE 1=0; -- 只复制结构
CREATE TABLE new_table SELECT * FROM old_table; -- 复制结构和数据

Q7: 如何查看表的结构？

A7: 使用DESCRIBE或SHOW COLUMNS命令：

sql

DESCRIBE table_name;
-- 或
SHOW COLUMNS FROM table_name;

Q8: 如何重命名表中的列？

A8: 使用ALTER TABLE ... CHANGE COLUMN命令：

sql

ALTER TABLE table_name CHANGE COLUMN old_column_name new_column_name datatype [constraints];

Q9: 如何删除表中的所有数据但保留表结构？

A9: 可以使用TRUNCATE TABLE或DELETE FROM命令：

sql

TRUNCATE TABLE table_name; -- 速度快，不记录日志
-- 或
DELETE FROM table_name; -- 速度慢，记录日志

Q10: 如何查看表的存储引擎？

A10: 使用SHOW TABLE STATUS命令：

sql

SHOW TABLE STATUS LIKE 'table_name';

或查询INFORMATION_SCHEMA表：

sql

SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name';

MySQL 表创建、修改与删除 ​

表创建 ​

1. 基本语法 ​

2. 数据类型选择 ​

数值类型 ​

字符串类型 ​

日期时间类型 ​

3. 约束设置 ​

主键约束 ​

外键约束 ​

唯一约束 ​

非空约束 ​

4. 表创建最佳实践 ​

表修改 ​

1. 修改表名 ​

2. 添加列 ​

3. 修改列 ​

4. 删除列 ​

5. 修改约束 ​

6. 修改存储引擎 ​

7. 修改字符集 ​

表删除 ​

1. 基本语法 ​

2. 临时表删除 ​

性能考虑 ​

1. 表创建性能 ​

2. 表修改性能 ​

3. 表删除性能 ​

最佳实践 ​

1. 命名规范 ​

2. 设计原则 ​

3. 安全操作 ​

常见问题与解决方案 ​

1. 表创建失败 ​

2. 表修改卡住 ​

3. 无法删除表 ​

4. 表空间不足 ​

常见问题（FAQ） ​

Q1: CREATE TABLE和CREATE TABLE IF NOT EXISTS有什么区别？ ​

Q2: 如何查看表的创建语句？ ​

Q3: 什么是在线DDL？ ​

Q4: TRUNCATE TABLE和DELETE FROM有什么区别？ ​

Q5: 如何修改表的自增列起始值？ ​

Q6: 如何复制表结构？ ​

Q7: 如何查看表的结构？ ​

Q8: 如何重命名表中的列？ ​

Q9: 如何删除表中的所有数据但保留表结构？ ​

Q10: 如何查看表的存储引擎？ ​

MySQL 表创建、修改与删除

表创建

1. 基本语法

2. 数据类型选择

数值类型

字符串类型

日期时间类型

3. 约束设置

主键约束

外键约束

唯一约束

非空约束

4. 表创建最佳实践

表修改

1. 修改表名

2. 添加列

3. 修改列

4. 删除列

5. 修改约束

6. 修改存储引擎

7. 修改字符集

表删除

1. 基本语法

2. 临时表删除

性能考虑

1. 表创建性能

2. 表修改性能

3. 表删除性能

最佳实践

1. 命名规范

2. 设计原则

3. 安全操作

常见问题与解决方案

1. 表创建失败

2. 表修改卡住

3. 无法删除表

4. 表空间不足

常见问题（FAQ）

Q1: CREATE TABLE和CREATE TABLE IF NOT EXISTS有什么区别？

Q2: 如何查看表的创建语句？

Q3: 什么是在线DDL？

Q4: TRUNCATE TABLE和DELETE FROM有什么区别？

Q5: 如何修改表的自增列起始值？

Q6: 如何复制表结构？

Q7: 如何查看表的结构？

Q8: 如何重命名表中的列？

Q9: 如何删除表中的所有数据但保留表结构？

Q10: 如何查看表的存储引擎？