MySQL第一讲·存储与字段

你好，我是安然无虞。

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

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

·创建MySQL数据库

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

为什么不是直接创建数据表，而是创建一个数据库呢？

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

数据库是MySQL里面的最大的存储单元，没有数据库，数据表就没有载体，也就无法存储数据。

1.如何创建数据库？

创建数据库就是一条简单的SQL语句：

create database demo;

2.查看数据库也是一条简单的SQL语句：

show databases;

看到的结果是：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| demo               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

让我们疑惑的是，我们刚才明明只创建了一个数据库demo，为什么查看数据库的时候显示的是5个呢？多余的这4个数据库是系统自带的吗，里面存储的是什么数据呢？

• “demo”是我们通过 SQL 语句创建的数据库，是我们用来存储用户数据的，也是我们使用的主要数据库
• “information_schema”是 MySQL 系统自带的数据库，主要保存 MySQL 数据库服务器的系统信息，比如数据库的名称、数据表的名称、字段名称、存取权限、数据文件所在的文件夹和系统使用的文件夹，等等
• “performance_schema”是 MySQL 系统自带的数据库，可以用来监控 MySQL 的各类性能指标。“sys”数据库是 MySQL 系统自带的数据库，主要作用是，以一种更容易被理解的方式展示 MySQL 数据库服务器的各类性能指标，帮助系统管理员和开发人员监控 MySQL 的技术性能
• “mysql”数据库保存了 MySQL 数据库服务器运行时需要的系统信息，比如数据文件夹、当前使用的字符集、约束检查信息，等等

·确认字段

数据存储流程的第二步是确认表的字段。

数据表中的列就是MySQL数据表的字段。

MySQL数据表由行与列组成，一行就是一条数据记录，每一条数据记录都被分成许多列，一列就叫一个字段。每个字段都需要定义数据类型，这个数据类型叫做字段类型。

MySQL支持多种字段类型，字段的定义会影响数据的取值范围、精度，以及系统的可靠性。

·创建数据表

数据存储流程的第三步，就是创建数据表。

MySQL中的数据表是什么呢？你可以把它看成用来存储数据的最主要的工具。

数据表对存储在里面的数据进行组织和管理，是数据变得有序，并且能够实现高效查询和处理。

通过SQL语句创建表：

create table demo.test
( 
  barcode text,
  goodsname text,
  price int
); 

有两点需要注意：

• 创建表的时候，最好指明数据库；
• 不要在最后一个字段后面加上’,’

1.如何查看表的结构？

创建好了表，如何通过SQL语句查看表的结构呢？

desc demo.test;

运行结果如下：

mysql> desc demo.test;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| barcode   | text | YES  |     | NULL    |       |
| goodsname | text | YES  |     | NULL    |       |
| price     | int  | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
3 rows in set (0.00 sec)

那上面这些信息都代表着什么意思呢？

• Field：表示字段名称。
  Type：表示字段类型，这里 barcode、goodsname 是文本型的，price 是整数类型的。
• Null：表示这个字段是否允许是空值（NULL）。这里你一定要注意，在 MySQL 里面，空值不等于空字符串。一个空字符串的长度是 0，而一个空值的长度是空。而且，在 MySQL 里面，空值是占用空间的。
• Key：我们暂时把它叫做键。
• Default：表示默认值。我们导入的表的所有的字段都允许是空，默认值都是 NULL。
• Extra：表示附加信息。

2.如何查看数据库中的表？

先通过use语句选择数据库：

use demo;

运行后，进入该数据库，然后通过show命令，查看这个数据库中的所有表：

show tables;

运行结果如下：

mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| test           |
+----------------+
1 row in set (0.00 sec)

3.如何设置主键？

主键可以确保数据的唯一性，而且能够减少数据错误。

举个现实生活当中的例子就是，主键就像是咱们的身份证号码，它是唯一的。每个身份证号码都只能唯一对应一个人，同样的，每个人都只有唯一的身份证号码。

MySQL中数据表的主键，是表中的一个字段或者几个字段的组合，它主要有3个特征：

• 必须唯一，不能重复；
• 不能是空；
• 必须可以唯一标识数据表中的记录

一个MySQL数据表中只能有一个主键。因为主键可以帮助我们减少错误数据，并且提高查询的速度，所以建议养成给表都定义主键的习惯。

举个例子，假如我们有下面这张表：

这是一张商品表，名字是‘goodsmaster’，那么在这张表里，哪个字段可以设置成为主键呢？

商品名称‘goodsname’可以不？不可以，因为重名的商品太多了，比如“笔”，各式各样的笔…由于可能重复，所以商品名称和数据记录之间不能形成一一对应的关系，所以不能将其设置成为主键，同理，价格也不可以。

那剩下最后一个商品条码‘barcode’可以成为这个数据表的主键吗？

好像是可以的诶，因为商品的条码一般都是被统一编码的，一种商品对应一个条码，一个条码对应一种商品，正好不就符合一一对应关系吗？

但是在实际生活中，是存在例外的情况的。比较常见的就是有很多门店都会自己生产商品，比如，馒头、面条等自产商品。为了方便管理，门店往往会自己给它们设置条码，这样一来就容易产生重复的现象。

这样一来，商品条码‘barcode’也有重复的可能，所以也不能设置成主键。

那么，如果数据表中的所有的字段都有重复的可能，我们怎么设置主键呢？

答案是我们可以自己添加一个不会重复的字段来做主键。

比如依然是上面的例子，我们就可以添加一个字段，字段类型是整数，我们给它取个名字叫做‘itemnumber’，而且，我们可以每次增加一条新数据的时候，让这个字段的值自动加1，这样就永远不会重复了，如下图：

我们添加字段商品编号‘itemnumber’为主键，我们的商品表’goodsmaster’就有了主键。

我们可以通过SQL语句来实现上述增加一个主键字段的功能：

alter table demo.test
add column itemnumber int primary key auto_increment;

其中上面关键字的含义如下：

• alter table: 表示修改表结构
• add column: 表示增加一列
• primary key: 表示这一列是主键
• auto_increment: 表示每增加一条记录，这个值会自动增加

·插入数据

数据存储流程的第四步，也是最后一步，是把数据插入到表中。

通过SQL语句将数据插入到表中：

insert into demo.test
(barcode, goodsname, price)
values ('0001', ‘本’, 3);

这里的insert into表示向demo.test中插入数据，后面是要插入数据的字段名，values表示对应的值。

在添加数据时，有两点需要注意：

• 建议每次都将需要插入的字段名加上，这样的好处是可读性好，不易出错，而且容易修改
• 由于字段 itemnumber 定义了 auto_increment，所以我们插入一条记录的时候，不给它赋值，系统也会自动给它赋值。而且，每次赋值都会在上次上次赋值的基础上，自动增加1

汇总一下上述用到的8种SQL语句：

-- 创建数据库
create database demo;

-- 删除数据库
drop database demo;

-- 查看数据库
show databases;

-- 创建数据表：
create table demo.test
(  
  barcode text,
  goodsname text,
  price int
); 

-- 查看表结构
desc 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);

---

2. 字段：那么多字段类型，该怎么定义？

MySQL 中有很多字段类型，比如整数、文本、浮点数，等等。如果类型定义合理，就能节省存储空间，提升数据查询和处理的速度，相反，如果数据类型定义不合理，就有可能会导致数据超出取值范围，引发系统报错，甚至可能会出现计算错误的情况，进而影响到整个系统。

所以我们需要掌握不同字段的类型，包括它们的适用场景，定义方法。

·整数类型

MySQL中整数类型一共有5中，包括 TINYINT、SMALLINT、MEDIUMINT、INT（INTEGER）和 BIGINT，它们的区别如下表所示：

这么多整数类型，咱们该如何进行选择呢？

其实，在评估用哪种整数类型的时候，需要考虑存储空间和可靠性的平衡问题：一方面，用占用字节数较少的整数类型可以节省存储空间；另一方面，要是为了节省存储空间，使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起系统错误，影响可靠性。

在实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，这里给出的建议是，首先确保数据不会超出取值范围，在这个前提下，再去考虑如何节省存储空间。

·浮点数类型和定点数类型

浮点数和定点数类型的特点就是可以处理小数，我们可以把整数看作成为小数的一个特例。因此，浮点数和定点数的使用场景，就比整数大多了。

首先，MySQL中支持的浮点数分别是：FLOAT、DOUBLE、REAL

• FLOAT：单精度浮点数

• DOUBLE：双精度浮点数

• REAL：默认就是DOUBLE。如果把SQL模式设置为启用‘REAL_AS_FLOAT’，那么，MySQL就认为REAL是FLOAT。上述可通过SQL语句完成：

  set sql_mode = "real_as_float";
  

那么，float和double的区别是啥呢？

其实，float就是占用字节数少，取值范围小；double就是占用字节数多，取值范围大。

我们发现一个问题：为什么浮点数类型的无符号数取值范围只相当于有符号数取值范围的一半，也就是只相当于有符号数取值范围大于等于零的部分呢？

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

主要注意的是，浮点数的类型有个缺陷，就是不精准。

因此，在一些对于精准度要求高的项目中，千万不要使用浮点数，不然会导致结果错误，甚至是造成不可挽回的损失。

为了方便理解，我们来借助一个实际中的例子进行演示：

我们先创建一个goodsmaster的表：

create table demo.goodsmaster
(
		barcode text,
  	goodsname text, 
  	price double,
  	itemnumber int primary key auto_increment
);

上面的price字段类型是double的，下面给这张表插入几条数据：

-- 第一条
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, goodsmaster, price)
values ('0003', '本', 0.19);

现在执行一条查询语句，看看表中的数据：

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)

然后我们执行下面的SQL语句，将这3种物品价格加在一起，看看得到了什么:

select SUM(price) from demo.goodsmaster;

这里的关键字SUM是MySQL中的求和函数，是MySQL聚合函数的一种。

我们期待的运行结果是：0.47 + 0.44 + 0.19 = 1.1，实际上得到的是：

mysql> select SUM(price) from demo.goodsmaster;
+--------------------+
| SUM(price)         |
+--------------------+
| 1.0999999999999999 |
+--------------------+

查询结果是：1.0999999999999999，虽然误差很小很小，但是确实存在误差。

将数据类型换成float，得到的结果是1.0999999940395355，显然误差更大了。

虽然，1.10和1.0999999999999999差不多，但是我们有时候需要通过数值比对为条件进行查询，一旦出现一丝一毫的误差，就查不出需要的结果了；

select * from demo.goodsmaster where sum(price)=1.1;

那么，问什么会存在这样的误差呢？

问题还是出现在MySQL对浮点数类型数据的存储方式上。

MySQL 用 4 个字节存储 FLOAT 类型数据，用 8 个字节来存储 DOUBLE 类型数据。无论哪个，都是采用二进制的方式来进行存储的。比如 9.625，用二进制来表达，就是 1001.101，或者表达成 1.001101×2^3。看到了吗？如果尾数不是 0 或 5（比如 9.624），你就无法用一个二进制数来精确表达。怎么办呢？就只好在取值允许的范围内进行近似（四舍五入）。

现在你一定明白了，为什么数据类型是 DOUBLE 的时候，我们得到的结果误差更小一些，而数据类型是 FLOAT 的时候，误差会更大一下。原因就是，DOUBLE 有 8 位字节，精度更高。

关于数据在内存中的存储，有兴趣的老铁可以看看之前我写的一篇文章：

C语言·深度剖析数据在内存中的存储

那么在MySQL中有没有精准的数据类型呢？当然了，就是定点数类型：DECIMAL。

就像浮点数的存储方式，决定了它不可能精准一样，定点数的存储方式也决定了它一定是精准的。

浮点数类型是把十进制转换成为二进制数进行存储，定点数则不同，它是把十进制数的整数部分和小数部分拆开，分别转换成为十六进制数，进行存储。这样，所有的数值，就都可以精准表达了，不会存在无法表达而损失精度的问题。

MySQL用DECIMAL（M，D）的方式表示高精度小数。其中，M表示整数部分加小数部分，一共有多少位，M<=65。D表示小数部分位数，D<M。

还是用刚刚的goodsmaster表进行验证：

首先修改表中price字段的数据类型为decimal(5, 2)：

alter table demo.goodsmaster
modify column price decimal(5, 2);

然后，我们在进行一次求和运算：

select sum(price) from demo.goodsmaster;

这次得到的结果就是预期结果啦。

简单小结一下浮点数和定点数的特点：

浮点数类型的取值范围大，但是不精准，适用于需要取值范围大，又可以容忍微笑误差的科学计算场景；

定点数类型取值范围相对较小，但是精准，没有误差，适合于对精准度要求极高的场景，比如涉及到金额的计算。

·文本类型

在实际的项目中，我们还会经常遇到一种数据，就是字符串数据。

比如，刚刚那个简单的表demo.goodsmaster中，有两个字段’barcode’和’goodsname’. 它们当中存储的条码，商品名称，都属于字符串类型。这两个字段的数据类型，我们都选择了text类型。

text类型是MySQL支持的文本类型的一种。

此外，MySQL还支持char, varchar, enum和set等文本类型。

我们来看看它们的区别：

• CHAR(M)：固定长度字符串。CHAR(M) 类型必须预先定义字符串长度。如果太短，数据可能会超出范围；如果太长，又浪费存储空间。
• VARCHAR(M)： 可变长度字符串。VARCHAR(M) 也需要预先知道字符串的最大长度，不过只要不超过这个最大长度，具体存储的时候，是按照实际字符串长度存储的。
• TEXT：字符串。系统自动按照实际长度存储，不需要预先定义长度。
• ENUM： 枚举类型，取值必须是预先设定的一组字符串值范围之内的一个，必须要知道字符串所有可能的取值。
• SET：是一个字符串对象，取值必须是在预先设定的字符串值范围之内的 0 个或多个，也必须知道字符串所有可能的取值。

因为不需要预先知道字符串的长度，系统会按照实际的数据长度进行存储，所以text类型最为灵活方便，我们重点学习一下它。

text类型也有4种，它们的区别就是最大长度不同。

• TINYTEXT：255 字符（这里假设字符是 ASCII 码，一个字符占用一个字节，下同）
• TEXT： 65535 字符
• MEDIUMTEXT：16777215 字符
• LONGTEXT： 4294967295 字符（相当于 4GB）

需要注意的是，text也有一个问题：由于实际存储的长度不确定，MySQL不允许text类型的字段做主键。遇到这种情况，我们只能采用char(M)或者varchar(M)

·日期与时间类型

日期和时间类型也是经常会被使用到的。

日期与时间是重要的信息，在我们的系统中，几乎所有的数据表都能够用得到。原因是客户需要知道数据的时间标签，从而进行数据查询，统计和处理。

用得最多的日期时间类型，就是datetime，因为这个数据类型包括了完整的日期和时间信息，使用起来比较方便。

为了确保数据的完整性和系统的稳定性，优先考虑使用datetime类型。

虽然datetime占用空间最多，但是它表达的时间最为完整，取值范围最大。

还有个问题，为什么时间类型 TIME 的取值范围不是 -23:59:59～23:59:59 呢？原因是 MySQL 设计的 TIME 类型，不光表示一天之内的时间，而且可以用来表示一个时间间隔，这个时间间隔可以超过 24 小时。

最后，汇总一下上面提到的SQL语句：

-- 修改字段类型语句
alter table demo.goodsmaster
modify column price double;

-- 计算字段合计函数
select sum(price) from demo.goodsmaster;

再分享 1 个小技巧:

在定义数据类型时，如果确定是整数，就用 INT；如果是小数，一定用定点数类型 DECIMAL；如果是字符串，只要不是主键，就用 TEXT；如果是日期与时间，就用 DATETIME。

• 整数：INT
• 小数：DECIMAL
• 字符串：TEXT
• 日期与时间：DATETIME

这样做的好处是，首先确保你的系统不会因为数据类型定义出错。

凡事都是有两面的，可靠性好，并不意味着高效。比如，TEXT 虽然使用方便，但是效率不如 CHAR(M) 和 VARCHAR(M)。

文章参考：朱晓峰·MySQL必知必会