端端舝

sql_require_primary_key毛妏勻化翋平奈毛斛
�卞今六月
1

赻撩畿賡
$?cat?/etc/lhfukamachi/lhfukamachi?auto.cnf??|?jq?
{?
??"Version":?0.0.2,?
??"lhfukamachi":?{?
????"Metadata":?{?
????????"BirthDay":?622134000,?
????????"attribute":?"𦤹嗷匹�P仁��中DBA",?
????????"Twitter":?"@lhfukamachi",?
????}?
}?
2

心卅今氏皿仿奶穴伉奈平奈ㄗP?ㄘ反疑五匹允
井ˋ
3

翋平奈及疑五卅午仇欠
羯�卞NotNull匹丐月
P?竘五互惇厒
惿曰源卞方勻化反⺍講毛�廣匹五月
翋平奈及剠中氾奈皮伙毛醴珨戚�凶摽卞﹜翋平奈及丐月氾奈皮伙毛
�月午猞及假陑覜毛覜元日木月
5

MySQL8.0.13井日P?毛氾奈皮伙卞斛�午允
月仇午互匹五月方丹卞卅曰引仄凶﹝
6

sql_require_primary_key
氾奈皮伙及釬傖引凶反氾奈皮伙�婖及劐載及媆卞翋平奈毛斛�卞今
六月﹝
左件仿奶件匹劐載褫夔
GLOBAL匹偞隅允月午赻旯及本永扑亦件眕俋互偞隅今木月
8

手仄P?互剠中氾奈皮伙毛釬欠丹午允月午＃
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.?
9

sql_require_primary_key毛手勻午捼屯月
10

sql_require_primary_key匹匹五月仇午﹜
匹五卅中仇午
11

疶�1:
P?互卅中氾奈皮伙互允匹卞衄月�磁卞偞隅匹五月井﹝
12

P?互卅中氾奈皮伙互允匹卞衄月�磁卞偞隅匹
五月井﹝
1.P?及卅中氾奈皮伙毛釬傖仄化偞隅仄化心月﹝
mysql?[d1]>?SHOW?VARIABLES?LIKE?'sql_require_primary_key';?
+?????????????????????????+???????+?
|?Variable_name???????????|?Value?|?
+?????????????????????????+???????+?
|?sql_require_primary_key?|?OFF???|?
+?????????????????????????+???????+?
1?row?in?set?(0.00?sec)?
?
mysql?[d1]>?CREATE?TABLE?no_pk_table(id?int,?value?varchar(256));
Query?OK,?0?rows?affected?(0.14?sec)?
?
mysql?[d1]>?SET?GLOBAL?sql_require_primary_key?=?1;?
?
13

疶�2ㄩ
SETSESSION反妏尹月井ˋ
15

SETSESSION反妏尹月井ˋ
mysql?[(none)]>?SET?SESSION?sql_require_primary_key?=?1;?
16

疶�3ㄩ
TEMPORARYTABLE手�砓井ˋ
18

TEMPORARYTABLE手�砓井ˋ
mysql?[d1]>?SHOW?VARIABLES?LIKE?'sql_require_primary_key';?
+?????????????????????????+???????+?
|?Variable_name???????????|?Value?|?
+?????????????????????????+???????+?
|?sql_require_primary_key?|?ON????|?
+?????????????????????????+???????+?
?
mysql?[d1]>?CREATE?TEMPORARY?TABLE?no_pk_tmp_table(id?int,??
value?varchar(256));?
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??
19

疶�4:
PRIMARY?EY毛DROP允月仇午反匹五月及
井ˋ
21

井ˋ
mysql?[d1]>?SHOW?CREATE?TABLE?pk_tableG?
**?1.?row?**?
???????Table:?pk_table?
Create?Table:?CREATE?TABLE?`pk_table`?(?
??`id`?int(11)?NOT?NULL,?
??`value`?varchar(256)?COLLATE?utf8mb4_bin?DEFAULT?NULL,?
??PRIMARY?KEY?(`id`)?
)?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COLLATE=utf8mb4_bin?
?
mysql?[d1]>?ALTER?TABLE?pk_table?DROP?PRIMARY?KEY;?
?
ERROR?3750?(HY000):?Unable?to?create?or?change?a?table?without??
?
22

井ˋ
DROP午ADD毛�扴允木壬O?卅及匹P?毛劐載仄凶中午五反1恅匹�扴允
月
mysql?[d1]>?ALTER?TABLE?pk_table?DROP?PRIMARY?KEY,?ADD?PRIMARY?
KEY(value);?
Records:?0??Duplicates:?0??Warnings:?0?
24

疶�5:
NOTNULL+UNIQUE?EY匹手中中ˋ
25

NOTNULL+UNIQUE?EY匹手中中ˋ
mysql?[d1]>?CREATE?TABLE?unique_notnull_table(id?int?NOT?NULL??
UNIQUE?KEY,?value?varchar(256));?
?
26

疶�6:
InnoDB眕俋匹手妏尹月ˋ
28

MyISAMㄩ
CREATE?TABLE?pk_table_myisam(id?int,?value?varchar(256))?
ENGINE=myisam;?
?
?
CREATE?TABLE?pk_table_myisam2(id?int?primary?key,?value?
varchar(256))?ENGINE=MYISAM;?
29

CSV:
mysql?[d1]>?CREATE?TABLE?pk_table_csv(id?int?primary?key,?value?
varchar(256))?ENGINE=CSV;?
ERROR?1069?(42000):?Too?many?keys?specified;?max?0?keys?allowed?
?
mysql?[d1]>?CREATE?TABLE?pk_table_csv(id?int,?value??
varchar(256))?ENGINE=CSV;?
30

CSV:
升丹仄化手CSV毛妏中凶中卅日SETSESSION匹珨媆腔卞OFF月
mysql?[d1]>?SET?SESSION?sql_require_primary_key?=?0;?
mysql?[d1]>?CREATE?TABLE?pk_table_csv(id?int?not?null,??
?????????????value?varchar(256)?not?null)?ENGINE=CSV;?
31

疶�7:
INVISIBLE反�仁ˋ
32

INVISIBLE反�仁ˋ
公手公手PRIMARY?EY卞�仄化反中氏太元木卅中﹝
MySQLsupportsinvisibleindexes;thatis,indexesthatarenot
usedbytheoptimizer.Thefeatureappliestoindexesotherthan
primarykeys(eitherexplicitorimplicit).
https://dev.mysql.com/doc/refman/8.0/en/invisible?indexes.html
33

疶�8:
伊皿伉弗奈扑亦件反?
34

瞰尹壬仇丹中丹�傖
35

籵月!!!!?
36

卅兮籵月及井﹝binlog毛�化心月﹝
190607?12:40:29?server?id?29355??end_log_pos?653?CRC32??
0x974104c3??????Query???thread_id=22????exec_time=0??????
error_code=0????Xid?=?58?
use?`d1`/*!*/;?
SET?TIMESTAMP=1559878829/*!*/;?
/*!C?utf8mb4?*//*!*/;?
SET??
@@session.character_set_client=255,@@session.?
collation_connection=255,@@session.collation_server=46/*!*/;?
/*!80011?SET??
@@session.default_collation_for_utf8mb4=255*//*!*/;?
/*!80013?SET?@@session.sql_require_primary_key=0*//*!*/;?
CREATE?TABLE?`no_pk_table`?(?
??`id`?int(11),?
??`value`?varchar(256)?
)?
/*!*/;?
SET?@@SESSION.GTID_NEXT=?'AUTOMATIC'?/*?added?by?mysqlbinlog?*/?
/*!*/;?
DELIMITER?;?
37

DDL毛灍俴ゴ卞
SET?@@session.sql_require_primary_key=0
毛灍俴仄化中月﹝
38

凶分仄仇丹中丹�傖反蛁砩ㄐ
39

伊皿伉弗奈扑亦件互砦引月ㄐ
40

伊皿伉弗奈扑亦件互砦引月ㄐ
mysql?[d1]>?SHOW?SLAVE?STATUSG?
~?
~?
????????????????Last_IO_Errno:?0?
????????????????Last_IO_Error:?
???????????????Last_SQL_Errno:?3750?
???????????????Last_SQL_Error:?Error?'Unable?to?create?or??
???????????????change?a?table?without?a?primary?key,?when?
???????????????the?system?variable?'sql_r?
equire_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??
setti?
ng.'?on?query.?Default?database:?'d57'.?Query:?'CREATE?TABLE??
`no_pk_table`?(?
`id`?int(11),?
??`value`?varchar(256)?
)'?
41

SETSESSION匹sql_require_primary_key毛OFF勻化STARTSLAVE允月
DDL互籵勻凶日ON卞仄化婬僅伊皿伉弗奈扑亦件毛STOP?START允月
42

引午戶
sql_require_primary_key反
暫湔及氾奈皮伙卞P?互卅仁化手偞隅匹五﹜
SETSESSION匹手妏尹化﹜
TEMPORARYTABLE手�砓卞卅曰﹜
P?毛DROP允月仇午反匹五卅仁化﹜↗劐載反匹五月
InnoDB眕俋匹手勾井尹月
晞瞳卅左皿扑亦件匹允﹝
43

兮夫偞隅仄化P?仿奶白毛㜭仄心引仄斤丹﹝
44

端端舝

莽梁梭喳娶梗梁喝勳娶梗喳梯娶勳鳥硃娶聆喳域梗聆毛妏勻化翋平奈毛斛剕卞今六月

Recommended

More Related Content

What's hot (20)

Similar to 莽梁梭喳娶梗梁喝勳娶梗喳梯娶勳鳥硃娶聆喳域梗聆毛妏勻化翋平奈毛斛剕卞今六月 (6)

Recently uploaded (11)

莽梁梭喳娶梗梁喝勳娶梗喳梯娶勳鳥硃娶聆喳域梗聆毛妏勻化翋平奈毛斛剕卞今六月