MySQL必知必会05：正确设置主键

您所在的位置：网站首页 › sql怎么设主键 › MySQL必知必会05：正确设置主键

MySQL必知必会05：正确设置主键

2023-12-26 19:43| 来源: 网络整理| 查看: 265

阅读整理自《MySQL 必知必会》- 朱晓峰，详细内容请登录极客时间官网购买专栏。

文章目录业务字段做主键使用自增字段做主键手动赋值字段做主键小结在一个项目中，客户要进行会员营销，相应的，就需要处理会员信息。会员信息表（demo.membermaster）的设计大体如下，为了能够唯一地标识一个会员的信息，需要为会员信息表设置一个主键。怎么为这个表设置主键，才能达到理想的目标？

cardno（卡号）membername（名称）memberphone（电话）memberpid（身份证号）address（地址）sex（性别）birthday（生日）10000001张三18758079160110123200001017890北京男2000-01-01 00:00:0010000002李四18758079161123123199001012356上海女1990-01-01 00:00:00

三种设置主键的思路：业务字段做主键、自增字段做主键和手动赋值字段做主键。

业务字段做主键

会员卡号（cardno）看起来比较合适，因为会员卡号不能为空，而且有唯一性，可以用来标识一条会员记录。

会员卡号做主键有没有什么问题？插入 2 条数据来验证下：

mysql> insert into demo.membermaster -> (cardno, membername, memberphone, memberpid, memberaddress, sex, birthday) -> values -> ('10000001', '张三', '18758079160', '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','李四','18758079161', '123123199001012356', '上海', '女', '1990-01-01'); Query OK, 1 row affected (0.01 sec) mysql> select * from demo.membermaster; +----------+------------+-------------+--------------------+---------------+------+---------------------+ | cardno | membername | memberphone | memberpid | memberaddress | sex | birthday | +----------+------------+-------------+--------------------+---------------+------+---------------------+ | 10000001 | 张三 | 18758079160 | 110123200001017890 | 北京 | 男 | 2000-01-01 00:00:00 | | 10000002 | 李四 | 18758079161 | 123123199001012356 | 上海 | 女 | 1990-01-01 00:00:00 | +----------+------------+-------------+--------------------+---------------+------+---------------------+ 2 rows in set (0.00 sec)

不同的会员卡号对应不同的会员，字段“cardno”唯一地标识某一个会员。

实际情况是，存在“cardno”无法唯一识别某一个会员的问题。因为会员卡号存在重复使用的情况。比如，张三不再到商家的门店消费了（退还了会员卡），于是张三就不再是这个商家门店的会员了。商家不想让这个会员卡空着，就把卡号是“10000001”的会员卡发给了王五。

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

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

transactionno（流水单号）itemnumber（商品编号）quantity（销售数量）price（价格）salesvalue（销售金额）cardno（会员卡号）transdate2020-12-011118989100000012020-12-01

创建销售流水表：

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.05 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.goodname, 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 | goodname | quantity | salesvalue | transdate | +------------+----------+----------+------------+---------------------+ | 张三 | book | 1.000 | 89.00 | 2020-12-01 00:00:00 | +------------+----------+----------+------------+---------------------+

假设会员卡 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.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from demo.membermaster; +----------+------------+-------------+--------------------+---------------+------+---------------------+ | cardno | membername | memberphone | memberpid | memberaddress | sex | birthday | +----------+------------+-------------+--------------------+---------------+------+---------------------+ | 10000001 | 王五 | 13698765432 | 475145197001012356 | 天津 | 女 | 1970-01-01 00:00:00 | | 10000002 | 李四 | 18758079161 | 123123199001012356 | 上海 | 女 | 1990-01-01 00:00:00 | +----------+------------+-------------+--------------------+---------------+------+---------------------+ 2 rows in set (0.00 sec)

再次运行之前的会员消费流水查询：

mysql> select b.membername, c.goodname, 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 | goodname | quantity | salesvalue | transdate | +------------+----------+----------+------------+---------------------+ | 王五 | book | 1.000 | 89.00 | 2020-12-01 00:00:00 | +------------+----------+----------+------------+---------------------+ 1 row in set (0.00 sec)

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

所以，千万不能把会员卡号当做主键。实际情况下，不管是会员电话号码、身份证号都不适合作为主键。

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

使用自增字段做主键

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

修改一下会员信息表的结构，添加一个自增字段做主键。

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

mysql> alter table demo.membermaster -> drop primary key; Query OK, 2 rows affected (0.15 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.09 sec) Records: 0 Duplicates: 0 Warnings: 0

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

mysql> alter table demo.trans -> add memberid int; Query OK, 0 rows affected (0.03 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

demo.membermaster 和 demo.trans 的结构：

验证前，先将表中的 cardno 为 10000001 的用户改回张三：

mysql> select * from demo.membermaster; +----------+------------+-------------+--------------------+---------------+------+---------------------+----+ | cardno | membername | memberphone | memberpid | memberaddress | sex | birthday | id | +----------+------------+-------------+--------------------+---------------+------+---------------------+----+ | 10000001 | 张三 | 18758079160 | 110123200001017890 | 北京 | 男 | 2000-01-01 00:00:00 | 1 | | 10000002 | 李四 | 18758079161 | 123123199001012356 | 上海 | 女 | 1990-01-01 00:00:00 | 2 | +----------+------------+-------------+--------------------+---------------+------+---------------------+----+

如果张三的会员卡 10000001 不再使用，发给了王五，就在会员信息表里面增加一条记录：

mysql> insert into demo.membermaster -> (cardno, membername, memberphone, memberpid, memberaddress, sex, birthday) -> values -> ('10000001', '王五', '13698765432', '475145197001012356', '南京', '男', '1992-02-02'); Query OK, 1 row affected (0.00 sec)

查看所有会员信息：

mysql> select * from demo.membermaster; +----------+------------+-------------+--------------------+---------------+------+---------------------+----+ | cardno | membername | memberphone | memberpid | memberaddress | sex | birthday | id | +----------+------------+-------------+--------------------+---------------+------+---------------------+----+ | 10000001 | 张三 | 18758079160 | 110123200001017890 | 北京 | 男 | 2000-01-01 00:00:00 | 1 | | 10000002 | 李四 | 18758079161 | 123123199001012356 | 上海 | 女 | 1990-01-01 00:00:00 | 2 | | 10000001 | 王五 | 13698765432 | 475145197001012356 | 南京 | 男 | 1992-02-02 00:00:00 | 3 | +----------+------------+-------------+--------------------+---------------+------+---------------------+----+ 3 rows in set (0.00 sec)

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

现在再来查会员消费，就不会出问题了：

mysql> select b.membername, c.goodname, 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 | goodname | quantity | salesvalue | transdate | +------------+----------+----------+------------+---------------------+ | 张三 | book | 1.000 | 89.00 | 2020-12-01 00:00:00 | +------------+----------+----------+------------+---------------------+ 1 row in set (0.00 sec) 手动赋值字段做主键

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

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

门店在添加会员的时候，先到总部 MySQL 数据库中获取这个最大值，在这个基础上加 1，然后用这个值作为新会员的“id”，同时，更新总部 MySQL 数据库管理信息表中的当前会员编号的最大值。这样一来，各个门店添加会员的时候，都对同一个总部 MySQL 数据库中的数据表字段进行操作，就解决了各门店添加会员时会员编号冲突的问题，同时也避免了使用业务字段导致数据错误的问题。

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

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

给已有的字段增加主键：

alter table demo.importhead modify listnumber int primary key;

mysql> describe demo.importhead; +----------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------+------+-----+---------+-------+ | listnumber | int | YES | | NULL | | | supplierid | int | YES | | NULL | | | stocknumber | int | YES | | NULL | | | importtype | int | YES | | 1 | | | importquantity | decimal(10,3) | YES | | NULL | | | importvalue | decimal(10,2) | YES | | NULL | | | recorder | int | YES | | NULL | | | recordingdate | datetime | YES | | NULL | | +----------------+---------------+------+-----+---------+-------+ 8 rows in set (0.00 sec) mysql> alter table demo.importhead modify listnumber int primary key; Query OK, 0 rows affected (0.15 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> describe demo.importhead; +----------------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+---------------+------+-----+---------+-------+ | listnumber | int | NO | PRI | NULL | | | supplierid | int | YES | | NULL | | | stocknumber | int | YES | | NULL | | | importtype | int | YES | | 1 | | | importquantity | decimal(10,3) | YES | | NULL | | | importvalue | decimal(10,2) | YES | | NULL | | | recorder | int | YES | | NULL | | | recordingdate | datetime | YES | | NULL | | +----------------+---------------+------+-----+---------+-------+ 8 rows in set (0.01 sec)

【本文地址】

公司简介

联系我们