MySQL 必知必会(7)主键:如何正确设置主键?

2022年11月9日 45点热度

主键:如何正确设置主键

今天我们来聊一聊如何用好 MySQL 的主键。

前面在讲存储的时候,我提到过主键,它可以唯一标识表中的某一条记录,对数据表来说非常重要。当我们需要查询和引用表中的一条记录的时候,最好的办法就是通过主键。只有合理地设置主键,才能确保我们准确、快速地找到所需要的数据记录。今天我就借助咱们的超市项目的实际需求,来给你讲一讲怎么正确设置主键。

在我们的项目中,客户要进行会员营销,相应的,我们就需要处理会员信息。会员信息表(demo.membermaster)的设计大体如下:

会员信息表

为了能够唯一地标识一个会员的信息,我们需要为会员信息表设置一个主键。那么,怎么为这个表设置主键,才能达到我们理想的目标呢?

天,我就带你在解决这个实际问题的过程中,学习下三种设置主键的思路:业务字段做主键、自增字段做主键和手动赋值字段做主键

业务字段做主键

针对这个需求,我们最容易想到的,是选择表中已有的字段,也就是跟业务相关的字段做主键。那么,在这个表里,哪个字段比较合适呢?我们来分析一下。

会员卡号(cardno)看起来比较合适,因为会员卡号不能为空,而且有唯一性,可以用来标识一条会员记录。我们来尝试一下用会员卡号做主键。

我们可以用下面的代码,在创建表的时候,设置字段 cardno 为主键:

mysql> CREATE TABLE demo.membermaster
    -> (
    -> cardno CHAR(8) PRIMARY KEY, -- 会员卡号为主键
    -> membername TEXT,
    -> memberphone TEXT,
    -> memberpid TEXT,
    -> memberaddress TEXT,
    -> sex TEXT,
    -> birthday DATETIME
    -> );
Query OK, 0 rows affected (0.06 sec)

我们来查询一下表的结构,确认下主键是否创建成功了:

mysql> DESCRIBE demo.membermaster;
+---------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+-------+
| cardno | char(8) | NO | PRI | NULL | |
| membername | text | YES | | NULL | |
| memberphone | text | YES | | NULL | |
| memberpid | text | YES | | NULL | |
| memberaddress | text | YES | | NULL | |
| sex | text | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
+---------------+----------+------+-----+---------+-------+
7 rows in set (0.02 sec)

可以看到,字段 cardno 在表示键值的 key 这一列的值是“PRI”,意思是 PRIMARY KEY,这就表示它已经被设置成主键了。这里需要注意的一点是,除了字段 cardno,所有的字段都允许为空。这是因为,这些信息有可能当时不知道,要稍后补齐。

会员卡号做主键有没有什么问题呢?我们插入 2 条数据来验证下:

mysql> INSERT INTO demo.membermaster
    -> (
    -> cardno,
    -> membername,
    -> memberphone,
    -> memberpid,
    -> memberaddress,
    -> sex,
    -> birthday
    -> )
    -> VALUES
    -> (
    -> '10000001',
    -> '张三',
    -> '13812345678',
    -> '110123200001017890',
    -> '北京',
    -> '男',
    -> '2000-01-01'
    -> );
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO demo.membermaster
    -> (
    -> cardno,
    -> membername,
    -> memberphone,
    -> memberpid,
    -> memberaddress,
    -> sex,
    -> birthday
    -> )
    -> VALUES
    -> (
    -> '10000002',
    -> '李四',
    -> '13512345678',
    -> '123123199001012356',
    -> '上海',
    -> '女',
    -> '1990-01-01'
    -> );
Query OK, 1 row affected (0.01 sec)

插入成功后,我们来看一下表的内容:

mysql> SELECT * FROM demo.membermaster;
+----------+------------+-------------+--------------------+---------------+--
| cardno | membername | memberphone | memberpid | memberaddress | s
+----------+------------+-------------+--------------------+---------------+--
| 10000001 | 张三 | 13812345678 | 110123200001017890 | 北京 | 男
| 10000002 | 李四 | 13512345678 | 123123199001012356 | 上海 | 女
+----------+------------+-------------+--------------------+---------------+--
2 rows in set (0.00 sec)

我们发现,不同的会员卡号对应不同的会员,字段“cardno”唯一地标识某一个会员。如果都是这样,会员卡号与会员一一对应,系统是可以正常运行的。

但是实际情况是,上线不到一周,就发生了“cardno”无法唯一识别某一个会员的问题。原来,会员卡号存在重复使用的情况。

这也很好理解,比如,张三因为工作变动搬离了原来的地址,不再到商家的门店消费了(退还了会员卡),于是张三就不再是这个商家门店的会员了。但是,商家不想让这个会员卡空着,就把卡号是“10000001”的会员卡发给了王五。

从系统设计的角度看,这个变化只是修改了会员信息表中的卡号是“10000001”这个会员信息,并不会影响到数据一致性。也就是说,修改会员卡号是“10000001”的会员信息,系统的各个模块,都会获取到修改后的会员信息,不会出现“有的模块获取到修改之前的会员信息,有的模块获取到修改后的会员信息,而导致系统内部数据不一致”的情况。因此,从信息系统层面上看是没问题的。但是从使用系统的业务层面来看,就有很大的问题了,会对商家造成影响。

下面,我们就来看看这种修改,是如何影响到商家的。

比如,我们有一个销售流水表,记录了所有的销售流水明细。2020 年 12 月 01 日,张三在门店购买了一本书,消费了 89 元。那么,系统中就有了张三买书的流水记录,如下所示:

流水记录

我们可以用下面的代码创建销售流水表。因为需要引用会员信息和商品信息,所以表中要包括商品编号字段和会员卡号字段。

mysql> CREATE table demo.trans
    -> (
    -> transactionno INT,
    -> itemnumber INT, -- 为了引用商品信息
    -> quantity DECIMAL(10,3),
    -> price DECIMAL(10,2),
    -> salesvalue DECIMAL(10,2),
    -> cardno CHAR(8), -- 为了引用会员信息
    -> transdate DATETIME
    -> );
Query OK, 0 rows affected (0.10 sec)

创建好表以后,我们来插入一条销售流水:

mysql> INSERT INTO demo.trans
    -> (
    -> transactionno,
    -> itemnumber,
    -> quantity,
    -> price,
    -> salesvalue,
    -> cardno,
    -> transdate
    -> )
    -> VALUES
    -> (
    -> 1,
    -> 1,
    -> 1,
    -> 89,
    -> 89,
    -> '10000001',
    -> '2020-12-01'
    -> );
Query OK, 1 row affected (0.01 sec)

接着,我们查询一下 2020 年 12 月 01 日的会员销售记录:

mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
    -> FROM demo.trans AS a
    -> JOIN demo.membermaster AS b
    -> JOIN demo.goodsmaster AS c
    -> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber);
+------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate |
+------------+-----------+----------+------------+---------------------+
| 张三 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.00 sec)

我们得到的查询结果是:张三,在 2020 年 12 月 01 日买了一本书,花了 89 元。

需要注意的是,这里我用到了 JOIN,也就是表的关联,目的是为了引用其他表的信息,包括会员信息表(demo.membermaster)和商品信息表(demo.goodsmaster)。有关关联表查询的具体细节,我会在下节课讲到,这里你只要知道,通过关联查询,可以从会员信息表中获取会员信息,从商品信息表中获取商品信息,就可以了。

下面,我们假设会员卡“10000001”又发给了王五,我们需要更改会员信息表:

mysql> UPDATE demo.membermaster
    -> SET membername = '王五',
    -> memberphone = '13698765432',
    -> memberpid = '475145197001012356',
    -> memberaddress='天津',
    -> sex='女',
    -> birthday = '1970-01-01'
    -> WHERE cardno = '10000001';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0

会员记录改好了,我们再次运行之前的会员消费流水查询:

mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
    -> FROM demo.trans AS a
    -> JOIN demo.membermaster AS b
    -> JOIN demo.goodsmaster AS c
    -> ON (a.cardno = b.cardno AND a.itemnumber=c.itemnumber);
+------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate |
+------------+-----------+----------+------------+---------------------+
| 王五 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.01 sec)

这次得到的结果是:王五在 2020 年 12 月 01 日,买了一本书,消费 89 元。

很明显,这个结果把张三的消费行为放到王五身上去了,肯定是不对的。这里的原因就是,我们把会员卡号是“10000001”的会员信息改了,而会员卡号是主键,会员消费查询通过会员卡号关联到会员信息,得到了完全错误的结果。

现在你知道了吧,千万不能把会员卡号当做主键。

那么,会员电话可以做主键吗?不行的。在实际操作中,手机号也存在被运营商收回,重新发给别人用的情况。

那身份证号行不行呢?好像可以。因为身份证决不会重复,身份证号与一个人存在一一对应的关系。可问题是,身份证号属于个人隐私,顾客不一定愿意给你。对门店来说,顾客就是上帝,要是强制要求会员必须登记身份证号,会把很多客人赶跑的。其实,客户电话也有这个问题,这也是我们在设计会员信息表的时候,允许身份证号和电话都为空的原因。

这样看来,任何一个现有的字段都不适合做主键。

所以,我建议你尽量不要用业务字段,也就是跟业务有关的字段做主键。毕竟,作为项目设计的技术人员,我们谁也无法预测在项目的整个生命周期中,哪个业务字段会因为项目的业务需求而有重复,或者重用之类的情况出现。

既然业务字段不可以,那我们再来试试自增字段。

使用自增字段做主键

我们来给会员信息表添加一个字段,比如叫 id,给这个字段定义自增约束,这样,我们就有了一个具备唯一性的,而且不为空的字段来做主键了。

接下来,我们就来修改一下会员信息表的结构,添加一个自增字段做主键。

第一步,修改会员信息表,删除表的主键约束,这样,原来的主键字段,就不再是主键了。不过需要注意的是,删除主键约束,并不会删除字段。

mysql> ALTER TABLE demo.membermaster
    -> DROP PRIMARY KEY;
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0

第二步,修改会员信息表,添加字段“id”为主键,并且给它定义自增约束:

mysql> ALTER TABLE demo.membermaster
    -> ADD id INT PRIMARY KEY AUTO_INCREMENT;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

第三步,修改销售流水表,添加新的字段 memberid,对应会员信息表中的主键:

mysql> ALTER TABLE demo.trans
    -> ADD memberid INT;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

第四步,我们更新一下销售流水表,给新添加的字段“memberid”赋值,让它指向对应的会员信息:

mysql> UPDATE demo.trans AS a,demo.membermaster AS b
    -> SET a.memberid=b.id
    -> WHERE a.transactionno > 0
    -> AND a.cardno = b.cardno; -- 这样操作可以不用删除trans的内容,在实际工作中更适合
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

这个更新语句包含了 2 个关联的表,看上去有点复杂。其实,你完全可以通过删除表 demo.trans、重建表,再插入一条数据的操作,来达到同样的目的,但是我不建议你这么做。

在实际操作中,你不一定能删掉 demo.trans 这个表,因为这个表里面可能已经有了很多重要的数据。所以,你一定要认真学习一下我给你介绍的这个更新数据的方法,这种复杂一点的更新语句在实战中更有用。

好了,到这里,我们就完成了数据表的重新设计,让我们看一下新的数据表 demo.membermaster 和 demo.trans 的结构:

mysql> DESCRIBE demo.membermaster;
+---------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+----------+------+-----+---------+----------------+
| cardno | char(8) | NO | | NULL | |
| membername | text | YES | | NULL | |
| memberphone | text | YES | | NULL | |
| memberpid | text | YES | | NULL | |
| memberaddress | text | YES | | NULL | |
| sex | text | YES | | NULL | |
| birthday | datetime | YES | | NULL | |
| id | int | NO | PRI | NULL | auto_increment |
+---------------+----------+------+-----+---------+----------------+
8 rows in set (0.02 sec)

mysql> DESCRIBE demo.trans;
+---------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| transactionno | int | NO | PRI | NULL | |
| itemnumber | int | YES | | NULL | |
| quantity | decimal(10,3) | YES | | NULL | |
| price | decimal(10,2) | YES | | NULL | |
| salesvalue | decimal(10,2) | YES | | NULL | |
| cardno | char(8) | YES | | NULL | |
| transdate | datetime | YES | | NULL | |
| memberid | int | YES | | NULL | |
+---------------+---------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

现在,如果我们再次面对卡号重用的情况,该如何应对呢(这里我们假设回到修改会员卡 10000001 为王五之前的状态)?

如果张三的会员卡“10000001”不再使用,发给了王五,我们就在会员信息表里面增加一条记录:

mysql> INSERT INTO demo.membermaster
    -> (
    -> cardno,
    -> membername,
    -> memberphone,
    -> memberpid,
    -> memberaddress,
    -> sex,
    -> birthday
    -> )
    -> VALUES
    -> (
    -> '10000001',
    -> '王五',
    -> '13698765432',
    -> '475145197001012356',
    -> '天津',
    -> '女',
    -> '1970-01-01'
    -> );
Query OK, 1 row affected (0.02 sec)

下面我们看看现在的会员信息:

mysql> SELECT *
    -> FROM demo.membermaster;
+----------+------------+-------------+--------------------+---------------+--
| cardno | membername | memberphone | memberpid | memberaddress | s
+----------+------------+-------------+--------------------+---------------+--
| 10000001 | 张三 | 13812345678 | 110123200001017890 | 北京 | 男
| 10000002 | 李四 | 13512345678 | 123123199001012356 | 上海 | 女
| 10000001 | 王五 | 13698765432 | 475145197001012356 | 天津 | 女
+----------+------------+-------------+--------------------+---------------+--
3 rows in set (0.00 sec)

由于字段“cardno”不再是主键,可以允许重复,因此,我们可以在保留会员“张三”信息的同时,添加使用同一会员卡号的“王五”的信息。

现在再来查会员消费,就不会出问题了:

mysql> SELECT b.membername,c.goodsname,a.quantity,a.salesvalue,a.transdate
    -> FROM demo.trans AS a
    -> JOIN demo.membermaster AS b
    -> JOIN demo.goodsmaster AS c
    -> ON (a.memberid = b.id AND a.itemnumber=c.itemnumber);
+------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate |
+------------+-----------+----------+------------+---------------------+
| 张三 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.01 sec)

可以看到,结果是 2020 年 12 月 01 日,张三买了一本书,消费 89 元,是正确的。

如果是一个小项目,只有一个 MySQL 数据库服务器,用添加自增字段作为主键的办法是可以的。不过,这并不意味着,在任何情况下你都可以这么做。

举个例子,用户要求把增加新会员的工作放到门店进行,因为发展新会员的工作一般是在门店进行的,毕竟,人们一般都是在购物的同时申请会员。解决的办法是,门店的信息系统添加新增会员的功能,把新的会员信息先存放到本地 MySQL 数据库中,再上传到总部,进行汇总。

可是问题来了,如果会员信息表的主键是自增的,那么各个门店新加的会员就会出现“id”冲突的可能。

比如,A 店的 MySQL 数据库中的 demo.membermaster 中,字段“id”的值是 100,这个时候,新增了一个会员,“id”是 101。同时,B 店的字段“id”值也是 100,要加一个新会员,“id”也是 101,毕竟,B 店的 MySQL 数据库与 A 店相互独立。等 A 店与 B 店都把新的会员上传到总部之后,就会出现两个“id”是 101,但却是不同会员的情况,这该如何处理呢?

手动赋值字段做主键

为了解决这个问题,我们想了一个办法:取消字段“id”的自增属性,改成信息系统在添加会员的时候对“id”进行赋值。

具体的操作是这样的:在总部 MySQL 数据库中,有一个管理信息表,里面的信息包括成本核算策略,支付方式等,还有总部的系统参数,我们可以在这个表中添加一个字段,专门用来记录当前会员编号的最大值。

门店在添加会员的时候,先到总部 MySQL 数据库中获取这个最大值,在这个基础上加 1,然后用这个值作为新会员的“id”,同时,更新总部 MySQL 数据库管理信息表中的当前会员编号的最大值。

这样一来,各个门店添加会员的时候,都对同一个总部 MySQL 数据库中的数据表字段进行操作,就解决了各门店添加会员时会员编号冲突的问题,同时也避免了使用业务字段导致数据错误的问题。

总结

今天,我给你介绍了设置数据表主键的三种方式:数据表的业务字段做主键、添加自增字段做主键,以及添加手动赋值字段做主键。

  • 用业务字段做主键,看起来很简单,但是我们应该尽量避免这样做。因为我们无法预测未来会不会因为业务需要,而出现业务字段重复或者重用的情况。
  • 自增字段做主键,对于单机系统来说是没问题的。但是,如果有多台服务器,各自都可以录入数据,那就不一定适用了。因为如果每台机器各自产生的数据需要合并,就可能会出现主键重复的问题。
  • 我们可以采用手动赋值的办法,通过一定的逻辑,确保字段值在全系统的唯一性,这样就可以规避主键重复的问题了。

刚开始使用 MySQL 时,很多人都很容易犯的错误是喜欢用业务字段做主键,想当然地认为了解业务需求,但实际情况往往出乎意料,而更改主键设置的成本非常高。所以,如果你的系统比较复杂,尽量给表加一个字段做主键,采用手动赋值的办法,虽然系统开发的时候麻烦一点,却可以避免后面出大问题。

思考题

在刚刚的例子中,如果我想把销售流水表 demo.trans 中,所有单位是“包”的商品的价格改成原来价格的 80%,该怎么实现呢?

Baimi

世上只有两种编程语言:一种是总是被人骂的,一种是从来没人用的。