【MySQL】数据库的数据类型

MySQL数据库的数据类型详解
最新推荐文章于 2025-09-22 14:26:22 发布
原创 最新推荐文章于 2025-09-22 14:26:22 发布 · 786 阅读 · 25 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#数据库 #mysql #oracle

在这里插入图片描述

个人主页：Guiat
归属专栏：MySQL

在这里插入图片描述

文章目录

正文

1. 数据类型概述

MySQL提供了多种数据类型，用于存储不同种类的数据。选择正确的数据类型对于数据库性能、存储空间优化和数据完整性至关重要。

1.1 数据类型的重要性

  • 存储空间优化
  • 查询性能提升
  • 数据完整性保证
  • 内存使用效率提高

1.2 数据类型选择原则

  • 选择能够满足需求的最小数据类型
  • 考虑未来数据增长的可能性
  • 考虑查询操作的效率
  • 避免使用过大的字符串类型
  • 使用专门的类型存储特殊数据（如日期、时间）

2. 数值类型

2.1 整数类型

MySQL提供了多种整数类型，不同类型支持不同范围的数值：

类型存储空间有符号范围无符号范围
TINYINT1字节-128 ~ 1270 ~ 255
SMALLINT2字节-32,768 ~ 32,7670 ~ 65,535
MEDIUMINT3字节-8,388,608 ~ 8,388,6070 ~ 16,777,215
INT4字节-2^31 ~ 2^31-10 ~ 2^32-1
BIGINT8字节-2^63 ~ 2^63-10 ~ 2^64-1
-- 整数类型示例
CREATE TABLE number_examples (
    tiny_col TINYINT,
    tiny_col_unsigned TINYINT UNSIGNED,
    small_col SMALLINT,
    int_col INT,
    int_col_zerofill INT ZEROFILL,  -- 显示时用0填充
    big_col BIGINT UNSIGNED
);

2.2 浮点数和定点数

浮点数和定点数用于存储小数：

类型存储空间精度范围特点
FLOAT4字节单精度近似值，存储空间小
DOUBLE8字节双精度近似值，精度更高
DECIMAL(M,D)变长精确存储精确值，适合金融计算

其中，DECIMAL(M,D)中M表示总位数，D表示小数位数。

-- 小数类型示例
CREATE TABLE decimal_examples (
    float_col FLOAT(10,2),        -- 总共10位，其中2位小数
    double_col DOUBLE(16,4),      -- 总共16位，其中4位小数
    decimal_col DECIMAL(20,6),    -- 精确存储，20位数字，6位小数
    price DECIMAL(10,2)           -- 适合存储金额
);

2.3 BIT类型

BIT类型用于存储位值：

-- BIT类型示例
CREATE TABLE bit_examples (
    bit_col1 BIT(1),    -- 可以存储1位，值为0或1
    bit_col8 BIT(8)     -- 可以存储8位，值范围0-255
);

INSERT INTO bit_examples VALUES (b'1', b'10101010');

3. 字符串类型

3.1 CHAR和VARCHAR

类型最大长度存储特点适用场景
CHAR(n)255字符固定长度长度基本固定的数据
VARCHAR(n)65,535字节可变长度长度变化的数据
-- CHAR和VARCHAR示例
CREATE TABLE string_examples (
    char_col CHAR(10),         -- 固定存储10个字符，不足补空格
    varchar_col VARCHAR(255),  -- 可变长度，最多255个字符
    code CHAR(6),              -- 适合存储固定长度的代码
    name VARCHAR(100)          -- 适合存储名称等可变长度信息
);

3.2 TEXT类型

TEXT类型用于存储大量的文本数据：

类型最大长度存储空间
TINYTEXT255字节1字节长度前缀
TEXT65,535字节2字节长度前缀
MEDIUMTEXT16,777,215字节3字节长度前缀
LONGTEXT4GB4字节长度前缀
-- TEXT类型示例
CREATE TABLE text_examples (
    tiny_text_col TINYTEXT,      -- 适合存储小型文本
    text_col TEXT,               -- 适合一般文章内容
    medium_text_col MEDIUMTEXT,  -- 适合较长文章
    long_text_col LONGTEXT       -- 适合非常长的内容
);

3.3 ENUM和SET

ENUM和SET类型允许从预定义的值列表中选择：

-- ENUM和SET示例
CREATE TABLE enum_set_examples (
    enum_col ENUM('small', 'medium', 'large'),   -- 只能选择其中一个值
    set_col SET('red', 'green', 'blue', 'yellow')  -- 可以选择多个值
);

INSERT INTO enum_set_examples VALUES ('medium', 'red,blue');

3.4 BINARY和VARBINARY

BINARY和VARBINARY类型用于存储二进制数据：

-- 二进制数据类型示例
CREATE TABLE binary_examples (
    binary_col BINARY(10),        -- 固定长度二进制数据
    varbinary_col VARBINARY(100)  -- 可变长度二进制数据
);

4. 日期和时间类型

4.1 日期类型概览

类型存储空间格式范围
DATE3字节YYYY-MM-DD1000-01-01 到 9999-12-31
TIME3-6字节HH:MM:SS-838:59:59 到 838:59:59
DATETIME5-8字节YYYY-MM-DD HH:MM:SS1000-01-01 00:00:00 到 9999-12-31 23:59:59
TIMESTAMP4字节YYYY-MM-DD HH:MM:SS1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
YEAR1字节YYYY1901 到 2155

4.2 日期时间类型特点

-- 日期时间类型示例
CREATE TABLE datetime_examples (
    date_col DATE,                     -- 仅存储日期
    time_col TIME,                     -- 仅存储时间
    datetime_col DATETIME,             -- 存储日期和时间
    timestamp_col TIMESTAMP,           -- 存储时间戳，自动更新
    year_col YEAR,                     -- 仅存储年份
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,              -- 创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新时间
);

4.3 TIMESTAMP vs DATETIME

TIMESTAMP和DATETIME的主要区别：
日期时间类型
TIMESTAMP
DATETIME
受时区影响
范围有限
自动更新
不受时区影响
范围更广
需手动设置更新

5. JSON类型

5.1 JSON类型介绍

MySQL 5.7.8及更高版本支持原生JSON数据类型，可以高效地存储和访问JSON格式数据。

-- JSON类型示例
CREATE TABLE json_examples (
    id INT PRIMARY KEY,
    json_data JSON
);

INSERT INTO json_examples VALUES (
    1, 
    '{"name": "John", "age": 30, "address": {"city": "New York", "zip": "10001"}, "skills": ["PHP", "MySQL", "JavaScript"]}'
);

5.2 JSON操作函数

MySQL提供了一系列函数来操作JSON数据：

-- 提取JSON数据
SELECT 
    json_data->'$.name' AS name,
    json_data->'$.age' AS age,
    json_data->'$.address.city' AS city,
    json_data->'$.skills[0]' AS first_skill
FROM json_examples;

-- 修改JSON数据
UPDATE json_examples 
SET json_data = JSON_SET(json_data, '$.age', 31, '$.skills[3]', 'Python')
WHERE id = 1;

6. 空间数据类型

6.1 几何数据类型

MySQL支持OpenGIS几何类型，用于存储地理空间数据：

类型描述
GEOMETRY任何几何体
POINT点
LINESTRING线
POLYGON多边形
MULTIPOINT点集合
MULTILINESTRING线集合
MULTIPOLYGON多边形集合
GEOMETRYCOLLECTION几何体集合
-- 空间数据类型示例
CREATE TABLE spatial_examples (
    id INT PRIMARY KEY,
    location POINT,
    area POLYGON
);

-- 插入点数据
INSERT INTO spatial_examples(id, location) VALUES (1, ST_GeomFromText('POINT(40.7128 -74.0060)'));

-- 插入多边形数据
INSERT INTO spatial_examples(id, area) VALUES (
    2, 
    ST_GeomFromText('POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))')
);

6.2 空间索引

MySQL支持对空间数据创建索引以加速查询：

-- 创建空间索引
CREATE SPATIAL INDEX idx_location ON spatial_examples(location);

7. 特殊数据类型

7.1 BLOB类型

BLOB (Binary Large Object) 类型用于存储二进制大对象数据，如图片、视频等：

类型最大长度存储空间
TINYBLOB255字节1字节长度前缀
BLOB65,535字节2字节长度前缀
MEDIUMBLOB16,777,215字节3字节长度前缀
LONGBLOB4GB4字节长度前缀
-- BLOB类型示例
CREATE TABLE blob_examples (
    id INT PRIMARY KEY,
    tiny_blob_col TINYBLOB,
    blob_col BLOB,
    medium_blob_col MEDIUMBLOB,
    long_blob_col LONGBLOB,
    image MEDIUMBLOB,      -- 适合存储图片
    document LONGBLOB      -- 适合存储PDF文档等
);

7.2 INET6类型

MySQL 8.0引入了专门用于存储IPv6地址的函数：

-- IPv6处理函数
SELECT INET6_ATON('2001:db8::1') AS ipv6_binary;
SELECT INET6_NTOA(INET6_ATON('2001:db8::1')) AS ipv6_text;

8. 数据类型转换

8.1 隐式转换

MySQL会在必要时进行隐式数据类型转换：

-- 隐式转换示例
CREATE TABLE conversion_test (
    id INT PRIMARY KEY,
    str_val VARCHAR(20)
);

INSERT INTO conversion_test VALUES (1, '123');

-- 字符串会被隐式转换为数字
SELECT * FROM conversion_test WHERE str_val = 123;

8.2 显式转换

可以使用CAST()或CONVERT()函数进行显式类型转换：

-- 显式转换示例
SELECT 
    CAST('2023-10-15' AS DATE) AS date_value,
    CONVERT('123', SIGNED INTEGER) AS int_value,
    CAST(123.45 AS CHAR) AS string_value;

9. 数据类型性能考量

9.1 存储空间比较
存储空间
固定长度类型
可变长度类型
预分配空间
存取效率高
可能浪费空间
按需分配空间
节省存储空间
性能略低

9.2 性能优化建议

  • 整数列优先选择 INT
  • 精确计算（如金额）使用 DECIMAL
  • 固定长度字符使用 CHAR，变长字符使用 VARCHAR
  • 避免使用 TEXT/BLOB 作为经常查询的列
  • TIMESTAMP 比 DATETIME 更节省空间
  • 使用 ENUM 代替字符串可大幅节省空间
  • 考虑每列是否需要允许 NULL 值

10. 实践应用

10.1 电商数据库案例

CREATE TABLE products (
    product_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  -- 无符号整数，自增
    name VARCHAR(255) NOT NULL,                          -- 产品名称
    description TEXT,                                    -- 产品描述，可能很长
    price DECIMAL(10, 2) NOT NULL,                       -- 精确存储价格
    stock SMALLINT UNSIGNED DEFAULT 0,                   -- 库存数量，不会太大
    category ENUM('electronics', 'clothing', 'food', 'books', 'other'), -- 固定类别选择
    tags SET('new', 'sale', 'popular', 'recommended'),   -- 多个标签组合
    attributes JSON,                                     -- 灵活存储产品属性
    image MEDIUMBLOB,                                    -- 产品图片
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,      -- 创建时间
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -- 更新时间
);

CREATE TABLE customers (
    customer_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password CHAR(60) NOT NULL,                          -- 存储哈希密码，固定长度
    phone VARCHAR(20),
    birth_date DATE,                                     -- 仅需存储日期
    address JSON,                                        -- 灵活存储地址信息
    last_login DATETIME,                                 -- 需要精确的登录时间
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    order_id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -- 订单ID可能会很大
    customer_id INT UNSIGNED,
    order_date DATETIME NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled'),
    total_amount DECIMAL(12, 2) NOT NULL,                -- 支持更大金额
    shipping_address TEXT NOT NULL,
    payment_method ENUM('credit_card', 'debit_card', 'paypal', 'bank_transfer'),
    tracking_number VARCHAR(50),
    notes TEXT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (
    order_id BIGINT UNSIGNED,
    product_id INT UNSIGNED,
    quantity SMALLINT UNSIGNED NOT NULL,
    unit_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

10.2 数据类型选择分析

数据选择的类型原因
用户IDINT UNSIGNED节省空间，不需要负值
订单IDBIGINT UNSIGNED预期订单量巨大
产品名称VARCHAR(255)长度可变，但有上限
产品描述TEXT可能非常长，不常用于查询条件
价格DECIMAL(10,2)需要精确计算
类别ENUM固定几个选项，节省空间
创建时间TIMESTAMP自动设置，占用空间小
地址信息JSON灵活存储结构化数据
产品图片MEDIUMBLOB二进制数据，中等大小

10.3 存储空间估算

每1000万产品记录估算:
- product_id (INT): 4字节 × 1000万 = 40MB
- name (VARCHAR): 平均50字节 × 1000万 = 500MB
- price (DECIMAL): 5字节 × 1000万 = 50MB
- stock (SMALLINT): 2字节 × 1000万 = 20MB
- category (ENUM): 1字节 × 1000万 = 10MB
- timestamps: 8字节 × 1000万 = 80MB
（不含TEXT、BLOB和JSON字段）

总计: 约700MB（基本字段）

选择合适的数据类型不仅影响性能，还能显著影响存储空间需求和查询效率。在设计数据库时，应根据实际需求和数据特性，合理选择最适合的数据类型。

结语
感谢您的阅读！期待您的一键三连！欢迎指正！

在这里插入图片描述

MySql数据库中数据类型
m0_74830524的博客
10-23 2201
本篇将介绍在 MySql 中的所有数据类型，其中主要分为四类：数值类型、文本和二进制类型、时间日期、String 类型。
【03】MySQL数据库的数据类型都有哪些？
qq_35320456的博客
11-25 1499
在 MySQL 中，选择合适的数据类型不仅能帮助你高效地存储数据，还能提高查询的效率。不同的数据类型具有不同的存储需求和性能特点，理解它们的特点并合理应用，是数据库设计的基础。通过本文的示例，你可以看到如何在实际项目中定义并使用这些数据类型。
Mysql数据库类型
weixin_53532638的博客
08-30 220
MySQL常见的数据类型 数值类型： 整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT 重点记住：int 浮点数类型 FLOAT DOUBLE (带小数点的) float(6，2)的含义数据是float型，数据长度是6，小数点后保留2位 定点数类型 DEC 了解 decimal型的默认整数位为10，小数位为0，即默认为整数。 会自动四舍五入 字符串类型： CHAR系列 （重点） CHAR VARCHAR (其长度指示要存储的最大字符
11种数据库类型详解：数据库分关系数据库、非关系数据库、时序数据库、向量数据库等
最新发布
橙-极纪元-JJYCheng
09-22 1418
数据库类型详解：数据库分关系数据库、非关系数据库、时序数据库、向量数据库等
MySQL数据库数据类型
qq_57570052的博客
08-03 1098
整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200.但是，如果你不确定字符串的长度，或者需要存储非常长的字符串，使用VARCHAR可以提高存储效率。1._BLOB和_text存储方式不同，_TEXT以文本方式存储，英文存储区分大小写，而_Blob是以二进制方式存储，不分大小写。，这个字段里的时间数据会随其他字段修改的时候自动刷新，所以这个数据类型的字段可以存放这条记录最后被修改的时间。另一方面，VARCHAR是变长字符串类型，它的长度是。
【MySQL】数据库数据类型
qq_62939852的博客
10-21 2054
创建一张表t6,内部包含 一个bigint类型的id 以及 无符号的浮点数类型的salary，并且salary共有4位，其中2位是小数。创建一张表t5，包含 int类型的id 以及 float类型的salary，并且salary共有4位，其中2位是小数。创建一张表 t10 ，内部包含 date类型的t1 ，datetime类型的t2，timestamp类型的 t3。可当插入 (123,5)时 ，由于 online 的类型为1个比特位，所以只能插入0或者1，所以会报错。
精选资源
巧好用的mysql数据库数据字典生成导出绿色工具
04-10
推荐一款好用、小巧的MYSQL数据库数据字典和生成工具， 使用起来非常简单，解压后即可看到执行文件：mysqldoc.exe ，双击运行，输入连接的MYSQL地址，账号密码等，点击获得数据库，如果连接成功，则会列出此账号下...
学习MySQL数据库 MySQL数据类型
08-12
MySQL数据类型
精选资源
MySQL数据库PDF文档
10-20
本文档主要介绍了MySQL数据库的基本概念、数据类型、SQL语言、约束条件、查询语言、聚合函数、子查询和多表查询等知识点。 数据库基础概念 在介绍MySQL数据库之前，首先需要了解数据库的基本概念。数据库是一个...
Mysql常见的数据库类型
记录和分享属于我的“IT”时光。
08-17 2770
Mysql常见的数据库类型，包括整型、浮点型、字符串型以及日期类型。
【MySQL】数据类型
2203_76003626的博客
04-23 1136
数据类型
二、Mysql数据类型
liuqi66的博客
12-30 197
1、数据类型 在MySQL数据库管理系统中，可以通过存储引擎来决定表的类型。同时，MySQL数据库管理系统也 提供了数据类型决定表存储数据的类型。 1.整型 作用：用于存储用户的年龄、游戏的Level、经验值等。 分类：tinyint smallint mediumint int bigint ​ 常用的是int 显示宽度：类型后面小括号内的数字是显示的最小宽度，并不能限制插入数值的大小 比如：bigint(2) 2是显示宽度 取值范围： | MySQL数据类型 | ...
mysql数据库常见数据类型
故事就酒的博客
07-27 724
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE 　PRECISION)。字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
mysql 数据库类型
qq_37111984的博客
03-16 374
https://www.cnblogs.com/y-rong/p/8110596.html
Mysql数据库的类型
欣辰的博客
10-23 248
常用的数据类型： 整数： int bit 小数: decimal 字符串: varchar, char 日期时间: date, time, datetime 枚举类型: enum decimal表示浮点数, 斜体样式decimal(5, 2)表示的是共存6位数, 小数占2位 约束 主键 prmary key 物理上存储的顺序 非空 not null 此字段上不允许填写空值 唯一 unique 此字段不允许有重复 默认default 当不填写此字段的时候会使用默认值代替 外键 f
Linux系统-----------MySQL 数据类型
zzzxxx520369的博客
03-23 1156
因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。2、如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充 空格以达到指定的长度。
【Mysql】 数据类型
sea18323的博客
05-25 1741
简单介绍了mysql中的数据类型。
MySQL到Oracle数据库数据迁移工具介绍
7. 数据类型转换：MySQL和Oracle在数据类型上存在一些差异，例如在数值类型、日期类型和字符类型上。因此，在进行数据迁移时需要确保数据类型能够正确转换，避免数据丢失或错误。 8. 迁移后的验证：迁移完成后，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

【Air】

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值