婚纱摄影网站html模板,互联网软件公司排名,营销型网站建设一般要多少钱,微信代码小程序数据库设计通常需要满足一定的范式要求#xff0c;其中主键更是最基本的要求。不过#xff0c;数据库管理系统却允许我们创建没有主键的表。这样的表在 MySQL 中会带来查询性能低下、复制延迟甚至无法实现高可用配置等问题。
为此#xff0c;MySQL 8.0.30 版本引入了一个新…数据库设计通常需要满足一定的范式要求其中主键更是最基本的要求。不过数据库管理系统却允许我们创建没有主键的表。这样的表在 MySQL 中会带来查询性能低下、复制延迟甚至无法实现高可用配置等问题。
为此MySQL 8.0.30 版本引入了一个新的功能叫做不可见主键Generated Invisible Primary Keys它可以自动为没有显式指定主键的 InnoDB 表创建一个不可见的主键。
不可见主键
MySQL 通过系统变量 sql_generate_invisible_primary_key 控制是否启用 GIPK 特性该变量的默认设置为 OFF。
以下示例创建了两个表都没有指定主键。
mysql SELECT sql_generate_invisible_primary_key;
--------------------------------------
| sql_generate_invisible_primary_key |
--------------------------------------
| 0 |
--------------------------------------
1 row in set (0.00 sec)mysql CREATE TABLE auto_0 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.02 sec)mysql SET sql_generate_invisible_primary_keyON;
Query OK, 0 rows affected (0.00 sec)mysql SELECT sql_generate_invisible_primary_key;
--------------------------------------
| sql_generate_invisible_primary_key |
--------------------------------------
| 1 |
--------------------------------------
1 row in set (0.00 sec)mysql CREATE TABLE auto_1 (c1 VARCHAR(50), c2 INT);
Query OK, 0 rows affected (0.04 sec)创建 auto_0 表时sql_generate_invisible_primary_key 设置为 OFF创建 auto_1 表时sql_generate_invisible_primary_key 设置为 ON。
使用 SHOW CREATE TABLE 语句查看两个表的区别
mysql SHOW CREATE TABLE auto_0\G
*************************** 1. row ***************************Table: auto_0
Create Table: CREATE TABLE auto_0 (c1 varchar(50) DEFAULT NULL,c2 int DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)mysql SHOW CREATE TABLE auto_1\G
*************************** 1. row ***************************Table: auto_1
Create Table: CREATE TABLE auto_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)
) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.00 sec)MySQL 自动为 auto_1 创建了一个不可见字段 my_row_id并且将其设置为主键。 不可见字段也叫做隐藏字段是 MySQL 8.0.23 版本增加的新功能。作为不可见字段my_row_id 不会出现在 SELECT * 或者 TABLE 语句的结果中查询该字段必须显式指定它的名字。 这种情况下系统默认增加的不可见主键字段名称固定为 my_row_id因此我们不能在创建表时指定其他字段名为 my_row_id除非同时将其指定为主键。
修改属性
当我们启用了 GIPK 功能时不可见主键 my_row_id 不能使用 ALTER TABLE 语句进行修改只能设置它的可见属性。以下命令将 auto_1 表的不可见主键设置为可见字段
mysql ALTER TABLE auto_1 ALTER COLUMN my_row_id SET VISIBLE;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql SHOW CREATE TABLE auto_1\G
*************************** 1. row ***************************Table: auto_1
Create Table: CREATE TABLE auto_1 (my_row_id bigint unsigned NOT NULL AUTO_INCREMENT,c1 varchar(50) DEFAULT NULL,c2 int DEFAULT NULL,PRIMARY KEY (my_row_id)
) ENGINEInnoDB AUTO_INCREMENT4 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
1 row in set (0.01 sec)当我们启用了 GIPK 功能时如果删除不可见主键会导致以下任意情况发生都不允许删除不可见主键
该表没有主键删除主键而保留主键字段。
另外GIPK 功能只支持 InnoDB 存储引擎当我们使用 ALTER TABLE 语句修改这种表的存储引擎时仍然会保留字段和主键约束但是它会变成普通的主键字段。
字典信息
默认情况下SHOW CREATE TABLE、SHOW COLUMNS 以及 SHOW INDEX 命令都会显示不可见主键信息。同时information_schema 数据库中的 COLUMNS 和 STATISTICS 表中也包含了不可见主键字段。这一行为可以通过系统变量 show_gipk_in_create_table_and_information_schema 进行控制默认值为 ON。
mysql SELECT show_gipk_in_create_table_and_information_schema;
----------------------------------------------------
| show_gipk_in_create_table_and_information_schema |
----------------------------------------------------
| 1 |
----------------------------------------------------
1 row in set (0.00 sec)此时我们查询系统表 COLUMNS 可以返回 auto_1 中的不可见主键 my_row_id
mysql SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY- FROM INFORMATION_SCHEMA.COLUMNS- WHERE TABLE_NAME auto_1;
------------------------------------------------------
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
------------------------------------------------------
| my_row_id | 1 | bigint | PRI |
| c1 | 2 | varchar | |
| c2 | 3 | int | |
------------------------------------------------------
3 rows in set (0.01 sec)如果将系统变量 show_gipk_in_create_table_and_information_schema 设置为 OFF再次查询系统表 COLUMNS不会显示不可见主键字段
mysql SET show_gipk_in_create_table_and_information_schema OFF;
Query OK, 0 rows affected (0.00 sec)mysql SELECT show_gipk_in_create_table_and_information_schema;
----------------------------------------------------
| show_gipk_in_create_table_and_information_schema |
----------------------------------------------------
| 0 |
----------------------------------------------------
1 row in set (0.00 sec)mysql SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE, COLUMN_KEY- FROM INFORMATION_SCHEMA.COLUMNS- WHERE TABLE_NAME auto_1;
------------------------------------------------------
| COLUMN_NAME | ORDINAL_POSITION | DATA_TYPE | COLUMN_KEY |
------------------------------------------------------
| c1 | 2 | varchar | |
| c2 | 3 | int | |
------------------------------------------------------
2 rows in set (0.00 sec)复制与备份
系统变量 sql_generate_invisible_primary_key 的配置不会被复制复制应用线程会忽略该变量。这就意味着源节点的设置不会对副本产生影响。从 MySQL 8.0.32 版本开始我们可以在 CHANGE REPLICATION SOURCE TO 语句使用 REQUIRE_TABLE_PRIMARY_KEY_CHECK GENERATE 选项设置副本节点启用 GIPK 功能为指定复制渠道中的那些没有主键的表自动增加不可见主键。
对于 CREATE TABLE … SELECT 语句GIPK 支持基于行的复制选项此时二进制日志中包含了 GIPK 定义可以正确地复制。如果是基于语句的复制CREATE TABLE … SELECT 不支持 sql_generate_invisible_primary_key ON。
如果启用了 GIPK 功能使用 mysqldump 备份或者还原数据时可以通过 --skip-generated-invisible-primary-key 选项排除 GIPK 信息。
