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数据类型和表操作，可以设计出高效可靠的数据库结构。实际应用中需结合业务场景选择合适的数据类型，遵循数据库设计规范，并定期进行表结构优化维护。
正文到此结束
相关文章
热门推荐
评论插件初始化中...