MySQL必知必会 05丨主键:如何正确设置主键?
主键可以唯一 标识表中的某一条记录,对数据表来说非常重要。当我们需要查询和引用表中的一条记录的时候,最好的办法就是通过主键 。只有合理地设置主键,才能确保我们准确、快速地找到所需要的数据记录。
在项目中,客户要进行会员营销,相应的,我们就需要处理会员信息。会员信息表(demo.membermaster)的设计大体如下:
为了能够唯一地标识一个会员的信息,我们需要为会员信息表设置一个主键。那么,怎么为这个表设置主键,才能达到我们理想的目标呢?今天,就学习下三种设置主键的思路:业务字段做主键、自增字段做主键和手动赋值字段做主键。
业务字段做主键
针对这个需求,我们最容易想到的,是选择表中已有的字段,也就是跟业务相关的字段做主键。会员卡号(cardno)看起来比较合适,因为会员卡号不能为空,而且有唯一性,可以用来标识一条会员记录。我们来尝试一下用会员卡号做主键。
我们可以用下面的代码,在创建表的时候,设置字段 cardno 为主键:
1 2 3 4 5 6 7 8 9 10 CREATE TABLE demo.membermaster( cardno CHAR (8 ) PRIMARY KEY, membername TEXT, memberphone TEXT, memberpid TEXT, memberid TEXT, sex TEXT, birthday DATETIME );
字段 cardno 在表示键值的 key 这一列的值是PRI
,意思是 PRIMARYKEY,这就表示它已经被设置成主键了。这里需要注意的一点是,除了字段 cardno,所有的字段都允许为空。这是因为,这些信息有可能当时不知道,要稍后补齐。
插入 1条数据来验证下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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 (会员卡号)
transdate 2020-12-01
1
1
1
89
89
10000001
2020-12-01
我们可以用下面的代码创建销售流水表。因为需要引用会员信息和商品信息,所以表中要包括商品编号字段和会员卡号字段。
1 2 3 4 5 6 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)
插入一条销售流水:
1 2 3 4 5 6 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 日的会员销售记录:
1 2 3 4 5 6 7 8 9 10 11 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 又发给了王五,需要更改会员信息表:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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)
再次运行之前的会员消费流水查询:
1 2 3 4 5 6 7 8 9 10 11 12 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,给这个字段定义自增约束,这样就有了一个具备唯一性的,而且不为空的字段来做主键了。
修改一下会员信息表的结构,添加一个自增字段做主键。
第一步,修改会员信息表,删除表的主键约束,这样,原来的主键字段,就不再是主键了。不过需要注意的是,删除主键约束,并不会删除字段:
1 2 3 4 mysql> alter table demo.membermaster - > drop primary key; Query OK, 2 rows affected (0.15 sec) Records: 2 Duplicates: 0 Warnings: 0
第二步,修改会员信息表,添加字段“id”为主键,并且给它定义自增约束:
1 2 3 4 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,对应会员信息表中的主键:
1 2 3 4 mysql> alter table demo.trans - > add memberid int ; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
第四步,更新一下销售流水表,给新添加的字段 memberid 赋值,让它指向对应的会员信息:
1 2 3 4 5 6 mysql> update demo.trans as a, demo.membermaster as b - > set a.memberid = b.id - > where a.transactionno > 0 ; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0
demo.membermaster 和 demo.trans 的结构:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 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.01 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)
验证前,先将表中的 cardno 为 10000001 的用户改回张三:
1 2 3 4 5 6 7 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 不再使用,发给了王五,就在会员信息表里面增加一条记录:
1 2 3 4 5 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)
查看所有会员信息:
1 2 3 4 5 6 7 8 9 10 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 不再是主键,可以允许重复,因此,我们可以在保留会员“李四”信息的同时,添加使用同一会员卡号的“赵六”的信息。现在再来查会员消费,就不会出问题了:
1 2 3 4 5 6 7 8 9 10 11 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)
如果是一个小项目,只有一个 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 数据库中的数据表字段进行操作,就解决了各门店添加会员时会员编号冲突的问题,同时也避免了使用业务字段导致数据错误的问题。
小结
用业务字段做主键,看起来很简单,但是应该尽量避免 这样做。因为无法预测未来会不会因为业务需要,而出现业务字段重复或者重用的情况
自增字段做主键,对于单机系统 来说是没问题的。但是,如果有多台服务器,各自都可以录入数据,那就不一定适用了。因为如果每台机器各自产生的数据需要合并,就可能会出现主键重复的问题。
采用手动赋值 的办法,通过一定的逻辑,确保字段值在全系统的唯一性,这样就可以规避主键重复的问题了。
给已有的字段增加主键:
1 alter table demo.importhead modify listnumber int primary key;