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

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

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

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

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

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

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

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

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

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

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

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

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

會員卡號（cardno）看起來比較合適，因為會員卡號不能為空，而且有唯一性，可以用來標識一條會員記錄，OK，那我們嘗試一下：

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

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

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

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)

可以看到，不同的會員卡號對應(yīng)不同的會員，字段cardno唯一標識某一個會員。

如果都是這樣的話，會員卡號與會員一一對應(yīng)，系統(tǒng)是可以正常運行的。但是實際情況是，會員卡號是存在重復使用的情況的。這個很好理解，比如，張三因為工作變動搬離了原來的地址，不再到商家的門店消費了（退還了會員卡），于是張三就不再是這家商店的會員了，于是商家為了不讓會員卡空著，就把卡號10000001的會員卡發(fā)給了王五。

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

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

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

因為需要引用會員信息和商品信息，所以銷售流水表需要包括商品編號字段和會員卡號字段：

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
);

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

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)，目的是為了引用其他表的信息，包括會員信息表（demo.membermaster）和商品信息表（demo.goodsmaster）。

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

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

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.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 日，買了一本書，消費 89 元。很明顯，這個結(jié)果把張三的消費行為放到王五身上去了，肯定是不對的。

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

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

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

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

既然業(yè)務(wù)字段不可以，那么我們試試自增字段。

自增字段做主鍵

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

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

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

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，對應(yīng)會員信息表中的主鍵：

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)的會員信息；

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

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

OK，到這里我們就完成了對數(shù)據(jù)表的重新設(shè)計，讓我們來看一下新的數(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)在，如果我們再次面對卡號重用的情況，該如何應(yīng)對呢（這里我們假設(shè)回到修改會員卡 10000001 為王五之前的狀態(tài)）？

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

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

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

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 日，張三買了一本書，消費 89 元，是正確的。

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

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

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

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

手動賦值字段做主鍵

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

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

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

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

主鍵總結(jié)

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

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

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

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

您可能感興趣的文章: