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数据类型和表操作，可以设计出高效可靠的数据库结构。实际应用中需结合业务场景选择合适的数据类型，遵循数据库设计规范，并定期进行表结构优化维护。