mysql 问题解答

01 Mysql有哪些数据类型

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

数值类型

1. 整型:

   • TINYINT：非常小的整数，如性别标识（0代表女性，1代表男性）。
   • SMALLINT：小型整数，如年龄字段。
   • MEDIUMINT：中型整数，可能用于存储社区内的用户数量。
   • INT或INTEGER：标准整数，适用于存储更大范围的数值，如用户ID。
   • BIGINT：更大范围的整数，例如存储社交媒体平台的全球用户ID。

   每种整型都有相应的UNSIGNED属性，可以存储更大的正数值。

2. 浮点型和定点型:

   • FLOAT：单精度浮点数，如存储商品的价格。
   • DOUBLE：双精度浮点数，如存储科学数据或精确计算。
   • DECIMAL或NUMERIC：用于存储精确的小数，常用于财务计算，例如账户余额。

日期和时间类型

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

字符串类型

1. 字符型:

   • CHAR：定长字符串，如存储车牌号。如果一个字段总是接近或等于最大长度，使用CHAR可能比VARCHAR更高效，因为VARCHAR需要额外的空间来存储长度信息。
   • VARCHAR：变长字符串，适用于存储长度不固定的文本，如用户姓名。

2. 文本型:

   • TINYTEXT：非常短的文本，如注释。
   • TEXT：标准的文本数据，如博客文章。
   • MEDIUMTEXT：中等长度的文本，如较长的文章。
   • LONGTEXT：极长文本数据，适用于存储大型文本，如书籍内容。

3. 二进制型:

   • 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类型相比，可能会使得处理速度较慢。

数据页

每个表里有多个数据页。
数据页就是存储在数据库文件中的一个数据块，这个数据块有固定的大小，比如16KB。每个数据页可以包含多条记录（即多行数据）。
提高性能：当数据库需要读取数据时，它会加载整个数据页到内存中。这意味着即使只需要页中的一小部分数据，整个页也会被加载。这种方式减少了硬盘I/O操作，从而提高了性能

在数据库中，TEXT类型的数据通常被存储在表的数据页之外，这句话的意思是，当TEXT类型的数据超过了数据页的大小限制时，数据库系统会将其存储在数据页之外的其他存储位置。
在数据库中，数据通常被存储在数据页中，每个数据页通常可以存储一定数量的数据行。然而，TEXT类型的数据通常比较大，可能无法完全存储在一个数据页中。因此，数据库系统会将TEXT类型的数据存储在数据页之外的其他位置，例如另一个文件或磁盘上的其他区域。
这种存储方式可以避免由于TEXT类型的数据过大而导致的数据页碎片化，从而提高数据库的性能和效率。同时，这种存储方式也使得TEXT类型的数据可以存储更大的内容，而不受数据页大小的限制。

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

假设你有一个博客系统，每篇博客的内容可能包括大量文本。你可以为博客内容创建一个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的表结构：

学生表：

| 学生ID | 学生姓名 |
|--------|----------|
| 1      | 张三     |
| 2      | 李四     |

课程表：

| 课程ID | 课程名称   |
|--------|-----------|
| 101    | 数学       |
| 102    | 物理       |

选课表：

| 学生ID | 课程ID |
|--------|--------|
| 1      | 101    |
| 1      | 102    |
| 2