MySQL數(shù)據(jù)庫如何正確設(shè)置主鍵

更新時(shí)間：2024年04月13日 09:35:04 作者：網(wǎng)安導(dǎo)師小李

主鍵是用于唯一標(biāo)識(shí)數(shù)據(jù)庫表中每一行數(shù)據(jù)的一列或一組列,主鍵可以確保數(shù)據(jù)的唯一性和完整性,這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫如何正確設(shè)置主鍵的相關(guān)資料,需要的朋友可以參考下

主鍵：如何正確設(shè)置主鍵？

前面我們?cè)谥v解存儲(chǔ)的時(shí)候，有提到過主鍵，它可以唯一標(biāo)識(shí)表中的某一條記錄，對(duì)數(shù)據(jù)表來說非常重要。當(dāng)我們需要查詢和引用表中的一條數(shù)據(jù)記錄的時(shí)候，最好的辦法就是通過主鍵。只有合理地設(shè)置主鍵，才能確保我們準(zhǔn)確、快速的找到所需要的數(shù)據(jù)記錄。

下面我們借助超市項(xiàng)目的實(shí)際需求，來講解一下怎么正確設(shè)置主鍵？

在超市項(xiàng)目中，店家想進(jìn)行會(huì)員營銷，相應(yīng)的，我們就需要處理會(huì)員信息。

會(huì)員信息表（demo.membermaster）的設(shè)計(jì)大體如下：

為了能夠唯一的標(biāo)識(shí)一個(gè)會(huì)員的信息，我們需要為會(huì)員信息表設(shè)置一個(gè)主鍵，那么，怎么為這張表設(shè)置主鍵，才能達(dá)到我們理想的目標(biāo)呢？

其實(shí)，設(shè)置主鍵一共有三種思路：業(yè)務(wù)字段做主鍵、自增字段做主鍵和手動(dòng)賦值字段做主鍵。

業(yè)務(wù)字段做主鍵

針對(duì)這個(gè)需求，我們最容易想到的是選擇表中跟業(yè)務(wù)相關(guān)的字段做主鍵。

那么在這張表中，哪個(gè)字段比較合適呢？

會(huì)員卡號(hào)（cardno）看起來比較合適，因?yàn)闀?huì)員卡號(hào)不能為空，而且有唯一性，可以用來標(biāo)識(shí)一條會(huì)員記錄，OK，那我們嘗試一下：

我們?cè)趧?chuàng)建表的時(shí)候，設(shè)置字段cardno為主鍵：

create table demo.membermaster
(
cardno char(8) primary key, -- 會(huì)員卡號(hào)為主鍵
membername text,
memberphone text,
memberpid text,
memberaddress text,
sex text,
birthday datetime
);

會(huì)員卡號(hào)做主鍵有什么問題呢？我們插入2條數(shù)據(jù)來驗(yàn)證一下：

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)

插入成功后，我們來看看表中的內(nèi)容：

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)

可以看到，不同的會(huì)員卡號(hào)對(duì)應(yīng)不同的會(huì)員，字段cardno唯一標(biāo)識(shí)某一個(gè)會(huì)員。

如果都是這樣的話，會(huì)員卡號(hào)與會(huì)員一一對(duì)應(yīng)，系統(tǒng)是可以正常運(yùn)行的。但是實(shí)際情況是，會(huì)員卡號(hào)是存在重復(fù)使用的情況的。這個(gè)很好理解，比如，張三因?yàn)楣ぷ髯儎?dòng)搬離了原來的地址，不再到商家的門店消費(fèi)了（退還了會(huì)員卡），于是張三就不再是這家商店的會(huì)員了，于是商家為了不讓會(huì)員卡空著，就把卡號(hào)10000001的會(huì)員卡發(fā)給了王五。

從系統(tǒng)設(shè)計(jì)的角度看，這個(gè)變化只是修改了會(huì)員信息表的卡號(hào)10000001這個(gè)會(huì)員信息，并不會(huì)影響到數(shù)據(jù)的一致性。也就是說，修改會(huì)員卡號(hào)10000001的會(huì)員信息，系統(tǒng)的各個(gè)模塊，都會(huì)獲取到修改后的會(huì)員信息，不會(huì)出現(xiàn)“有的模塊獲取到修改之前的會(huì)員信息，有的模塊獲取到修改后的會(huì)員信息，而導(dǎo)致系統(tǒng)內(nèi)部數(shù)據(jù)不一致”的情況。因此，從信息系統(tǒng)層面上看是沒問題的。

但是從使用系統(tǒng)的業(yè)務(wù)層面來看，就有很大的問題了，會(huì)對(duì)商家造成影響。

比如，我們有一個(gè)銷售流水表，記錄了所有的銷售流水明細(xì)。2020 年 12 月 01 日，張三在門店購買了一本書，消費(fèi)了 89 元。那么，系統(tǒng)中就有了張三買書的流水記錄，如下所示：

因?yàn)樾枰脮?huì)員信息和商品信息，所以銷售流水表需要包括商品編號(hào)字段和會(huì)員卡號(hào)字段：

create table demo.trans
(
transactionno int,
itemnumber int, -- 為了引用商品信息
quantity decimal(10,3), 
price decimal(10,2),
salesvalues decimal(10,2),
cardno char(8), -- 為了引用會(huì)員信息
transdate datetime
);

創(chuàng)建表之后，我們來插入一條銷售流水：

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日的會(huì)員銷售記錄：

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.itemnumber=c.itemnumber);
+------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate |
+------------+-----------+----------+------------+---------------------+
| 張三 | 書 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.00 sec)

我們得到的查詢結(jié)果是：張三，在 2020 年 12 月 01 日買了一本書，花了 89 元。

需要注意的是，這里我用到了 JOIN，也就是表的關(guān)聯(lián)，目的是為了引用其他表的信息，包括會(huì)員信息表（demo.membermaster）和商品信息表（demo.goodsmaster）。

有關(guān)關(guān)聯(lián)表查詢的具體細(xì)節(jié)，后面文章會(huì)講到，這里我們只要知道，通過關(guān)聯(lián)查詢，可以從會(huì)員信息表中獲取會(huì)員信息，從商品信息表中獲取商品信息，就可以了。

下面，我們假設(shè)會(huì)員卡“10000001”又發(fā)給了王五，我們需要更改會(huì)員信息表：

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

會(huì)員記錄改好了，我們?cè)俅芜\(yùn)行之前的會(huì)員消費(fèi)流水查詢：

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.itemnumber=c.itemnumber);
+------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate |
+------------+-----------+----------+------------+---------------------+
| 王五 | 書 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.01 sec)

這次得到的結(jié)果是：王五在 2020 年 12 月 01 日，買了一本書，消費(fèi) 89 元。很明顯，這個(gè)結(jié)果把張三的消費(fèi)行為放到王五身上去了，肯定是不對(duì)的。

這里的原因就是，我們把會(huì)員卡號(hào)是“10000001”的會(huì)員信息改了，而會(huì)員卡號(hào)是主鍵，會(huì)員消費(fèi)查詢通過會(huì)員卡號(hào)關(guān)聯(lián)到會(huì)員信息，得到了完全錯(cuò)誤的結(jié)果。

所以，千萬不能把會(huì)員卡號(hào)當(dāng)做主鍵。那么，會(huì)員電話可以做主鍵嗎？不行的。在實(shí)際操作中，手機(jī)號(hào)也存在被運(yùn)營商收回，重新發(fā)給別人用的情況。那身份證號(hào)行不行呢？好像可以。因?yàn)樯矸葑C決不會(huì)重復(fù)，身份證號(hào)與一個(gè)人存在一一對(duì)應(yīng)的關(guān)系?？蓡栴}是，身份證號(hào)屬于個(gè)人隱私，顧客不一定愿意給你。對(duì)門店來說，顧客就是上帝，要是強(qiáng)制要求會(huì)員必須登記身份證號(hào)，會(huì)把很多客人趕跑的。其實(shí)，客戶電話也有這個(gè)問題，這也是我們?cè)谠O(shè)計(jì)會(huì)員信息表的時(shí)候，允許身份證號(hào)和電話都為空的原因。

這樣看來，任何一個(gè)現(xiàn)有的字段都不適合做主鍵。

所以這里給出的建議是，盡量不要使用業(yè)務(wù)字段，也就是跟業(yè)務(wù)有關(guān)的字段做主鍵。

既然業(yè)務(wù)字段不可以，那么我們?cè)囋囎栽鲎侄巍?/p>

自增字段做主鍵

我們來給會(huì)員信息表添加一個(gè)字段，比如叫 id，給這個(gè)字段定義自增約束，這樣，我們就有了一個(gè)具備唯一性的，而且不為空的字段來做主鍵了。

接下來，我們就來修改一下會(huì)員信息表的結(jié)構(gòu)，添加一個(gè)自增字段做主鍵。

第一步，修改會(huì)員信息表，刪除表的主鍵約束，這樣一來，原先的主鍵字段，就不再是主鍵了。需要注意的是，刪除主鍵約束，并不會(huì)刪除字段。

mysql> alter table demo.membermaster
-> drop primary key;
Query OK, 2 rows affected (0.12 sec)
Records: 2 Duplicates: 0 Warnings: 0

第二步，修改會(huì)員信息表，添加字段‘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，對(duì)應(yīng)會(huì)員信息表中的主鍵：

mysql> alter table demo.trans
-> add memberid int;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

第四步，我們更新一下銷售流水表，給新添加的字段memberid賦值，讓它指向?qū)?yīng)的會(huì)員信息；

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的內(nèi)容，在實(shí)際工作中更適合
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

這個(gè)更新語句包含了 2 個(gè)關(guān)聯(lián)的表，看上去有點(diǎn)復(fù)雜。雖然復(fù)雜一些，但是在實(shí)戰(zhàn)中更有用。

OK，到這里我們就完成了對(duì)數(shù)據(jù)表的重新設(shè)計(jì)，讓我們來看一下新的數(shù)據(jù)表demo.membermaster和demo.trans的結(jié)構(gòu)：

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)

現(xiàn)在，如果我們?cè)俅蚊鎸?duì)卡號(hào)重用的情況，該如何應(yīng)對(duì)呢（這里我們假設(shè)回到修改會(huì)員卡 10000001 為王五之前的狀態(tài)）？

如果張三的會(huì)員卡“10000001”不再使用，發(fā)給了王五，我們就在會(huì)員信息表里面增加一條記錄：

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)

我們看看當(dāng)前的會(huì)員信息：

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”不再是主鍵，可以允許重復(fù)，因此，我們可以在保留會(huì)員“張三”信息的同時(shí)，添加使用同一會(huì)員卡號(hào)的“王五”的信息。

現(xiàn)在再來查會(huì)員消費(fèi)，就不會(huì)出問題了：

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.itemnumber=c.itemnumber);
+------------+-----------+----------+------------+---------------------+
| membername | goodsname | quantity | salesvalue | transdate |
+------------+-----------+----------+------------+---------------------+
| 張三 | 書 | 1.000 | 89.00 | 2020-12-01 00:00:00 |
+------------+-----------+----------+------------+---------------------+
1 row in set (0.01 sec)

可以看到，結(jié)果是 2020 年 12 月 01 日，張三買了一本書，消費(fèi) 89 元，是正確的。

如果是一個(gè)小項(xiàng)目，只有一個(gè) MySQL 數(shù)據(jù)庫服務(wù)器，用添加自增字段作為主鍵的辦法是可以的。不過，這并不意味著，在任何情況下你都可以這么做。

舉個(gè)例子，用戶要求把增加新會(huì)員的工作放到門店進(jìn)行，因?yàn)榘l(fā)展新會(huì)員的工作一般是在門店進(jìn)行的，畢竟，人們一般都是在購物的同時(shí)申請(qǐng)會(huì)員。解決的辦法是，門店的信息系統(tǒng)添加新增會(huì)員的功能，把新的會(huì)員信息先存放到本地 MySQL 數(shù)據(jù)庫中，再上傳到總部，進(jìn)行匯總。

可是問題來了，如果會(huì)員信息表的主鍵是自增的，那么各個(gè)門店新加的會(huì)員就會(huì)出現(xiàn)“id”沖突的可能。

比如，A 店的 MySQL 數(shù)據(jù)庫中的 demo.membermaster 中，字段“id”的值是 100，這個(gè)時(shí)候，新增了一個(gè)會(huì)員，“id”是 101。同時(shí)，B 店的字段“id”值也是 100，要加一個(gè)新會(huì)員，“id”也是 101，畢竟，B 店的 MySQL 數(shù)據(jù)庫與 A 店相互獨(dú)立。等 A 店與 B 店都把新的會(huì)員上傳到總部之后，就會(huì)出現(xiàn)兩個(gè)“id”是 101，但卻是不同會(huì)員的情況，這該如何處理呢？

手動(dòng)賦值字段做主鍵

為了解決這個(gè)問題，我們想了一個(gè)辦法：取消字段“id”的自增屬性，改成信息系統(tǒng)在添加會(huì)員的時(shí)候?qū)?ldquo;id”進(jìn)行賦值。

具體的操作是這樣的：在總部 MySQL 數(shù)據(jù)庫中，有一個(gè)管理信息表，里面的信息包括成本核算策略，支付方式等，還有總部的系統(tǒng)參數(shù)，我們可以在這個(gè)表中添加一個(gè)字段，專門用來記錄當(dāng)前會(huì)員編號(hào)的最大值。

門店在添加會(huì)員的時(shí)候，先到總部 MySQL 數(shù)據(jù)庫中獲取這個(gè)最大值，在這個(gè)基礎(chǔ)上加 1，然后用這個(gè)值作為新會(huì)員的“id”，同時(shí)，更新總部 MySQL 數(shù)據(jù)庫管理信息表中的當(dāng)前會(huì)員編號(hào)的最大值。

這樣一來，各個(gè)門店添加會(huì)員的時(shí)候，都對(duì)同一個(gè)總部 MySQL 數(shù)據(jù)庫中的數(shù)據(jù)表字段進(jìn)行操作，就解決了各門店添加會(huì)員時(shí)會(huì)員編號(hào)沖突的問題，同時(shí)也避免了使用業(yè)務(wù)字段導(dǎo)致數(shù)據(jù)錯(cuò)誤的問題。

主鍵總結(jié)

設(shè)置數(shù)據(jù)表主鍵的三種方式：數(shù)據(jù)表的業(yè)務(wù)字段做主鍵、添加自增字段做主鍵，以及添加手動(dòng)賦值字段做主鍵。

用業(yè)務(wù)字段做主鍵，看起來很簡單，但是我們應(yīng)該盡量避免這樣做。因?yàn)槲覀儫o法預(yù)測未來會(huì)不會(huì)因?yàn)闃I(yè)務(wù)需要，而出現(xiàn)業(yè)務(wù)字段重復(fù)或者重用的情況。
自增字段做主鍵，對(duì)于單機(jī)系統(tǒng)來說是沒問題的。但是，如果有多臺(tái)服務(wù)器，各自都可以錄入數(shù)據(jù)，那就不一定適用了。因?yàn)槿绻颗_(tái)機(jī)器各自產(chǎn)生的數(shù)據(jù)需要合并，就可能會(huì)出現(xiàn)主鍵重復(fù)的問題。
我們可以采用手動(dòng)賦值的辦法，通過一定的邏輯，確保字段值在全系統(tǒng)的唯一性，這樣就可以規(guī)避主鍵重復(fù)的問題了。

剛開始使用 MySQL 時(shí)，很多人都很容易犯的錯(cuò)誤是喜歡用業(yè)務(wù)字段做主鍵，想當(dāng)然地認(rèn)為了解業(yè)務(wù)需求，但實(shí)際情況往往出乎意料，而更改主鍵設(shè)置的成本非常高。所以，如果你的系統(tǒng)比較復(fù)雜，盡量給表加一個(gè)字段做主鍵，采用手動(dòng)賦值的辦法，雖然系統(tǒng)開發(fā)的時(shí)候麻煩一點(diǎn)，卻可以避免后面出大問題。

到此這篇關(guān)于MySQL數(shù)據(jù)庫如何正確設(shè)置主鍵的文章就介紹到這了,更多相關(guān)MySQL正確設(shè)置主鍵內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: