快捷導(dǎo)航

MySQL無法修改主鍵的問題分析及解決方案

更新時間：2024年01月23日 10:21:59 作者：愛可生開源社區(qū)

最近同事咨詢了一個問題,TDSQL（for MySQL）中的某張表主鍵需要改為聯(lián)合主鍵,是否必須先刪除現(xiàn)有的主鍵？因為刪除主鍵時,提示這個錯誤,所以本文給大家介紹了MySQL無法修改主鍵的問題分析及解決方案,需要的朋友可以參考下

問題背景

同事咨詢了一個問題，TDSQL（for MySQL）中的某張表主鍵需要改為聯(lián)合主鍵，是否必須先刪除現(xiàn)有的主鍵？因為刪除主鍵時，提示這個錯誤。

[test]> alter table test drop primary key;
ERROR 3750 (HY000): Unable to create or change a table without a primary key, 
when the system variable 'sql_require_primary_key' is set. 
Add a primary key to the table or unset this variable to avoid this message. 
Note that tables without a primary key can cause performance problems in row-based replication, 
so please consult your DBA before changing this setting.

問題分析

從提示上可以看到具體的原因，當設(shè)置了 sql_require_primary_key 參數(shù)，不能創(chuàng)建或改變一張沒有主鍵的表。解決方案是增加主鍵或者刪除此參數(shù)避免錯誤，同時提醒了，如果表無主鍵，可能會導(dǎo)致基于行的復(fù)制產(chǎn)生性能問題。

sql_require_primary_key 參數(shù)控制的是強制檢查主鍵，可以動態(tài)修改。

參數(shù)名稱：sql_require_primary_key
作用范圍：Global & Session
動態(tài)修改：Yes
默認值：OFF
該參數(shù)設(shè)置為ON時，SQL語句create table創(chuàng)建新表或者alter語句對已存在的表進行修改，將會強制檢查表中是否包含主鍵，如果沒有主鍵，則會報錯。

針對這個場景，是否還可以將主鍵改為聯(lián)合主鍵？

創(chuàng)建一張測試表，主鍵初始是 id。

bisal@mysqldb:  [test]> create table t_primary_key (id int, c1 varchar(1), c2 varchar(1), constraint pk_t_id primary key(id));
Query OK, 0 rows affected (0.07 sec)

解決方案

方案一

既然 sql_require_primary_key 參數(shù)控制了強制檢驗主鍵，而且又是可動態(tài)修改的，臨時關(guān)閉，再打開即可。

bisal@mysqldb:  [test]> alter table t_primary_key drop primary key;
ERROR 3750 (HY000): Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avo

bisal@mysqldb:  [(none)]> show variables like '%sql_require%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| sql_require_primary_key | OFF   |
+-------------------------+-------+
1 row in set (0.00 sec)

bisal@mysqldb:  [(none)]> set sql_require_primary_key = ON;
Query OK, 0 rows affected (0.02 sec)

bisal@mysqldb:  [(none)]> show variables like '%sql_require%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| sql_require_primary_key | ON    |
+-------------------------+-------+
1 row in set (0.00 sec)

bisal@mysqldb:  [test]> alter table t_primary_key drop primary key;
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

但可能的風險，就是刪除主鍵，再創(chuàng)建主鍵的這段時間內(nèi)，如果有主鍵字段的重復(fù)數(shù)據(jù)插入，就可能導(dǎo)致創(chuàng)建新的主鍵不成功。另外，鑒于該參數(shù)設(shè)置成為非默認值，創(chuàng)建完主鍵，需要記得改過來。

方案二

如果 sql_require_primary_key 設(shè)置為 ON，意思就是表任何的時刻都需要有主鍵，不能出現(xiàn)真空。變更主鍵的操作，實際包含了刪除原主鍵和創(chuàng)建新的主鍵兩個步驟，因此只需要將兩個步驟合并成一個即可。

MySQL 支持多個語句一次執(zhí)行，因此只需要將 alter table ... drop primary key 和 add constraint ... primary key ... 合成一條語句。

bisal@mysqldb:  [test]> alter table t_primary_key drop primary key, add constraint pk_t_01 primary key (id, c1);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

總結(jié)

從這個問題可以看出來，MySQL 的參數(shù)控制粒度很細，但通過各種應(yīng)對方法，可以針對性解決特定的場景問題，但前提還是對參數(shù)的意義，以及場景的需求能充分了解，才能找到合適的解決方案。

關(guān)于 SQLE

SQLE 是一款全方位的 SQL 質(zhì)量管理平臺，覆蓋開發(fā)至生產(chǎn)環(huán)境的 SQL 審核和管理。支持主流的開源、商業(yè)、國產(chǎn)數(shù)據(jù)庫，為開發(fā)和運維提供流程自動化能力，提升上線效率，提高數(shù)據(jù)質(zhì)量。

以上就是MySQL無法修改主鍵的問題分析及解決方案的詳細內(nèi)容，更多關(guān)于MySQL無法修改主鍵的資料請關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: