TL;DR
- sql_require_primary_key サーバー変数をONにすると、PRIMARY KEYのないテーブルを作ろうとした時にエラーにできる。
- セッションスコープとグローバルスコープと両方あるやつで、実効値はセッションスコープなので注意。
- ただし、
SET SESSION ..でも一般ユーザーでは値を変更することはできない（sql_log_binとかもそうですね）
- 超便利だ！！ 秘伝のタレに入れる時は
looseプレフィックスとかもいいと思うよ！！
取り敢えず基本的な使い方として、0(OFF)と1(ON)の時の動作の違い。
mysql80 9> SELECT @@sql_require_primary_key;
+---------------------------+
| @@sql_require_primary_key |
+---------------------------+
| 0 |
+---------------------------+
1 row in set (0.00 sec)
mysql80 9> CREATE TABLE t1 (num int);
Query OK, 0 rows affected (0.04 sec)
mysql80 9> SET @@session.sql_require_primary_key= 1;
Query OK, 0 rows affected (0.00 sec)
mysql80 9> SELECT @@sql_require_primary_key;
+---------------------------+
| @@sql_require_primary_key |
+---------------------------+
| 1 |
+---------------------------+
1 row in set (0.00 sec)
mysql80 9> CREATE TABLE t2 (num int);
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
おお、マニュアル通り。
Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting. にちょっとニヤリとするｗｗｗ
しかし、セッションとグローバルと両方持ってるってことは一般ユーザーで値書き換えられちゃうんでは？ と思ったけど、それができない類の変数になっていた（昔から
sql_log_bin とかもセッション変数だけど一般ユーザーは変更できないのでそれと同じところを通ってるのであろう）mysql80 10> SHOW GRANTS;
+--------------------------------------------------+
| Grants for yoku0825@% |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `yoku0825`@`%` |
| GRANT ALL PRIVILEGES ON `d1`.* TO `yoku0825`@`%` |
+--------------------------------------------------+
2 rows in set (0.00 sec)
mysql80 10> SET @@session.sql_require_primary_key= 1;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation
これで勝手にPK必須が剥がれる心配もなく。
ちなみに、これがONの時にどれくらいPKを強制されるかというと
ちなみに、これがONの時にどれくらいPKを強制されるかというと
mysql80 11> OPTIMIZE TABLE t2;
+-------+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-------+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| d1.t2 | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| d1.t2 | optimize | error | Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting. |
| d1.t2 | optimize | status | Operation failed |
+-------+----------+----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)
mysql80 11> ALTER TABLE t2 ADD val varchar(32);
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
mysql80 11> ALTER TABLE t2 ADD KEY(num);
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
mysql80 11> INSERT INTO t2 VALUES (2);
Query OK, 1 row affected (0.01 sec)
ALTER TABLE 関連（ OPTIMIZE TABLE もInnoDBでは ALTER TABLE にマッピングされるので同類とする）は全滅。Sql_cmd_alter_table::execute -> mysql_alter_table -> create_table_impl -> mysql_prepare_create_table でエラーになるので、 ALTER TABLE だけが引っ掛かりそうだしDMLは影響を受けなさそう。
ちなみにストレージエンジン問わないので、ONにしたままだとCSVストレージエンジンは身動きが取れない(*ﾉ∀ﾉ)
mysql80 12> CREATE TABLE t3 (num int) Engine= CSV;
ERROR 3750 (HY000): Unable to create a table without PK, when system variable 'sql_require_primary_key' is set. Add a PK to the table or unset this variable to avoid this message. Note that tables without PK can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
mysql80 12> CREATE TABLE t3 (num int, PRIMARY KEY(num)) Engine= CSV;ERROR 1069 (42000): Too many keys specified; max 0 keys allowed
