当前位置：首页 > news >正文

mysql 问题解答

news 2025/9/8 2:17:32

01 Mysql有哪些数据类型

MySQL支持多种数据类型，这些类型可以分为几个大的类别：数值类型、日期和时间类型、字符串（字符和字节）类型、空间类型、JSON类型。下面是每种类型的简要说明和用途，以及示例。

数值类型

整型:
- TINYINT：非常小的整数，如性别标识（0代表女性，1代表男性）。
- SMALLINT：小型整数，如年龄字段。
- MEDIUMINT：中型整数，可能用于存储社区内的用户数量。
- INT或INTEGER：标准整数，适用于存储更大范围的数值，如用户ID。
- BIGINT：更大范围的整数，例如存储社交媒体平台的全球用户ID。
每种整型都有相应的UNSIGNED属性，可以存储更大的正数值。
浮点型和定点型:
- FLOAT：单精度浮点数，如存储商品的价格。
- DOUBLE：双精度浮点数，如存储科学数据或精确计算。
- DECIMAL或NUMERIC：用于存储精确的小数，常用于财务计算，例如账户余额。

日期和时间类型

DATE：包含日期值的列（年月日），例如存储用户的生日。
TIME：包含时间值（时分秒），例如记录事件的持续时间。
DATETIME：包含日期和时间值，用于记录文章发布的具体时间。
TIMESTAMP：包含日期和时间值，通常用于记录数据行的创建和修改时间。
YEAR：年份值，比如表示汽车的制造年份。

字符串类型

字符型:
- CHAR：定长字符串，如存储车牌号。
- VARCHAR：变长字符串，适用于存储长度不固定的文本，如用户姓名。
文本型:
- TINYTEXT：非常短的文本，如注释。
- TEXT：标准的文本数据，如博客文章。
- MEDIUMTEXT：中等长度的文本，如较长的文章。
- LONGTEXT：极长文本数据，适用于存储大型文本，如书籍内容。
二进制型:
- BINARY、VARBINARY：类似于CHAR和VARCHAR，但是用于二进制字符串。
- TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB：用于存储二进制数据，如图片或文件。

空间类型

GEOMETRY、POINT、LINESTRING、POLYGON 等：这些类型用于存储地理空间数据，例如在地图应用中标示位置或区域。

JSON类型

JSON：用于存储JSON（JavaScript Object Notation）文档。在需要存储非结构化数据或复杂数据结构的应用中非常有用，例如存储配置参数或临时数据。

示例应用场景

假设你正在开发一个电子商务平台，你可能需要以下数据类型：

商品ID：INT 或 BIGINT（如果预计商品数量非常大）。
商品价格：DECIMAL（用于保证价格的精确度）。
用户名：VARCHAR（因为用户名长度可能不同）。
用户密码：VARCHAR（通常存储密码的散列值）。
商品描述：TEXT（因为描述的长度可能超出VARCHAR的限制）。
用户注册时间：DATETIME（记录日期和时间）。
商品图片：BLOB 或直接存储图片的URL（如果图片以文件形式存储在数据库中）。
用户地理位置：POINT（如果需要存储用户的精确位置）。
用户配置：JSON（可以灵活地存储用户的配置设置）。

在设计数据库时，选择合适的数据类型对于优化性能、空间利用和数据完整性至关重要。正确的数据类型不仅可以减少存储空间的浪费，还可以加快查询速度，确保数据的准确性。

04 CHAR，VARCHAR 和 Text 的区别？

text用于存储文章，博客等任何可能超过VARCHAR限制的文本。
在MySQL中，TEXT类型的列不能指定默认值。这是因为TEXT类型是用来存储大段文本的，它们通常是不定长的，可以存储非常长的字符串，而MySQL不允许给这种类型的列指定默认值。

此外，TEXT类型的数据通常被存储在表的数据页之外。这意味着当数据库存储和检索这种类型的数据时，可能需要更多的磁盘I/O操作，因为它必须跨越不同的位置。这种存储方式与CHAR和VARCHAR类型相比，可能会使得处理速度较慢。

下面是一个简单的例子来解释这一点：

假设你有一个博客系统，每篇博客的内容可能包括大量文本。你可以为博客内容创建一个TEXT类型的列。

CREATE TABLE blog_posts (id INT AUTO_INCREMENT PRIMARY KEY,title VARCHAR(255),content TEXT,published_date DATETIME
);

在这个blog_posts表中，content列被定义为TEXT类型，因为博客文章可能非常长，超过了VARCHAR类型的最大长度限制。

现在，如果你想为content列设置一个默认值（比如一个空字符串），这样的操作是不被允许的：

ALTER TABLE blog_posts ALTER COLUMN content SET DEFAULT '';

执行这条语句会导致错误，因为TEXT类型的列不支持默认值。

当你要查询表中的博客文章时：

SELECT * FROM blog_posts WHERE id = 1;

MySQL首先从表的主数据页中读取行的基本信息，如果content列的数据存储在表外，MySQL还需要做额外的I/O操作去检索这部分文本数据，这可能比直接从表内读取数据慢。

这种表外存储通常适用于TEXT和BLOB类型的数据，因为它们的长度可能会非常长，如果存储在表内可能会造成表的数据页过大，影响其他列的检索性能。通过这种方式，MySQL可以更有效地管理内存和存储空间，尽管可能会牺牲一些性能。

表外存储

“表外存储”（也被称作"溢出"或"外部存储"）的概念，是指数据实际上并不存储在数据库的主数据文件中的同一个数据页（或行）上。在MySQL中，尽管所有数据都存储在MySQL服务器管理的文件系统中，某些类型的数据可能不会直接存储在表的主数据结构中。

在MySQL中，一个表通常由一系列的数据页组成，每页大小默认为16KB。对于大部分的数据类型，如INT、CHAR、VARCHAR，其数据是直接存储在数据页内部的。这意味着数据与表的其他部分紧密排列在一起，可以通过单个磁盘I/O操作读取。

然而，对于TEXT和BLOB这样的大型数据类型，如果文本或二进制数据非常大，这些数据可能不会完全存储在同一个数据页中，特别是当它们超过页大小时。MySQL会将数据的一个部分或全部存储在表的主结构之外的地方，仅仅在数据页中保留一个指针来引用这些数据。当需要查询这些数据时，MySQL首先读取数据页以获取指针，然后通过指针去读取实际存储的数据，这就需要额外的磁盘I/O操作。

这种设计允许MySQL更有效地处理大量的大型数据对象，因为这些大对象如果存储在表内，会极大地增加表的大小，导致常规查询的性能下降。通过将它们存储在表外，MySQL能够维持数据页的紧凑性和效率，同时仍能处理大型数据。

虽然从逻辑上看，这些数据仍然是表的一部分，但从物理存储的角度来看，它们是分开存储的，而且可能分散在不同的磁盘位置。这就是所谓的"表外存储"。

05 什么是三大范式？

数据库设计的"三大范式"是指三个基本的规则，用来指导数据库表结构的设计，以减少数据的冗余和改善数据的逻辑结构。遵循这些范式可以帮助设计出既节省空间又能提高效率的数据库。

第一范式(1NF)：列的原子性

规则：表的每一列都是不可分割的原子数据项，同一列中的所有数据项都是相同类型的单一数据。

解释：每个字段都应该是最小的数据单位，不可以再分割。例如，你不应该在一个字段中存储多个电话号码，而是应该为每个电话号码提供单独的字段。

例子：
不符合1NF的表结构：

| 客户ID | 客户名称 | 订单        |
|--------|----------|------------|
| 1      | 张三     | 订单1, 订单2 |
| 2      | 李四     | 订单3      |

在上面的表中，订单列包含了多个订单号，不满足1NF的要求。

符合1NF的表结构：

| 客户ID | 客户名称 | 订单   |
|--------|----------|-------|
| 1      | 张三     | 订单1 |
| 1      | 张三     | 订单2 |
| 2      | 李四     | 订单3 |

在这个修改后的表中，每个订单都有自己的行，满足了1NF。

第二范式(2NF)：消除部分依赖

规则：在1NF的基础上，表必须有一个主键，并且非主键字段必须完全依赖于主键，而不是依赖于主键的一部分（只适用于组合主键）。

解释：如果一个表有一个组合主键，那么表中的其他数据应该与整个主键有关系，而不是仅与主键的一部分有关系。

例子：
不符合2NF的表结构：

| 学生ID | 课程ID | 学生姓名 | 课程名称   |
|--------|--------|----------|-----------|
| 1      | 101    | 张三     | 数学       |
| 1      | 102    | 张三     | 物理       |
| 2      | 101    | 李四     | 数学       |

在这个表中，学生姓名只依赖于学生ID，而课程名称只依赖于课程ID。这违反了2NF。

符合2NF的表结构：

学生表：