数据库学习案例20240317-怎么强制 MySQL 表带有主键？

1 前言概述

通常情况下，在公司中DBA会制定数据库开发policy，其中有一条就是创建的表必须他有主键（复制和部分高可用集群要求；性能要求）。但是有些公司并没有SQL上线审核平台，并且给用户授予的权限相对较大（具有创建表权限），此时可以用什么方法来规避这种情况的出现呢？

通过打开sql_require_primary_key来强制要求用户创建表时必须包含主键，否则创建失败。

set global sql_require_primary_key = 1;


07:14:  [mgr]> set global sql_require_primary_key=0;
Query OK, 0 rows affected (0.02 sec)

07:14:  [mgr]> show variables like '%sql_require_primary_key%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| sql_require_primary_key | ON    |
+-------------------------+-------+
1 row in set (0.01 sec)


需要重启
echo "sql_require_primary_key=1">>/etc/my.cnf

在设置参数后，我们试图创建无主键的表，

create table t1(id bigint);

查看现有环境参数如下：

[root@router log]# mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.36 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

07:07:  [(none)]> show variables like '%sql_require_primary_key%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| sql_require_primary_key | OFF   |
+-------------------------+-------+
1 row in set (0.12 sec)

07:07:  [(none)]> show variables like '%sql_generate_invisible_primary_key %';
Empty set (0.01 sec)

07:08:  [(none)]> show variables like '%sql_generate_invisible_primary_key%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| sql_generate_invisible_primary_key | OFF   |
+------------------------------------+-------+
1 row in set (0.00 sec)

07:08:  [(none)]>

需要注意什么：

临时表也需要主键 create table as select ...会报错，临时表也需要主键。

2 自动生成隐式主键

set global sql_generate_invisible_primary_key = 1;
show create table t3;

-- result
CREATE TABLE `t3` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` bigint DEFAULT NULL,
  `t_name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

但是，在创建无主键表时，如果已经有了my_row_id，就会引发Failed to generate invisible primary key. Column 'my_row_id' already exists.的错误。

3 注意点

使用sql_require_primary_key需要 MySQL 版本在8.0.13以上。
使用sql_generate_invisible_primary_key需要MySQL 版本在8.0.30以上。
有一点需要注意的是，在测试的时候可能在set global sql_generate_invisible_primary_key = 1 或者set global sql_require_primary_key = 1后没有起作用，这是由于没有重新连接，当前连接还是使用的会话变量。或者设置会话变量测试。