MySQL数据类型与表操作全指南( 从基础到高级实践)
本文详解MySQL数据类型分类（数值、日期/时间、字符串）及表操作（创建、修改、维护）,涵盖优化技巧如数据类型选择、备份、分区,强调规范设计与实际应用结合,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧
目录
- MySQL数据类型详解
- 数值类型
- 日期时间类型
- 字符串类型
- 表操作全解析
- 创建表
- 修改表结构
- 添加列
- 修改列
- 删除列
- 约束管理
- 添加主键
- 添加外键
- 添加唯一约束
- 表维护操作
- 重命名表
- 截断表
- 删除表
- 表优化技巧
- 最佳实践与注意事项
- 实战案例：电商系统表设计
- 常见问题解决方案
- 进阶技巧
MySQL数据类型详解
MySQL支持多种数据类型，主要分为三类：数值类型、日期/时间类型和字符串类型。
数值类型
数值类型用于存储数字，包括整数和浮点数：
| 类型 | 大小(字节) | 范围（有符号） | 说明 |
|---|---|---|---|
| TINYINT | 1 | -128 到 127 | 小整数值 |
| INT | 4 | -2147483648 到 2147483647 | 标准整数 |
| BIGINT | 8 | ±9.22e18 | 大整数 |
| FLOAT | 4 | -3.402823466E+38 到 3.402823466E+38 | 单精度浮点数 |
| DOUBLE | 8 | ±1.7976931348623157E+308 | 双精度浮点数 |
| DECIMAL(M,D) | 变长 | 取决于M和D | 精确小数，M总位数，D小数位 |
示例：
CREATE TABLE products (
id INT PRIMARY KEY,
price DECIMAL(10,2), -- 总10位，含2位小数
quantity SMALLINT UNSIGNED -- 无符号小整数
);
日期时间类型
日期和时间类型用于存储时间信息：
| 类型 | 格式 | 范围 | 说明 |
|---|---|---|---|
| DATE | YYYY-MM-DD | 1000-01-01 到 9999-12-31 | 日期值 |
| TIME | HH:MM:SS | -838:59:59 到 838:59:59 | 时间值 |
| DATETIME | YYYY-MM-DD HH:MM:SS | 1000-01-01 00:00:00 到 9999-12-31 23:59:59 | 混合日期时间 |
| TIMESTAMP | YYYY-MM-DD HH:MM:SS | 1970-01-01 00:00:01 到 2038-01-19 03:14:07 | 时间戳，自动更新 |
| YEAR | YYYY | 1901 到 2155 | 年份值 |
字符串类型
字符串类型用于存储文本和二进制数据：
| 类型 | 最大长度 | 说明 |
|---|---|---|
| CHAR(n) | 255字符 | 定长字符串，空格填充 |
| VARCHAR(n) | 65,535字符 | 变长字符串，节省空间 |
| TEXT | 65,535字符 | 长文本数据 |
| BLOB | 65,535字节 | 二进制大对象 |
| ENUM | 65,535项 | 枚举类型，值从预定义列表中选择 |
| SET | 64个成员 | 集合类型，允许选择多个预定义值 |
示例：
CREATE TABLE users (
username VARCHAR(50) NOT NULL,
gender ENUM('Male','Female','Other'),
interests SET('Music','Sports','Reading')
);
表操作全解析
创建表
基本语法：
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
PRIMARY KEY (one_or_more_columns)
);
完整示例：
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT,
first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
birth_date DATE,
hire_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
salary DECIMAL(10,2) CHECK (salary > 0),
PRIMARY KEY (emp_id),
UNIQUE (first_name, last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
修改表结构
添加列
ALTER TABLE employees ADD COLUMN email VARCHAR(100) AFTER last_name;
修改列
-- 修改数据类型 ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12,2); -- 重命名列 ALTER TABLE employees CHANGE COLUMN birth_date date_of_birth DATE;
删除列
ALTER TABLE employees DROP COLUMN hire_date;
约束管理
添加主键
ALTER TABLE orders ADD PRIMARY KEY (order_id);
添加外键
ALTER TABLE order_items ADD CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE;
添加唯一约束
ALTER TABLE users ADD UNIQUE (email);
表维护操作
重命名表
RENAME TABLE old_name TO new_name; -- 或 ALTER TABLE old_name RENAME TO new_name;
截断表
TRUNCATE TABLE log_entries; -- 快速删除所有数据
删除表
DROP TABLE IF EXISTS temp_data;
表优化技巧
- 选择合适的数据类型
- 用INT代替VARCHAR存储数字
- 用DATE代替DATETIME如果不需要时间部分
- 用ENUM代替VARCHAR存储固定选项
- 规范命名约定
CREATE TABLE customer_orders ( -- 使用蛇形命名法
order_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id INT UNSIGNED NOT NULL,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (order_id)
);
使用注释增强可读性
CREATE TABLE payments (
payment_id INT COMMENT '主键ID',
amount DECIMAL(10,2) COMMENT '支付金额',
payment_method ENUM('Credit','Paypal','Bank')
COMMENT '支付方式'
) COMMENT='支付信息表';
分区大表优化查询
CREATE TABLE sensor_data (
id INT AUTO_INCREMENT,
sensor_id INT,
reading_time TIMESTAMP,
value FLOAT,
PRIMARY KEY (id, reading_time)
) PARTITION BY RANGE (YEAR(reading_time)) (
PARTITION p2020 VALUES LESS THAN (2021),
PARTITION p2021 VALUES LESS THAN (2022),
PARTITION p2022 VALUES LESS THAN (2023)
);
最佳实践与注意事项
备份优先原则 执行结构变更前务必备份：
mysqldump -u root -p database_name > backup.sql
- 外键约束影响
- ON DELETE CASCADE：删除主表记录时自动删除从表相关记录
- ON DELETE SET NULL：将外键设为NULL
- 谨慎使用CASCADE避免误删连锁反应
- 字符集选择
- 推荐utf8mb4支持所有Unicode字符（包括emoji）
- 校对规则：utf8mb4_unicode_ci（大小写不敏感）
- 存储引擎选择
SHOW ENGINES; -- 查看支持的引擎
- InnoDB：支持事务、行级锁（默认）
- MyISAM：全文索引，但不支持事务
- Memory：数据存储在内存中
- 性能优化
- 避免过度使用ENUM（修改值需重建表）
- TEXT/BLOB列单独存到副表
- 定期分析表优化存储：
ANALYZE TABLE orders; OPTIMIZE TABLE log_data;
实战案例：电商系统表设计
-- 商品表
CREATE TABLE products (
product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
price DECIMAL(10,2) UNSIGNED NOT NULL,
stock INT UNSIGNED DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
INDEX idx_name (name)
) ENGINE=InnoDB;
-- 订单表
CREATE TABLE orders (
order_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id INT UNSIGNED NOT NULL,
total_amount DECIMAL(12,2) NOT NULL,
status ENUM('Pending','Paid','Shipped','Completed') DEFAULT 'Pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE RESTRICT
) PARTITION BY HASH(order_id) PARTITIONS 4;
-- 订单明细表
CREATE TABLE order_details (
detail_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
order_id BIGINT UNSIGNED NOT NULL,
product_id INT UNSIGNED NOT NULL,
quantity SMALLINT UNSIGNED NOT NULL,
price DECIMAL(10,2) NOT NULL,
CONSTRAINT fk_order
FOREIGN KEY (order_id) REFERENCES orders(order_id)
ON DELETE CASCADE,
CONSTRAINT fk_product
FOREIGN KEY (product_id) REFERENCES products(product_id)
ON DELETE RESTRICT
);
常见问题解决方案
问题1：如何修改AUTO_INCREMENT起始值？
ALTER TABLE products AUTO_INCREMENT = 1000;
问题2：误删表如何恢复？
- 使用备份文件恢复
- 若无备份，尝试从binlog恢复：
mysqlbinlog --start-datetime="2023-01-01 00:00:00" binlog.000001 | mysql -u root -p
问题3：大表添加列卡顿 使用pt-online-schema-change工具在线修改：
pt-online-schema-change --alter "ADD COLUMN new_col INT" D=database,t=table --execute
问题4：存储引擎转换
ALTER TABLE orders ENGINE = InnoDB; -- 转换为InnoDB
进阶技巧
生成列（Generated Columns）
CREATE TABLE invoices (
subtotal DECIMAL(10,2),
tax_rate DECIMAL(5,4),
tax_amount DECIMAL(10,2) AS (subtotal * tax_rate) STORED,
total DECIMAL(10,2) AS (subtotal + tax_amount) STORED
);
JSON数据类型操作
CREATE TABLE product_specs (
product_id INT PRIMARY KEY,
specs JSON
);
INSERT INTO product_specs VALUES (1, '{"color": "red", "weight": 500}');
SELECT specs->>"$.color" FROM product_specs;
表空间管理
-- 创建独立表空间
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB;
CREATE TABLE large_table (
id INT PRIMARY KEY
) TABLESPACE ts1;
不可见列（MySQL 8.0+）
CREATE TABLE accounts (
id INT AUTO_INCREMENT PRIMARY KEY,
balance DECIMAL(10,2) INVISIBLE
);
INSERT INTO accounts (id) VALUES (1); -- 必须显式指定可见列
SELECT * FROM accounts; -- 不显示balance列
SELECT id, balance FROM accounts; -- 显式查询
通过深入理解MySQL数据类型和表操作，可以设计出高效可靠的数据库结构。实际应用中需结合业务场景选择合适的数据类型，遵循数据库设计规范，并定期进行表结构优化维护。
到此这篇关于MySQL数据类型从基础到高级实践与表操作全指南的文章就介绍到这了,更多相关mysql数据类型内容请搜索骃骐网以前的文章或继续浏览下面的相关文章希望大家以后多多支持骃骐网！
您可能感兴趣的文章:
- 关于MYSQL 你需要知道的数据类型和操作数据表
- 详解MySQL数据类型DECIMAL(N,M)中N和M分别表示的含义
- MySQL与Oracle数据类型对应关系(表格形式)
- Java数据类型与MySql数据类型对照表
- MySQL所支持的数据类型与表字段约束类型的学习教程
- MySQL优化之表结构优化的5大建议（数据类型选择讲的很好）
- Mysql中返回一个数据库的所有表名,列名数据类型备注