MySQL 为了让你的表里有一个主键是操碎了心

wubx

发布于 2022-12-03 12:31:33

9940

发布于 2022-12-03 12:31:33

文章被收录于专栏：MySQLBeginnerMySQLBeginner

在 MySQL 的开发规范中都会明确写着：MySQL InnoDB 表必须有主键，主键的选择建议：添加一个自增列作为主键，每一行的值删除后一般不会重用。但实质上，业务开发中，还是会遇到 InnoDB 表无主键无索引的情况。

首先来看一下 MySQL InnoDB 对于主键的选择：

1. 显式声明的 primary key
2. 如果没有显式的声明主键，第一个非空的 unique key 可以成为主键
3. 如果前两者都没有，MySQL 会生成一个内部隐式的 6 bytes 的隐式 row_id 作为主键

再来看看 MySQL 主键现在的用处：

1. 首先来讲 MySQL InnoDB 的主键设计影响写入，更新等。
2. 好的主键设计也可以加速复制
3. 好的主键设计也可以加速 MGR

关于 MySQL 主键，推荐阅读：https://www.percona.com/blog/2018/07/26/tuning-innodb-primary-keys/

在这里我和大家盘点一下 MySQL 为了让每个表有一个主键都做了什么事情：

1. 可以启用参数：sql_requite_primary_key 该参数是 MySQL 8.0.13 引入，如果该参数开启后，创建的表必须有主键。对于创建的新表如果没有主键将会报错。
2. MySQL 8.0 为了更友好的支持 java 狂魔们所有的类实例时都会生成一个 uuid ，把 uuid 当成表的主键的问题，推出：
- • uuid_to_bin 把 32 位字符串压缩到 varbinary(16)
- • bin_to_uuid 把 varbinary(16) 转成 32 位的 uuid 用来应对 uuid 的优化。
3. 到 MySQL 8.0.30 又推出：sql_generate_invisible_primary_key 不可见主键。直到这个功能的出现，可以说是给 MySQL 表没主键这个事情，搞上一个句号。下面给一个简单例子：-- 创建表结构

   set sql_generate_invisible_primary_key=ON;
   CREATE TABLE `wubx_1` (
   `c1` varchar(50) DEFAULT NULL,
   `c2` int DEFAULT NULL
   ) ENGINE=InnoDB;

   -- 查看表结构

   CREATE TABLE `wubx_1` (
     `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
     `c1` varchar(50) DEFAULT NULL,
     `c2` int DEFAULT NULL,
     PRIMARY KEY (`my_row_id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci


   -- 插入一条数据
   MySQL [wubx]> insert into wubx_1 values('datbend',1);
   Query OK, 1 row affected (0.001 sec)

   -- 读取数据
   MySQL [wubx]> select * from wubx_1;
   +---------+------+
   | c1      | c2   |
   +---------+------+
   | datbend |    1 |
   +---------+------+
   1 row in set (0.000 sec)

   -- 读取隐式主键
   MySQL [wubx]> select my_row_id, c1, c2 from wubx_1;
   +-----------+---------+------+
   | my_row_id | c1      | c2   |
   +-----------+---------+------+
   |         1 | datbend |    1 |
   +-----------+---------+------+
   1 row in set (0.000 sec)

   -- 使用隐式主键删除数据
   MySQL [wubx]> delete from wubx_1 where my_row_id=1;
   Query OK, 1 row affected (0.000 sec)

   MySQL [wubx]> select * from wubx_1;
   Empty set (0.000 sec)

   -- 再次写入数据
   MySQL [wubx]> insert into wubx_1 values('datbend',2);
   Query OK, 1 row affected (0.000 sec)

   -- 读取数据
   MySQL [wubx]> select * from wubx_1;
   +---------+------+
   | c1      | c2   |
   +---------+------+
   | datbend |    2 |
   +---------+------+
   1 row in set (0.000 sec)

   MySQL [wubx]> select my_row_id, c1,c2 from wubx_1;
   +-----------+---------+------+
   | my_row_id | c1      | c2   |
   +-----------+---------+------+
   |         2 | datbend |    2 |
   +-----------+---------+------+
   1 row in set (0.000 sec)

这里面有一些限制

1. 隐式主键的列名只能是： my_row_id
2. 不能删除只有一个 my_row_id 隐式主键，除非同时指定其它主键
3. 在主从复制中，从库设置 sql_generate_invisible_primary_key 为 on , 但主库没有设置，创建的表也不会自动添加 my_row_id

对于 GIPK 隐式主键的使用场景：

对于一些业务确实没有声明主键，现在就不用改业务，直接创建一个隐式主键就 Ok 了。

可以说到这里 MySQL 也是给主键这个事情操碎了心。如果大家以后不要在 MySQL 主键这个事情上掉坑里了。

有了 GIPK 我也不怕传统业务开发中的无主键的表了 :)

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2022-08-29，如有侵权请联系 cloudcommunity@tencent.com 删除

分布式