wordpress开启子域名多站,如何在免费网站上做推扩,全球搜钻是什么公司,新站seo优化快速上排名你好#xff0c;我是安然无虞。 文章目录 主键#xff1a;如何正确设置主键#xff1f;业务字段做主键自增字段做主键手动赋值字段做主键 主键总结 主键#xff1a;如何正确设置主键#xff1f;
前面我们在讲解存储的时候#xff0c;有提到过主键#xff0c;它可以唯一… 你好我是安然无虞。 文章目录 主键如何正确设置主键业务字段做主键自增字段做主键手动赋值字段做主键 主键总结 主键如何正确设置主键
前面我们在讲解存储的时候有提到过主键它可以唯一标识表中的某一条记录对数据表来说非常重要。当我们需要查询和引用表中的一条数据记录的时候最好的办法就是通过主键。只有合理地设置主键才能确保我们准确、快速的找到所需要的数据记录。
下面我们借助超市项目的实际需求来讲解一下怎么正确设置主键
在超市项目中店家想进行会员营销相应的我们就需要处理会员信息。
会员信息表demo.membermaster的设计大体如下 为了能够唯一的标识一个会员的信息我们需要为会员信息表设置一个主键那么怎么为这张表设置主键才能达到我们理想的目标呢
其实设置主键一共有三种思路业务字段做主键、自增字段做主键和手动赋值字段做主键。
业务字段做主键
针对这个需求我们最容易想到的是选择表中跟业务相关的字段做主键。
那么在这张表中哪个字段比较合适呢
会员卡号cardno看起来比较合适因为会员卡号不能为空而且有唯一性可以用来标识一条会员记录OK那我们尝试一下
我们在创建表的时候设置字段cardno为主键
create table demo.membermaster
(
cardno char(8) primary key, -- 会员卡号为主键
membername text,
memberphone text,
memberpid text,
memberaddress text,
sex text,
birthday datetime
);会员卡号做主键有什么问题呢我们插入2条数据来验证一下
mysql insert into demo.membermaster
- (
- cardno,
- membername,
- memberphone,
- memberpid,
- memberaddress,
- sex,
- birthday
- )
- values
- (
- 10000001,
- 张三,
- 13812345678,
- 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,
- 李四,
- 13512345678,
- 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 | 张三 | 13812345678 | 110123200001017890 | 北京 | 男 | 2000-01-01 00:00:00 |
| 10000002 | 李四 | 13512345678 | 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),
salesvalues decimal(10,2),
cardno char(8), -- 为了引用会员信息
transdate datetime
);创建表之后我们来插入一条销售流水
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.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.itemnumberc.itemnumber);
------------------------------------------------------------------
| membername | goodsname | quantity | salesvalue | transdate |
------------------------------------------------------------------
| 张三 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
------------------------------------------------------------------
1 row in set (0.00 sec)我们得到的查询结果是张三在 2020 年 12 月 01 日买了一本书花了 89 元。
需要注意的是这里我用到了 JOIN也就是表的关联目的是为了引用其他表的信息包括会员信息表demo.membermaster和商品信息表demo.goodsmaster。
有关关联表查询的具体细节后面文章会讲到这里我们只要知道通过关联查询可以从会员信息表中获取会员信息从商品信息表中获取商品信息就可以了。
下面我们假设会员卡“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.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0会员记录改好了我们再次运行之前的会员消费流水查询
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.itemnumberc.itemnumber);
------------------------------------------------------------------
| membername | goodsname | quantity | salesvalue | transdate |
------------------------------------------------------------------
| 王五 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
------------------------------------------------------------------
1 row in set (0.01 sec)这次得到的结果是王五在 2020 年 12 月 01 日买了一本书消费 89 元。很明显这个结果把张三的消费行为放到王五身上去了肯定是不对的。
这里的原因就是我们把会员卡号是“10000001”的会员信息改了而会员卡号是主键会员消费查询通过会员卡号关联到会员信息得到了完全错误的结果。
所以千万不能把会员卡号当做主键。那么会员电话可以做主键吗不行的。在实际操作中手机号也存在被运营商收回重新发给别人用的情况。那身份证号行不行呢好像可以。因为身份证决不会重复身份证号与一个人存在一一对应的关系。可问题是身份证号属于个人隐私顾客不一定愿意给你。对门店来说顾客就是上帝要是强制要求会员必须登记身份证号会把很多客人赶跑的。其实客户电话也有这个问题这也是我们在设计会员信息表的时候允许身份证号和电话都为空的原因。
这样看来任何一个现有的字段都不适合做主键。
所以这里给出的建议是尽量不要使用业务字段也就是跟业务有关的字段做主键。
既然业务字段不可以那么我们试试自增字段。
自增字段做主键
我们来给会员信息表添加一个字段比如叫 id给这个字段定义自增约束这样我们就有了一个具备唯一性的而且不为空的字段来做主键了。
接下来我们就来修改一下会员信息表的结构添加一个自增字段做主键。
第一步修改会员信息表删除表的主键约束这样一来原先的主键字段就不再是主键了。需要注意的是删除主键约束并不会删除字段。
mysql alter table demo.membermaster
- drop primary key;
Query OK, 2 rows affected (0.12 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.12 sec)
Records: 0 Duplicates: 0 Warnings: 0第三步修改销售流水表添加新的字段menberid对应会员信息表中的主键
mysql alter table demo.trans
- add memberid int;
Query OK, 0 rows affected (0.04 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这个更新语句包含了 2 个关联的表看上去有点复杂。虽然复杂一些但是在实战中更有用。
OK到这里我们就完成了对数据表的重新设计让我们来看一下新的数据表demo.membermaster和demo.trans的结构
mysql desc 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.02 sec)mysql desc demo.trans;
---------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
---------------------------------------------------------
| transactionno | int | NO | PRI | 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 为王五之前的状态
如果张三的会员卡“10000001”不再使用发给了王五我们就在会员信息表里面增加一条记录
mysql insert into demo.membermaster
- (
- cardno,
- membername,
- memberphone,
- memberpid,
- memberaddress,
- sex,
- birthday
- )
- values
- (
- 10000001,
- 王五,
- 13698765432,
- 475145197001012356,
- 天津,
- 女,
- 1970-01-01
- );
Query OK, 1 row affected (0.02 sec)我们看看当前的会员信息
mysql select *- from demo.membermaster;
-----------------------------------------------------------------------------------------------------
| cardno | membername | memberphone | memberpid | memberaddress | sex | birthday | id |
-----------------------------------------------------------------------------------------------------
| 10000001 | 张三 | 13812345678 | 110123200001017890 | 北京 | 男 | 2000-01-01 00:00:00 | 1 |
| 10000002 | 李四 | 13512345678 | 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.itemnumberc.itemnumber);
------------------------------------------------------------------
| membername | goodsname | quantity | salesvalue | transdate |
------------------------------------------------------------------
| 张三 | 书 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
------------------------------------------------------------------
1 row in set (0.01 sec)可以看到结果是 2020 年 12 月 01 日张三买了一本书消费 89 元是正确的。
如果是一个小项目只有一个 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 数据库中的数据表字段进行操作就解决了各门店添加会员时会员编号冲突的问题同时也避免了使用业务字段导致数据错误的问题。
主键总结
设置数据表主键的三种方式数据表的业务字段做主键、添加自增字段做主键以及添加手动赋值字段做主键。
用业务字段做主键看起来很简单但是我们应该尽量避免这样做。因为我们无法预测未来会不会因为业务需要而出现业务字段重复或者重用的情况。自增字段做主键对于单机系统来说是没问题的。但是如果有多台服务器各自都可以录入数据那就不一定适用了。因为如果每台机器各自产生的数据需要合并就可能会出现主键重复的问题。我们可以采用手动赋值的办法通过一定的逻辑确保字段值在全系统的唯一性这样就可以规避主键重复的问题了。
刚开始使用 MySQL 时很多人都很容易犯的错误是喜欢用业务字段做主键想当然地认为了解业务需求但实际情况往往出乎意料而更改主键设置的成本非常高。所以如果你的系统比较复杂尽量给表加一个字段做主键采用手动赋值的办法虽然系统开发的时候麻烦一点却可以避免后面出大问题。 文章参考朱晓峰·MySQL必知必会
