快捷導(dǎo)航

MySQL無(wú)法修改主鍵的問(wèn)題分析及解決方案

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

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

問(wèn)題背景

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

[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.

問(wèn)題分析

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

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

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

針對(duì)這個(gè)場(chǎng)景，是否還可以將主鍵改為聯(lián)合主鍵？

創(chuàng)建一張測(cè)試表，主鍵初始是 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ù)控制了強(qiáng)制檢驗(yàn)主鍵，而且又是可動(dòng)態(tài)修改的，臨時(shí)關(guān)閉，再打開(kāi)即可。

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

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

方案二

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

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

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

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

關(guān)于 SQLE

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

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

您可能感興趣的文章: