Mysql 怎么产生隐藏主键和还要不要学MySQL

AustinDatabases

发布于 2024-03-21 15:51:11

700

发布于 2024-03-21 15:51:11

MySQL 要不要学的这个问题，回答是一定要学，继续学，哪怕不用。实际上最近有人已经问了这个问题了，还有人问ORACLE 要不要学的问题，我觉得这个些提问题的人，很奇怪，如果有觉得你有更值得要学的数据库，马上要用的数据库可以去学，没有必要问，ORACLE,MYSQL要不要学，你问我就会告诉你，学一定要学。

提出这些问题的人有一个思路，就是我未来用的到用不到这些知识，比如我公司现在主流的数据库不是MYSQL 是POLARDB 和 PostgreSQL 当然这两个我要学，但ORACLE MySQL一点学习的必要都没有,我是认为太绝对了，你那天从单位毕业了，其他JD要求会 ORACLE MYSQL你是不是就傻眼了，或者说MYSQL 和ORACLE 在数据库业界的地位和原理，哪里不值得你去学习，增加自己的对于数据库原理的理解，增加你数据库职业的底蕴，所以别问我，问就是学。

最近我写PG和MYSQL的量的确是少了，1是工作忙，事情多 2 的确经历在PG 和 POLARDB 的上面更多了，但MYSQL 会继续的。今天我们来说说MySQL的 invisible primary key.

首先invisible primary key是MySQL 8.030推出的GIPK 的功能，主要的原因在一些程序设计中，的确忽略了主键的设计，但基于mysql的原理和推行的 innodb cluster 的部分对于表的主键的强需求，MySQL一直是不允许有没有主键的情况，基于这些原因，所有推出了 invisible primary key .

在调整完参数重启数据库，我们在查看系统的状态

mysql> SELECT @@sql_generate_invisible_primary_key;
+--------------------------------------+
| @@sql_generate_invisible_primary_key |
+--------------------------------------+
|                                    1 |
+--------------------------------------+
1 row in set (0.00 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.01 sec)

那么我们需要做一个实验，在系统中我们创建 2个表，一个在sql_generate_invisible_primary_key=ON 的情况下，一个在sql_generate_invisible_primary_key=OFF 在建立一个表我们查看表的情况。

mysql> show create table test;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                        |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `age` smallint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

上面是标准的方式来进行mysql表建立的语句，下面我们将primary key 去掉，然后我们看相关的产生的表是什么状态。

下图中我们可以看到，如果在原有的语句中，来建立没有主键的表是不能建立，会报错，因为MYSQL会自动建立主键并且是自增的，所以不允许没有主键同时还建立自增字段，直接在建立的时候就报错。

Failed to generate invisible primary key. Auto-increment column already exists.

mysql> create table test_1 (id int AUTO_INCREMENT,name varchar(20),age smallint);
ERROR 4109 (HY000): Failed to generate invisible primary key. Auto-increment column already exists.
mysql> 
mysql> 
mysql> create table test_1 (id int,name varchar(20),age smallint);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test_1;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                                                                   |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_1 | CREATE TABLE `test_1` (
  `my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` smallint DEFAULT NULL,
  PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

那么我们在建立的时候，去掉的自增此时我们的里面在建立语句的时候可以看到自动建立了一个 my_row_id的主键，并且为自增的和无符号来进行，以bigint 作为字段的类型。

mysql> SET sql_generate_invisible_primary_key=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test_1 (id int,name varchar(20),age smallint);
ERROR 1050 (42S01): Table 'test_1' already exists
mysql> 
mysql> 
mysql> create table test_2 (id int,name varchar(20),age smallint);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test_2;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                         |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_2 | CREATE TABLE `test_2` (
  `id` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` smallint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在会话层关闭选项，后在建立表与我们之前的预想的状态一致，没有新建表没有任何的主键。在我们对于三个表进行 select 的情况下，对于中间的test_1 表没有看到 my_row_id 的字段，这里这个字段的确是对于表隐藏了。

mysql> select * from test;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | session |   35 |
+----+---------+------+
1 row in set (0.00 sec)

mysql> select * from test_1;
+------+---------+------+
| id   | name    | age  |
+------+---------+------+
| NULL | session |   35 |
+------+---------+------+
1 row in set (0.00 sec)

mysql> select * from test_2;
+------+---------+------+
| id   | name    | age  |
+------+---------+------+
| NULL | session |   35 |
+------+---------+------+
1 row in set (0.00 sec)

这里通过alter语句来对表隐藏的字段进行显示操作，和隐式操作。

mysql> alter table test_1 alter column my_row_id set visible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_1;
+-----------+------+---------+------+
| my_row_id | id   | name    | age  |
+-----------+------+---------+------+
|         1 | NULL | session |   35 |
+-----------+------+---------+------+
1 row in set (0.00 sec)

mysql> alter table test_1 alter column my_row_id set invisible;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_1;
+------+---------+------+
| id   | name    | age  |
+------+---------+------+
| NULL | session |   35 |
+------+---------+------+
1 row in set (0.00 sec)


mysql> select table_CATALOG,table_name,column_name from columns where column_name = 'my_row_id';
+---------------+------------+-------------+
| TABLE_CATALOG | TABLE_NAME | COLUMN_NAME |
+---------------+------------+-------------+
| def           | test_1     | my_row_id   |
+---------------+------------+-------------+
1 row in set (0.01 sec)

mysql>

相关在mysql中的column中显示添加的字段也是OK 的，同时也可以通过变量将这个字段进行隐藏，但这里觉得没有这个必要，对这部分知识也就滤过了。另外还有一个知识点是关于复制中相关的语句是否会被传递到从库，这里默认这些语句是不会被复制到从库的，复制应用线程会绕过这部分的命令，在8.032后的版本可以通过，REQUIRE_TABLE_PRIMARY_KEY_CHECK = GENERATE 的方式，使副本在给定复制通道中为没有主键的复制表添加生成的不可见主键。


mysql> select table_CATALOG,table_name,column_name from columns where column_name = 'my_row_id';
+---------------+------------+-------------+
| TABLE_CATALOG | TABLE_NAME | COLUMN_NAME |
+---------------+------------+-------------+
| def           | test_1     | my_row_id   |
+---------------+------------+-------------+
1 row in set (0.01 sec)

mysql>

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

原始发表：2024-03-19，如有侵权请联系?cloudcommunity@tencent.com 删除

原理