05-设置主键

05-设置主键

设置主键的三种思路：

  • 业务字段做主键
  • 自增字段做主键
  • 手动赋值字段做主键

以会员信息表为例：

业务字段做主键

会员卡号适合，具有唯一性，可以用来表示会员记录

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

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

张三将卡转给了王五，此时cardno将无法唯一识别某个会员，有的模块获取了修改前的信息，有的模块获取了修改后的信息，导致内部数据不一致。

这种修改如何影响商家？

张三有一条买书的流水记录

创建销售流水表

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

此处使用了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

再次查询消费流水

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

变成了王五买馒头！

所以不能拿会员号作为主键

目前看，任何一个已有字段都不适合做主键

尽量不要用业务字段作为主键，因为无法预测项目的整个生命周期内，哪些业务字段会因为项目的业务需求而又重复或重用。

使用自增字段做主键

给会员信息添加一个字段id，给字段定义自增约束

  • 第一步：修改会员信息表，删除表的主键约束
1
2
alter table demo.membermaster
drop primary key
MYSQL

  • 第二步：修改会员信息表，添加字段id作为主键，定义自增约束
1
2
alter table demo.membermaster
add id int primary key auto_increment;
MYSQL

  • 第三步：修改销售流水表，添加memberid，对应会员表中的主键
1
2
alter table demo.trans
add memberid int;
MYSQL

  • 第四步：更新销售流水表，给新加字段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

此时，再次面对卡号重用，比如张三的卡转给王五，则在会员信息表中增加一条

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

保存了张三的信息，将不会出现错位问题

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

但是如果门店新增会员，新会员信息存在本地Mysqk之中，再汇总到总部，本地id自增就会与总部的id相互冲突，这种情况就需要取消id的自增属性，改成信息系统在添加过程中对id进行赋值操作

手动赋值字段做主键

具体操作：在总部的Mysql数据库中，有一个管理信息表，里面的信息包括成本核算策略、支付方式等，还有总部的系统参数，只需要天际一个字段用于记录会员编号的最大值

添加会员时，先获取总部数据库中的id最大值，再在此基础上加1，作为新的id

MySQL
05-设置主键
http://example.com/2023/01/17/05-设置主键/
Author
WYX
Posted on
January 17, 2023
Licensed under