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

2025-08-08 21:50
文章标签 基础 mysql 实践 操作 数据类型 高级 指南 database 与表

本文主要是介绍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.chinasem.cn符（包括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.chinasem.cnr": "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数据类型内容请搜索China编程(www.chinasem.cn)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程China编程(www.chinasem.cn)！

这篇关于MySQL数据类型与表操作全指南( 从基础到高级实践)的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1155642。

相关文章

Git进行版本控制的实战指南

Git进行版本控制的实战指南

《Git进行版本控制的实战指南》Git是一种分布式版本控制系统,广泛应用于软件开发中,它可以记录和管理项目的历史修改,并支持多人协作开发,通过Git,开发者可以轻松地跟踪代码变更、合并分支、回退版本等... 目录一、Git核心概念解析二、环境搭建与配置1. 安装Git（Windows示例）2. 基础配置（必
阅读更多...
MySQL8.0临时表空间的使用及解读

MySQL8.0临时表空间的使用及解读

《MySQL8.0临时表空间的使用及解读》MySQL8.0+引入会话级（temp_N.ibt）和全局（ibtmp1）InnoDB临时表空间,用于存储临时数据及事务日志,自动创建与回收,重启释放,管理高... 目录一、核心概念：为什么需要“临时表空间”？二、InnoDB 临时表空间的两种类型1. 会话级临时表
阅读更多...
MySQL之复合查询使用及说明

MySQL之复合查询使用及说明

《MySQL之复合查询使用及说明》文章讲解了SQL复合查询中emp、dept、salgrade三张表的使用,涵盖多表连接、自连接、子查询（单行/多行/多列）及合并查询（UNION/UNIONALL）等... 目录复合查询基本查询回顾多表查询笛卡尔积自连接子查询单行子查询多行子查询多列子查询在from子句中使
阅读更多...
Java中最全最基础的IO流概述和简介案例分析

Java中最全最基础的IO流概述和简介案例分析

《Java中最全最基础的IO流概述和简介案例分析》JavaIO流用于程序与外部设备的数据交互,分为字节流（InputStream/OutputStream）和字符流（Reader/Writer）,处理... 目录IO流简介IO是什么应用场景IO流的分类流的超类类型字节文件流应用简介核心API文件输出流应用文
阅读更多...
MySQL使用EXISTS检查记录是否存在的详细过程

MySQL使用EXISTS检查记录是否存在的详细过程

《MySQL使用EXISTS检查记录是否存在的详细过程》EXISTS是SQL中用于检查子查询是否返回至少一条记录的运算符,它通常用于测试是否存在满足特定条件的记录,从而在主查询中进行相应操作,本文给大... 目录基本语法示例数据库和表结构1. 使用 EXISTS 在 SELECT 语句中2. 使用 EXIS
阅读更多...
在.NET项目中嵌入Python代码的实践指南

在.NET项目中嵌入Python代码的实践指南

《在.NET项目中嵌入Python代码的实践指南》在现代开发中,.NET与Python的协作需求日益增长,从机器学习模型集成到科学计算,从脚本自动化到数据分析,然而,传统的解决方案（如HTTPAPI或... 目录一、CSnakes vs python.NET：为何选择 CSnakes？二、环境准备：从 Py
阅读更多...
Docker多阶段镜像构建与缓存利用性能优化实践指南

Docker多阶段镜像构建与缓存利用性能优化实践指南

《Docker多阶段镜像构建与缓存利用性能优化实践指南》这篇文章将从原理层面深入解析Docker多阶段构建与缓存机制,结合实际项目示例,说明如何有效利用构建缓存,组织镜像层次,最大化提升构建速度并减少... 目录一、技术背景与应用场景二、核心原理深入分析三、关键 dockerfile 解读3.1 Docke
阅读更多...
Java Stream流与使用操作指南

Java Stream流与使用操作指南

《JavaStream流与使用操作指南》Stream不是数据结构,而是一种高级的数据处理工具,允许你以声明式的方式处理数据集合,类似于SQL语句操作数据库,本文给大家介绍JavaStream流与使用... 目录一、什么是stream流二、创建stream流1.单列集合创建stream流2.双列集合创建str
阅读更多...
Python正则表达式匹配和替换的操作指南

Python正则表达式匹配和替换的操作指南

《Python正则表达式匹配和替换的操作指南》正则表达式是处理文本的强大工具,Python通过re模块提供了完整的正则表达式功能,本文将通过代码示例详细介绍Python中的正则匹配和替换操作,需要的朋... 目录基础语法导入re模块基本元字符常用匹配方法1. re.match() - 从字符串开头匹配2.
阅读更多...
Spring Security简介、使用与最佳实践

Spring Security简介、使用与最佳实践

《SpringSecurity简介、使用与最佳实践》SpringSecurity是一个能够为基于Spring的企业应用系统提供声明式的安全访问控制解决方案的安全框架,本文给大家介绍SpringSec... 目录一、如何理解 Spring Security？—— 核心思想二、如何在 Java 项目中使用？——
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2