开发者

MySQL数据类型与表操作全指南( 从基础到高级实践)

开发者 https://www.devze.com 2025-08-09 09:06 出处：网络 作者： 搬砖的码农
目录mysql数据类型详解数值类型日期时间类型字符串类型表操作全解析创建表修改表结构添加列修改列删除列约束管理添加主键添加外键添加唯一约束表维护操作重命名表截断表删除表表优化技巧最佳实践与注意事项实战案例：
相关专题：mysql表操作
目录
  • mysql数据类型详解
    • 数值类型
    • 日期时间类型
    • 字符串类型
  • 表操作全解析
    • 创建表
    • 修改表结构
    • 添加列
    • 修改列
    • 删除列
    • 约束管理
    • 添加主键
    • 添加外键
    • 添加唯一约束
    • 表维护操作
    • 重命名表
    • 截断表
    • 删除表
    • 表优化技巧
  • 最佳实践与注意事项
    • 实战案例：电商系统表设计
      • 常见问题解决方案
        • 进阶技巧

          MySQL数据类型详解

          MySQL支持多种数据类型，主要分为三类：数值类型、日期/时间类型和字符串类型。

          数值类型

          数值类型用于存储数字，包括整数和浮点数：

          类型大小(字节)范围（有符号）说明
          TINYINT1-128 到 127小整数值
          INT4-2147483648 到 2147483647标准整数
          BIGINT8±9.22e18大整数
          FLOAT4-3.402823466E+38 到 3.402823466E+38单精度浮点数
          DOUBLE8±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 -- 无符号小整数
);

          日期时间类型

          日期和时间类型用于存储时间信息：

          类型格式范围说明
          DATEYYYY-MM-DD1android000-01-01 到 9999-12-31日期值
          TIMEHH:MM:SS-838:59:59 到 838:59:59时间值
          DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 到 9999-12-31 23:59:59混合日期时间
          TIMESTAMPYYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 到 2038-01-19 03:14:07时间戳，自动更新
          YEARYYYY1901 到 2155年份值

          字符串类型

          字符串类型用于存储文本和二进制数据：

          类型最大长度说明
          CHAR(n)255字符定长字符串，空格填充
          VARCHAR(n)65,535字符变长字符串，节省空间
          TEXT65,535字符长文本数据
          BLOB65,535字节二进制大对象
          ENUM65,535项枚举类型，值从预定义列表中选择
          SET64个成员集合类型，允许选择多个预定义值

          示例：

          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 emplopythonyees
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字http://www.devze.com符（包括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, '{"colohttp://www.devze.comr": "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数据类型内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！

          0

          上一篇:

          :下一篇

          更多 数据库 相关资讯：

          精彩评论

          暂无评论...
          登录 注册
          Qedev.com
          验证码 换一张
          取 消

          关注公众号

          热门标签

          图文推荐

          MySQL双主高可用详解

          MySQL双主高可用详解
          基于Redisson实现分布式系统下的接口限流

          基于Redisson实现分布式系统下的接口限流
          SQL join中on和where的区别解析

          SQL join中on和where的区别解析
          MySQL8.4组复制的实现步骤

          MySQL8.4组复制的实现步骤
          MySQL之复合查询解读

          MySQL之复合查询解读
          MySQL基本查询方式(表的增删查改)

          MySQL基本查询方式(表的增删查改)
          MySQL在Centos7环境安装MySQL教程

          MySQL在Centos7环境安装MySQL教程
          Mysql中的图形化界面方式

          Mysql中的图形化界面方式

          开发者开发者网给大家分享系统运维,大数据运维,云计算,编程开发技巧,路由交换,运维和开发相关的资讯及技术文章，同时StackOverflow中文社区，知识经验交流分享。

          法律声明：本站内容均为网友上传，网站举办方负责审核和监督，如存在版权或非法内容，欢迎举报，我们将尽快予以删除。邮箱：devze@qq.com

          Copyright © 2018-2020 开发者. All rights reserved. Powered by 开发者ICP备案号: 京ICP备10032868号-9