sql_require_primary_key参数详解

最新推荐文章于 2024-10-09 23:00:28 发布

Liu三变

最新推荐文章于 2024-10-09 23:00:28 发布

阅读量4.3k

点赞数 3

分类专栏： MySQL

本文链接：https://blog.csdn.net/justaiiit/article/details/109033790

版权

MySQL 专栏收录该内容

13 篇文章 1 订阅

订阅专栏

MySQL 8.0.13版本新引进一个参数，该参数非常实用，要求表有主键，减少了DBA对于代码的审计。官方文件信息：sql_require_primary_key: Whether tables must have a primary key. Added in MySQL 8.0.13.

实验

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like '%primary%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| group_replication_single_primary_mode | OFF   |
| sql_require_primary_key               | ON    |
+---------------------------------------+-------+
2 rows in set (0.01 sec)

mysql> use crmsb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table kk(id int,name varchar(20));
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.
mysql> 
mysql> 
mysql> create table kk(id int primary key,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql>

延伸

MySQL 8.0.20版本又新增了一个参数：REQUIRE_TABLE_PRIMARY_KEY_CHECK，在配置主从复制的过程中可以指定为{ON|OFF|STREAM}，官方说法如下：REQUIRE_TABLE_PRIMARY_KEY_CHECK (available as of MySQL 8.0.20) enables a replica to select
its own policy for primary key checks. When the option is set to ON for a replication channel, the replica
always uses the value ON for the sql_require_primary_key system variable in replication operations,
requiring a primary key. When the option is set to OFF, the replica always uses the value OFF for the
sql_require_primary_key system variable in replication operations, so that a primary key is never
required, even if the source required one. When the REQUIRE_TABLE_PRIMARY_KEY_CHECK option is
set to STREAM, which is the default, the replica uses whatever value is replicated from the source for each
transaction.
STREAM:根据主库配置情况制定主键强制检查