2023年MySQL核心技术第一篇

目录

一 . 存储：一个完整的数据存储过程是怎样的？

1.1 数据存储过程

 1.1.1 创建MySQl 数据库

1.1.1.1 为什么我们要先创建一个数据库，而不是直接创建数据表？

1.1.1.2基本操作部分

1.2 选择索引问题

二 . 字段：这么多的字段类型，应该如何定义

2.1 简介

2.1.1 例子：

2.1.1.1解释：

2.2 整数类型

2.2.1 如何选择合适的整数类型

2.3 浮点数类型和定点类型

2.3.1 为什么浮点数类型的无符号只有有符号的一半取值范围？

2.3.2 浮点数的精度问题

2.3.2.1 建表：

2.3.2.2 然后插入数据

2.3.2.3 查看表里面的数据

2.3.2.4 利用SQl语句进行价格相加：

2.3.2.4.1 sum：

2.3.2.4.1.1 导致这个的原因;

2.3.2.5 解决小数的精度问题 

2.4 文本类型

2.4 .1 详细解释TEXT类型

---

一 . 存储：一个完整的数据存储过程是怎样的？

1.1 数据存储过程

 MySQL是怎么进行数据存储的。

存储数据是处理数据的第一步，对各种繁杂的数据，进行有序和高效地存储起来。

在MySQL中，完整的数据存储过程共有4步，分别是 创建数据库，确认字段，创建数据表，插入数据。

 1.1.1 创建MySQl 数据库

数据存储的第一步就是创建数据库。

1.1.1.1 为什么我们要先创建一个数据库，而不是直接创建数据表？

数据库是MySQL里面最大的存储单元，系统架构层次上看，MySQL数据库系统，从小到大依次是数据库服务器，数据库，数据表，数据表的行与列。

没有数据库，数据表就没有载体，就无法存储数据。

1.1.1.2基本操作部分

创建数据库

create database demo；

删除数据库

drop database demo；

查看数据库

show databases；

创建数据表：

create table demo.test

(

barcode text,

goodsname text,

price int

)；

查看表结构

describe  demo.test;

查看所有表

show tables；

添加主键

alter table demo.test

add column itemnumber int primary key auto_increment

向表添加数据

insert into demo.test

（barcode，goodsname，price）

values（'0001'，'本'，3）；

1.2 选择索引问题

select count(*) from t; t中有id(主键)，name，age,sex4个字段。假设数据10条，对sex添加索引。用explain 查看执行计划发现用了sex索引，为什么不是主键索引呢?主键索引应该更快的.

解答：

MySQL Innodb的主键索引是一个B+树，数据存储在叶子节点上，10条数据，就有10个叶子节点。
1. sex索引是辅助索引，也是一个B+树，不同之处在于，叶子节点存储的是主键值，由于sex只有2个
可能的值：男和女，因此，这个B+树只有2个叶子节点，比主键索引的B+树小的多
2. 这个表有主键，因此不存在所有字段都为空的记录，所以COUNT(*)只要统计所有主键的值就可以
了，不需要回表读取数据
3. SELECT COUNT(*) FROM t，使用sex索引，只需要访问辅助索引的小B+树，而使用主键索引，要
访问主键索引的那个大B+树，明细工作量大，这就是为什么，优化器使用辅助索引的原因

二 . 字段：这么多的字段类型，应该如何定义

2.1 简介

 MySQl中有很多字段类型，比如整数，文本，浮点数。

2.1.1 例子：

在销售流水表中，需要定义商品销售的数量。由于有称重
商品，不能用整数，想当然地用了浮点数，为了确保精度，还用了 DOUBLE 类型。
结果却造成了在没有找零的情况下，客人无法结账的重大错误，DOUBLE 类型是不精准的，不能使用。

2.1.1.1解释：

浮点数在计算机中的内部表示是二进制的，而不是十进制的。对于某些常见的十进制小数（如0.1），其在二进制表示中是一个无限循环的小数。这样就存在一些十进制小数无法准确转换为浮点数的二进制表示。

当进行浮点数计算时，舍入误差会逐渐累积。即使看似简单的计算，例如0.1 + 0.1 + 0.1，也可能产生一个微小的舍入误差。这意味着在处理货币或计量单位时，通过浮点数计算得到的结果可能与预期的结果有细微差异。

在结账场景中，如果使用浮点数（DOUBLE）存储商品销售的数量和金额，并进行计算，那么可能会出现舍入误差。例如，如果商品价格是0.1元，数量是3个，正确的总金额应该是0.3元。但由于浮点数的舍入误差，实际计算时可能得到一个接近0.30000000000000004的结果。这样就导致无法准确匹配预期的金额，客人无法正确结账。

因此，在处理与货币或计量单位相关的数据时，浮点数（DOUBLE）类型不是一个理想的选择，因为它可能引发舍入误差和精度问题。更好的选择是使用固定点数类型（如DECIMAL），它可以提供更高的精确度和准确性来处理这些情况，避免结账错误的发生。

2.2 整数类型

整数类型一共5种：tinyint ，smallint，mediumint，int（integer），bigint。

2.2.1 如何选择合适的整数类型

需要考虑存储空间和可靠性的平衡问题：

1.占用字节数少的整数类型可以节省出存储空间，如果太小了，可能会出现超出取值范围的情况，引发系统问题。

例子：

在我们的项目中，商品编号采用的数据类型是 INT。
我们之所以没有采用占用字节更少的 SMALLINT 类型整数，原因就在于，客户门店中流通的
商品种类较多，而且，每天都有旧商品下架，新商品上架，这样不断迭代，日积月累。如果使
用 SMALLINT 类型，虽然占用字节数比 INT 类型的整数少，但是却不能保证数据不会超出范
围 65535。相反，使用 INT，就能确保有足够大的取值范围，不用担心数据超出范围影响可
靠性的问题。

注意：实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本，我们应该首先确保数据不会超过取值范围，在这个前提下考虑如何节省存储空间。

2.3 浮点数类型和定点类型

浮点数和定点数的特点是可以处理小数，将整数看成小数的特例。

浮点数类型：float，double，real 

float 表示单精度浮点数；4字节

double 表示 双精度浮点数   8字节

real 默认 double，

如果要float：set sql_mode = " real_as_float";

2.3.1 为什么浮点数类型的无符号只有有符号的一半取值范围？

原因是，MySQL 是按照这个格式存储浮点数的：符号（S）、尾数（M）和阶
码（E）。因此，无论有没有符号，MySQL 的浮点数都会存储表示符号的部分。因此，所谓
的无符号数取值范围，其实就是有符号数取值范围大于等于零的部分。

2.3.2 浮点数的精度问题

2.3.2.1 建表：

CREATE TABLE demo.goodsmaster
(
barcode TEXT,
goodsname TEXT,
price DOUBLE,
itemnumber INT PRIMARY KEY AUTO_INCREMENT
);

2.3.2.2 然后插入数据

-- 第一条
INSERT INTO demo.goodsmaster
(
barcode,
goodsname,
price
)
VALUES
(
'0001',
'书',
0.47
);
-- 第二条
INSERT INTO demo.goodsmaster
(
barcode,
goodsname,
price
)
VALUES
(
'0002',
'笔',
0.44
);
-- 第三条
INSERT INTO demo.goodsmaster
(
barcode,
goodsname,
price
)
VALUES
(
'0002',
'胶水',
0.19
);

2.3.2.3 查看表里面的数据

SELECT * from demo.goodsmaster;

结果：

mysql> SELECT *
-> FROM demo.goodsmaster;
+---------+-----------+-------+------------+
| barcode | goodsname | price | itemnumber |
+---------+-----------+-------+------------+
| 0001 | 书 | 0.47 | 1 |
| 0002 | 笔 | 0.44 | 2 |
| 0002 | 胶水 | 0.19 | 3 |
+---------+-----------+-------+------------+
3 rows in set (0.00 sec)

2.3.2.4 利用SQl语句进行价格相加：

SELECT SUM(price)
FROM demo.goodsmaster;

2.3.2.4.1 sum：

关键字sum，MySQL的求和函数，MySQL聚合函数的一种，知道这个函数表述计算字段值的和就可以了。

我们应该理想的值，0.47+0.44+0.19 =1.1

结果：

mysql> SELECT SUM(price)
-> FROM demo.goodsmaster;
+--------------------+
| SUM(price) |查询结果是 1.0999999999999999

 将类型改成float，输出的值为1.0999999940395355，误差更大。

当我们需要进行值对比作为条件进行查询的时候，就会发生误差。

比如：

SELECT *
FROM demo.goodsmaster
WHERE SUM(price)=1.1

2.3.2.4.1.1 导致这个的原因;

出在 MySQL 对浮点类型数据的存储方式上。
MySQL 用 4 个字节存储 FLOAT 类型数据，用 8 个字节来存储 DOUBLE 类型数据。无论哪
个，都是采用二进制的方式来进行存储的。比如 9.625，用二进制来表达，就是 1001.101，
？如果尾数不是 0 或 5（比如 9.624）。

在计算机中，所有数字都是以二进制的形式表示的。因此，将十进制小数转换为二进制小数是非常重要的，这样计算机才能够进行准确的计算和精确的表示。证明这种转换方式的正确性其实很简单。我们知道，在十进制中，小数点左侧的每一位数位的权重都是10的非负整数次幂，如：1000   100    10    1   .     1/10    1/100    1/1000
10^3   10^2   10^1  10^0     10^-1   10^-2   10^-3同样地，在二进制中，小数点右侧的每一位数位的权重都是2的负整数次幂，如：.     1/2    1/4    1/8   1/16   1/32   1/64
2^-1  2^-2   2^-3   2^-4  2^-5   2^-6   2^-7因此，我们可以将十进制小数乘以2，并取整数部分，再将所得到的小数部分继续乘以2，直到小数部分为0或者达到所需要的位数。这样，就可以通过二进制小数精确地表示十进制小数。当然，二进制小数并非适用于所有的十进制小数，有些十进制小数是无法精确地表示为有限的二进制小数的。但是，在实际应用中，我们通常只需要使用有限位数的二进制小数来进行计算。因此，通过乘以2并取整的方法来转换小数可以满足大多数应用场景的需求。

你就无法用一个二进制数来精确表达。怎么办呢？就只好在取值允许的范围内进行近似（四舍五入）。现在你一定明白了，为什么数据类型是 DOUBLE 的时候，我们得到的结果误差更小一些，而数据类型是 FLOAT 的时候，误差会更大一下。原因就是，DOUBLE 有 8 位字节，精度更高。

2.3.2.5 解决小数的精度问题 

定点数类型 decimal是一种用于精确表示小数的数据类型，它的实现方式可以保证精度的正确性。 decimal 类型一般由两个部分组成：整数部分和小数部分。整数部分用于表示正负号和整数部分的数值，而小数部分用于表示小数的位数和数值。

在 decimal 类型中，精度是由小数部分的位数定义的。

例子：

decimal(5,2) 表示总共有 5 位数，其中 2 位是小数位。这意味着 decimal 类型可以表示从 -99.99 到 +99.99 的范围内的任意小数，且小数部分会被精确地保留到两位小数。

decimal类型的实现会使用一些算法来确保精度的正确性，例如四舍五入、进位等。这样，无论使用 decimal 类型进行何种数学运算，系统都会在内部处理这些算法，以保证最终结果的精度是正确的。

因此，使用 decimal 类型可以有效地确保小数的精度是正确的。在进行计算过程中，如果需要保持较高的精度，请选择合适的 decimal 类型和位数，以适应应用的需求。

2.4 文本类型

 对于存储的条码、商品名称，都是字符串数据。这两个字段的数据类型，我们可能都选择了 TEXT 类型。

TEXT 类型是 MySQL 支持的文本类型的一种。此外，MySQL 还支持 CHAR、VARCHAR、
ENUM 和 SET 等文本类型。

CHAR(M)：固定长度字符串。CHAR(M) 类型必须预先定义字符串长度。如果太短，数据
可能会超出范围；如果太长，又浪费存储空间。

VARCHAR(M)： 可变长度字符串。VARCHAR(M) 也需要预先知道字符串的最大长度，不
过只要不超过这个最大长度，具体存储的时候，是按照实际字符串长度存储的。

TEXT：字符串。系统自动按照实际长度存储，不需要预先定义长度。

ENUM： 枚举类型，取值必须是预先设定的一组字符串值范围之内的一个，必须要知道字
符串所有可能的取值。

SET：是一个字符串对象，取值必须是在预先设定的字符串值范围之内的 0 个或多个，也必
须知道字符串所有可能的取值。

2.4 .1 详细解释TEXT类型

TEXT 类型也有 4 种，它们的区别就是最大长度不同。
TINYTEXT：255 字符（这里假设字符是 ASCII 码，一个字符占用一个字节，下同）。

TEXT： 65535 字符。
MEDIUMTEXT：16777215 字符。
LONGTEXT： 4294967295 字符（相当于 4GB）。

TEXT 有一个问题：由于实际存储的长度不确定，

MySQL 不允许TEXT 类型的字段做主键。

遇到这种情况，我们只能采用 CHAR(M)，或者 VARCHAR(M)。