当前位置： 代码网 > it编程>数据库>Mysql > MySQL数据类型与表操作全指南( 从基础到高级实践)

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

2025年08月08日 • Mysql •我要评论
mysql数据类型详解mysql支持多种数据类型，主要分为三类：数值类型、日期/时间类型和字符串类型。数值类型数值类型用于存储数字，包括整数和浮点数：类型大小(字节)范围（有符号）说明tinyint1

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-dd1000-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 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数据类型内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！

赞 (0)

相关文章:

  • 深入MySQL调优原则

    深入MySQL调优原则

    mysql的调优是为了确保数据库在高负载和大数据量情况下能够高效稳定运行。调优原则主要包括硬件调优、系统配置调优、mysql配置调优、模式设计调优、查询优化等。... [阅读全文]
  • 浅谈MySQL的容量规划

    浅谈MySQL的容量规划

    进行mysql的容量规划是确保数据库能够在当前和未来的负载下顺利运行的重要步骤。容量规划包括评估当前资源使用情况、预测未来增长、调整配置和硬件资源等。以下是进行... [阅读全文]
  • MySQL的触发器全解析(创建、查看触发器)

    MySQL的触发器全解析(创建、查看触发器)

    触发器的概念：mysql触发器（‌trigger‌）是一种与数据库表关联的特殊存储程序，当指定事件（如insert、update、delet... [阅读全文]
  • MYSQL中information_schema的使用

    MYSQL中information_schema的使用

    关键要点information_schema 是 mysql 中的一个虚拟数据库，提供关于数据库结构和权限的元数据信息。研究表明，它包含只读表（视图），用户可查... [阅读全文]
  • MySQL中处理数据的并发一致性的实现示例

    MySQL中处理数据的并发一致性的实现示例

    在mysql中处理数据的并发一致性是确保多个用户或应用程序同时访问和修改数据库时，不会导致数据冲突、数据丢失或数据不一致。mysql通过事务（transacti... [阅读全文]
  • MySQL容灾备份的实现方案

    MySQL容灾备份的实现方案

    进行mysql的容灾备份是确保数据安全和业务连续性的关键步骤。容灾备份可以分为本地备份和远程备份，主要包括逻辑备份和物理备份两种方式。以下是详细说明和示例代码。... [阅读全文]

版权声明：本文内容由互联网用户贡献，该文观点仅代表作者本人。本站仅提供信息存储服务，不拥有所有权，不承担相关法律责任。 如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 2386932994@qq.com 举报，一经查实将立刻删除。

发表评论

验证码：
最近更新的文章
推荐阅读
大家感兴趣的文章
Copyright © 2017-2025  代码网 保留所有权利. 粤ICP备2024248653号
站长QQ:2386932994 | 联系邮箱：2386932994@qq.com