设置主键
主键可以唯一标识表中的某一条记录，对数据表来说非常重要。
当我们需要查询和引用表中的一条记录时，最好的办法就是通过主键。只有合理地设置主键，才能确保我们准确、快速地找到所需要的数据记录。
在我们的项目中，客户要进行会员营销，相应的，我们就需要处理会员信息。会员信息表（demo.membermaster）表结构如下：
为了能够唯一标识会员信息，我们需要为会员信息表设置一个主键。那么，应该如何设置主键，才可以达到我们理想的目标呢？
今天我们来学习三种设置主键的思路：业务字段做主键、自增字段做主键、手动赋值字段做主键。
业务字段做主键
针对这个需求，最容易想到的，就是选择表中已有字段，也就是跟业务相关的字段做主键。
在这个表中，会员卡号（cardno）看起来比较合适，因为会员卡号不能为空且具有唯一性，可以用来标识一条会员记录。
我们可以用下面的代码，在创建表的时候，设置字段 cardno 作为主键：
CREATE TABLE
demo.membermaster (
-- 会员卡号为主键
cardno CHAR(8) PRIMARY KEY,
membername TEXT,
memberphone TEXT,
memberpid TEXT,
memberaddress TEXT,
sex TEXT,
birthday DATETIME
);
然后我们来查询一下表结构，确认下主键是否创建成功了：
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.01 sec)
可以看到，字段 cardno 在表示键值的 Key 这一系列的值是 ”PRI“，意思是 PRIMARY KEY，这就表示它已经被设置成主键了。
会员卡号做主键会有什么问题嘛？我们插入 2 条数据来验证下：
INSERT INTO
demo.membermaster (
cardno,
membername,
memberphone,
memberpid,
memberaddress,
sex,
birthday
)
VALUES (
'10000001',
'张三',
'15928792771',
'110123200001017890',
'济南',
'男',
'2000-01-01'
), (
'10000002',
'李四',
'13578271231',
'123123199001012356',
'北京',
'女',
'1990-01-01'
);
插入成功后，我们再来看下表的内容：
SELECT * FROM demo.membermaster;
mysql> SELECT * FROM demo.membermaster;
+----------+------------+-------------+--------------------+---------------+------+---------------------+
| cardno | membername | memberphone | memberpid | memberaddress | sex | birthday |
+----------+------------+-------------+--------------------+---------------+------+---------------------+
| 10000001 | 张三 | 15928792771 | 110123200001017890 | 济南 | 男 | 2000-01-01 00:00:00 |
| 10000002 | 李四 | 13578271231 | 123123199001012356 | 北京 | 女 | 1990-01-01 00:00:00 |
+----------+------------+-------------+--------------------+---------------+------+---------------------+
2 rows in set (0.00 sec)
可以发现，不同的会员卡号对应不同的会员，字段 ”cardno“ 唯一地标识某一个会员。如果都是这样，会员卡号与会员一一对应，系统是可以正常运行的。
但是实际情况并没有这么简单，会员卡号存在重复使用的情况。比如，张三因为工作变动搬离原来的地址，不再到商家的门店消费（退还会员卡），于是张三就不再是这个商店门店的会员了。但是，商家不想让这个会员卡空着，就把卡号是 ”10000001“ 的会员卡发给王五。
从系统设计的角度来看，这个变化只是修改会员信息表中的卡号为 ”10000001“ 的会员信息，但不会影响到数据一致性。也就是说，修改会员卡号是 ”10000001“ 的会员信息，系统的各个模块都会获取到修改后的会员信息。因此，从信息系统层面上看是没有问题的。但是从使用系统的业务层面来看，就有很大的问题了，会对商家造成影响。
下面，我们就来看看这种修改，是如何影响到商家的。
比如，我们有一个销售流水表，记录了所有的销售流水明细。2020 年 12 月 01 日，张三在门店购买一本书，消费 89 元。那么，系统中就有了张三买书的记录，如下所示：
我们可以用下面的代码创建销售流水表。因为需要引用会员信息和商品信息，所以表中要包括商品编号字段和会员卡号字段。
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
);
创建好表之后，我们就来插入一条销售流水：
INSERT INTO
demo.trans (
transactionno,
itemnumber,
quantity,
price,
salesvalue,
cardno,
transdate
)
VALUES (
1,
1,
1,
89,
89,
'10000001',
'2023-10-10'
);
接着，我们再来查看一下 2023 年 10 月 10 日的会员销售记录。
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
);
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 | 2023-10-10 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.00 sec)
我们可以得到查询结果：张三，在 2023 年 10 月 10 日买了一本书，花了 89 元。
这里我们用到了 JOIN，也就是表的关联，目的就是为了引用其他表的信息，包括会员信息表（demo.membermaster）和商品信息表（demo.goodsmater）。通过关联查询，我们就可以从会员信息表中获取会员信息，从商品信息表获取商品信息。
下面，我们假设会员卡 ”10000001“ 又发给王五，我们需要更改会员信息表。
UPDATE demo.membermaster
SET
membername = '王五',
memberphone = '13798293042',
memberpid = '475145197001012356',
memberaddress = '天津',
sex = '女',
birthday = '1970-01-01'
WHERE cardno = '10000001';
会员记录修改后之后，我们再次运行之前的会员消费流水查询：
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 | 2023-10-10 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.00 sec)
这次得到的结果是：王五在 2023 年 10 月 10 日买了一本书，消费 89 元。
很明显，这个结果把张三的消费行为放到王五身上了，肯定是不对的。原因就是，我们将会员卡号是 “10000001” 对应的会员信息改了，而会员卡号是主键，会员消费查询通过会员卡号关联到会员信息，最终得到错误的结果。
现在你已经知道，为什么不能把会员卡号当作主键。另外，会员电话也不能做主键，在实际操作中，手机号也存在被运营商收回，重新发给别人用的情况。
同理身份证号也不行。虽然身份证号不会重复，与每个人存在一一对应的关系。但是，身份证号属于个人隐私，顾客不一定会提供。对门店来说，顾客就是上帝，要是强制会员必须登记身份证号，会流失很多客户。另外，客户电话也有同样的问题。
这样看来，任何一个现有字段都不适合做主键。所以，建议你尽量不要使用与业务有关的字段做主键。作为项目设计的技术人员，我们无法预测在项目的整个生命周期中，哪个业务字段会因为项目的业务需求存在重复或者重用之类的情况出现。
既然业务字段不可以，那我们再来试试自增字段。
自增字段做主键
我们再给会员信息表添加一个字段，比如叫 id，然后我们给这个字段定义自增约束，这样，我们就具备唯一性的，而且不为空的字段来做主键了。
接下来，我们来修改会员信息表的结构，添加一个自增字段做主键。
第一步，修改会员信息表，删除表的主键约束（删除主键约束，并不会删除字段）。
ALTER TABLE demo.membermaster DROP PRIMARY KEY;
第二步，修改会员信息表，添加字段 “id” 为主键，并且给它定义自增约束：
ALTER TABLE demo.membermaster ADD id INT PRIMARY KEY AUTO_INCREMENT;
第三步，修改销售流水表，添加新的字段 memberid，对应会员信息表中的主键：
ALTER TABLE demo.trans ADD memberid INT;
第四步，更新一下销售流水表，给新添加的字段 “memberid” 赋值，让它指向对应的会员信息：
UPDATE
demo.trans AS a,
demo.membermaster AS b
SET a.memberid = b.id
WHERE
a.transactionno > 0
AND a.cardno = b.cardno;
这个更新语句包含 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.00 sec)
mysql> DESCRIBE demo.trans;
+---------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| transactionno | int | YES | | 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 为张三的状态。
UPDATE demo.membermaster
SET
membername = '张三',
memberphone = '15928792771',
memberpid = '110123200001017890',
memberaddress = '济南',
sex = '男',
birthday = '2000-01-01 00:00:00'
WHERE cardno = '10000001';
现在，如果我们再次面对卡号重用的情况，该如何应对呢？
如果张三的会员卡 “10000001” 不再使用，发给王五，我们可以在会员信息表中增加一条记录：
INSERT INTO
demo.membermaster (
cardno,
membername,
memberphone,
memberpid,
memberaddress,
sex,
birthday
)
VALUES (
'10000001',
'王五',
'13698765432',
'475145197001012356',
'天津',
'女',
'1970-01-01'
);
下面我们再来看现在的会员信息表：
mysql> SELECT * FROM demo.membermaster;
+----------+------------+-------------+--------------------+---------------+------+---------------------+----+
| cardno | membername | memberphone | memberpid | memberaddress | sex | birthday | id |
+----------+------------+-------------+--------------------+---------------+------+---------------------+----+
| 10000001 | 张三 | 15928792771 | 110123200001017890 | 济南 | 男 | 2000-01-01 00:00:00 | 1 |
| 10000002 | 李四 | 13578271231 | 123123199001012356 | 北京 | 女 | 1990-01-01 00:00:00 | 2 |
| 10000001 | 王五 | 13698765432 | 475145197001012356 | 天津 | 女 | 1970-01-01 00:00:00 | 3 |
+----------+------------+-------------+--------------------+---------------+------+---------------------+----+
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 | 2023-10-10 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.00 sec)
可以看到，结果是 2023 年 10 月 10 日，张三买了一本书，消费 89 元，是正确的。
如果是一个小项目，只有一个 MySQL 数据库服务器，用添加自增字段作为主键的办法是可以的。不过，这并不意味，在任何情况下都可以这么做。
举个例子，用户要求把增加新会员的工作放到门店进行（发展新会员一般在门店进行，人们通常在购物的同时申请会员）。解决的办法是，门店的信息系统新增会员的功能，把新的会员信息先存放到本地 MySQL 数据库中，再上传到总部，进行汇总（分布式系统中的汇总重复问题）。
那么问题来了，如果会员信息表的主键是自增的，那么各个门店新加的会员就会出现“id”冲突的可能。那这种情况应该如何处理呢？
手动赋值字段做主键
要想解决这个问题，我们可以取消字段 “id" 的自增属性，改成信息系统在添加会员的时候对 ”id“ 进行赋值。
具体可以这样操作：在总部 MySQL 数据库中，有一个管理信息表，里面的信息包括成本核算策略，支付方式等，还有总部的系统参数，我们可以在这个表中添加一个字段，专门用来记录当前会员编号的最大值。
店在添加会员的时候，先到总部 MySQL 数据库中获取这个最大值，在这个基础上加 1，然后用这个值作为新会员的“id”，同时，更新总部 MySQL 数据库管理信息表中的当前会员编号的最大值。
这样一来，各个门店添加会员的时候，都对同一个总部 MySQL 数据库中的数据表字段进行操作，就解决了各门店添加会员时会员编号冲突的问题，同时也避免了使用业务字段导致数据错误的问题。
总结
今天，我们学习了设置数据表主键的三种方式：数据表的业务字段做主键、添加自增字段做主键，以及添加手动赋值字段做主键。
- 用业务字段做主键，看起来很简单，但是我们应该尽量避免这样做。因为我们无法预测未来会不会因为业务需要，而出现业务字段重复或者重用的情况。
- 自增字段做主键，对于单机系统来说是没问题的。但是，如果有多台服务器，各自都可以录入数据，那就不一定适用了。因为如果每台机器各自产生的数据需要合并，就可能会出现主键重复的问题。
- 我们可以采用手动赋值的办法，通过一定的逻辑，确保字段值在全系统的唯一性，这样就可以规避主键重复的问题了。
刚开始使用 MySQL 时，很多人都很容易犯的错误是喜欢用业务字段做主键，想当然地认为了解业务需求，但实际情况往往出乎意料，而更改主键设置的成本非常高。所以，如果你的系统比较复杂，尽量给表加一个字段做主键，采用手动赋值的办法，虽然系统开发的时候麻烦一点，却可以避免后面出大问题。