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;

表优化技巧

  1. 选择合适的数据类型

    • 用INT代替VARCHAR存储数字
    • 用DATE代替DATETIME如果不需要时间部分
    • 用ENUM代替VARCHAR存储固定选项

  2. 规范命名约定

    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)
);

  3. 使用注释增强可读性

    CREATE TABLE payments (
    payment_id INT COMMENT '主键ID',
    amount DECIMAL(10,2) COMMENT '支付金额',
    payment_method ENUM('Credit','Paypal','Bank') 
        COMMENT '支付方式'
) COMMENT='支付信息表';

  4. 分区大表优化查询

    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)
);

最佳实践与注意事项

  1. 备份优先原则 执行结构变更前务必备份：

    mysqldump -u root -p database_name > backup.sql

  2. 外键约束影响

    • ON DELETE CASCADE：删除主表记录时自动删除从表相关记录
    • ON DELETE SET NULL：将外键设为NULL
    • 谨慎使用CASCADE避免误删连锁反应

  3. 字符集选择

    • 推荐utf8mb4支持所有Unicode字符（包括emoji）
    • 校对规则：utf8mb4_unicode_ci（大小写不敏感）

  4. 存储引擎选择

    SHOW ENGINES; -- 查看支持的引擎
    • InnoDB：支持事务、行级锁（默认）
    • MyISAM：全文索引，但不支持事务
    • Memory：数据存储在内存中

  5. 性能优化

    • 避免过度使用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：误删表如何恢复？

  1. 使用备份文件恢复
  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

进阶技巧

  1. 生成列（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
);

  2. 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;

  3. 表空间管理

    -- 创建独立表空间
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=InnoDB;

CREATE TABLE large_table (
    id INT PRIMARY KEY
) TABLESPACE ts1;

  4. 不可见列（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
相关文章
热门推荐
评论插件初始化中...
Loading...
标签云
开发效率 工程实践 ThreadLocal 数据转换 响应封装 WebFlux 缓存淘汰机制 进程管理 运维 集成和配置 软件工程 XA事务 数据库调试 Java框架 代码设计 实时显示 数据库运维 数据库开发 线程同步 预处理 缓存 流式计算 数据库编程 惰性求值 Bean管理 html 容器技术 异步通信 进程通信 CentOS 7 文件合并 分库分表 Java性能优化 Thread类 SQL约束
本文目录