05-设置主键设置主键的三种思路:
业务字段做主键
自增字段做主键
手动赋值字段做主键
以会员信息表为例:
image-20230117105518725
业务字段做主键会员卡号适合,具有唯一性,可以用来表示会员记录
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, memberaddress text, sex text, birthday datetime );
MYSQL
image-20230117105816605
cardno被设置为了主键
插入两条数据
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 31 32 33 34 35 36 37 38 39 40 41 42 insert into demo.membermaster ( cardno, membername, memberphone, memberpid, memberaddress, sex, birthday ) values ( '10000001', '张三', '13812345678', '110123200001017890', '北京', '男', '2000-01-01' ); insert into demo.membermaster ( cardno, membername, memberphone, memberpid, memberaddress, sex, birthday ) values ( '10000002', '李四', '13512345678', '123123199001012356', '上海', '女', '1990-01-01' );
MYSQL
image-20230117110451072
张三将卡转给了王五,此时cardno将无法唯一识别某个会员,有的模块获取了修改前的信息,有的模块获取了修改后的信息,导致内部数据不一致。
这种修改如何影响商家?
张三有一条买书的流水记录
image-20230117110839531
创建销售流水表
1 2 3 4 5 6 7 8 9 10 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 );
MYSQL
创建以后,插入一条销售流水
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 insert into demo.trans ( transactionno, itemnumber, quantity, price, salesvalue, cardno, transdate ) values ( 1, 1, 1, 89, 89, '10000001', '2020-12-01' );
MYSQL
查询2020-12-01的会员销售记录
1 2 3 4 5 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
image-20230117112728303
此处使用了join,即表的关联,目的是为了引用其他表的信息,包括了demo.goodsmaster和demo.membermaster
修改会员信息,将10000001卡给王五
1 2 3 4 5 6 7 8 update demo.membermaster set membername = '王五', memberphone = '13698765432', memberpid = '475145197001012356', memberaddress = '天津', sex = '女', birthday = '1970-01-01' where cardno = '10000001'
MYSQL
image-20230117114142524
再次查询消费流水
1 2 3 4 5 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
image-20230117114222836
变成了王五买馒头!
所以不能拿会员号作为主键
目前看,任何一个已有字段都不适合做主键
尽量不要用业务字段作为主键 ,因为无法预测项目的整个生命周期内,哪些业务字段会因为项目的业务需求而又重复或重用。
使用自增字段做主键给会员信息添加一个字段id,给字段定义自增约束
1 2 alter table demo.membermaster drop primary key
MYSQL
image-20230117114839786
第二步:修改会员信息表,添加字段id作为主键,定义自增约束
1 2 alter table demo.membermaster add id int primary key auto_increment;
MYSQL
image-20230117122830695
第三步:修改销售流水表,添加memberid,对应会员表中的主键
1 2 alter table demo.trans add memberid int;
MYSQL
image-20230117123020506
第四步:更新销售流水表,给新加字段memberid赋值
1 2 3 4 update demo.trans as a,demo.membermaster as b set a.memberid = b.id where a.transactionno > 0 and a.cardno = b.cardno;
MYSQL
image-20230117123257361
此时,再次面对卡号重用,比如张三的卡转给王五,则在会员信息表中增加一条
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 insert into demo.membermaster ( cardno, membername, memberphone, memberpid, memberaddress, sex, birthday ) values ( '10000001', '王五', '13698765432', '475145197001012356', '天津', '女', '1970-01-01' );
MYSQL
image-20230117161145263
保存了张三的信息,将不会出现错位问题
1 2 3 4 5 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);
MYSQL
image-20230117162221748
但是如果门店新增会员,新会员信息存在本地Mysqk之中,再汇总到总部,本地id自增就会与总部的id相互冲突,这种情况就需要取消id的自增属性,改成信息系统在添加过程中对id进行赋值操作
手动赋值字段做主键具体操作:在总部的Mysql数据库中,有一个管理信息表,里面的信息包括成本核算策略、支付方式等,还有总部的系统参数,只需要天际一个字段用于记录会员编号的最大值
添加会员时,先获取总部数据库中的id最大值,再在此基础上加1,作为新的id