那些MySQL 8.0中的隐藏特性
作者：Corrado Pandiani 本文来源：Percona 官网博客
爱可生开源社区出品。
隐藏列
生成的隐藏主键
隐藏索引
隐藏列
mysql> CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
article TEXT,
PRIMARY KEY(id)
);
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO articles(article) VALUES
("This is first article"),
("This is second article"),
("This is third article");
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM articles;
+----+---------------------------+------------------------------+
| id | ts | article |
+----+---------------------------+------------------------------+
| 1 | 2023-07-28 13:15:03 | This is first article |
| 2 | 2023-07-28 13:15:03 | This is second article |
| 3 | 2023-07-28 13:15:03 | This is third article |
+----+---------------------------+------------------------------+
ts 列之后向表中添加一个新的字段 title。为了避免我们的应用程序因 SELECT * 和新添加的中间列等情况失效，我们必须将 title 列创建为 INVISIBLE。mysql> ALTER TABLE articles ADD COLUMN title VARCHAR(200) INVISIBLE AFTER ts;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
为新列提供一些值：
mysql> UPDATE articles SET title='Title 1' WHERE id=1;
UPDATE articles SET title='Title 2' WHERE id=2;
UPDATE articles SET title='Title 3' WHERE id=3;
现在看看表架构：
CREATE TABLE `articles` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`ts` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`title` varchar(200) DEFAULT NULL /*!80023 INVISIBLE */,
`article` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
mysql> SELECT * FROM articles;
+----+---------------------------+------------------------------+
| id | ts | article |
+----+---------------------------+------------------------------+
| 1 | 2023-07-28 13:15:03 | This is first article |
| 2 | 2023-07-28 13:15:03 | This is second article |
| 3 | 2023-07-28 13:15:03 | This is third article |
+----+---------------------------+------------------------------+
你看，该列没有返回。这允许 schema 改变后查询不会失败。
如果你想看 title，你必须明确寻址该字段：
mysql> SELECT id, ts, title, article FROM articles;
+----+---------------------------+-----------+------------------------------+
| id | ts | title | article |
+----+---------------------------+-----------+------------------------------+
| 1 | 2023-07-28 13:15:03 | Title 1 | This is first article |
| 2 | 2023-07-28 13:15:03 | Title 2 | This is second article |
| 3 | 2023-07-28 13:15:03 | Title 3 | This is third article |
+----+---------------------------+-----------+------------------------------+
使用以下 DDL 将列设置为可见：
mysql> ALTER TABLE articles MODIFY COLUMN title varchar(200) VISIBLE;
information_schema 中可用，INVISIBLE/VISIBLE 关键字在 binlog 中保留，以便正确复制所有更改。生成的隐藏主键
mysql> SET [PERSIST] sql_generate_invisible_primary_key=ON;
现在在不指定显式主键的情况下创建一个表：
mysql> CREATE TABLE customer(name VARCHAR(50));
Query OK, 0 rows affected (0.03 sec)
检查模式：
mysql> SHOW CREATE TABLE customerG
*************************** 1. row ***************************
Table: customer
Create Table: CREATE TABLE `customer` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
注意：
GIPK 的名称始终为
my_row_id。您不能在表中有相同名称的列。GIPK 的数据类型始终为使用 AUTO_INCREMENT 的 BIGINT UNSIGNED。
mysql> INSERT INTO customer VALUES('Tim'),('Rob'),('Bob');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT my_row_id, name FROM customer;
+--------------+-------+
| my_row_id | name |
+--------------+-------+
| 1 | Tim |
| 2 | Rob |
| 3 | Bob |
+--------------+-------+
3 rows in set (0.00 sec)
mysql> SELECT my_row_id, name FROM customer WHERE my_row_id=2;
+--------------+-------+
| my_row_id | name |
+--------------+-------+
| 2 | Rob |
+--------------+-------+
1 row in set (0.00 sec)
SELECT *，主键不会被返回：mysql> SELECT * FROM customer WHERE my_row_id=2;
+-------+
| name |
+-------+
| Rob |
+-------+
在某些时候，您最终可以决定使其可见，并在需要时更改名称:
mysql> ALTER TABLE customer MODIFY `my_row_id` bigint unsigned not null auto_increment VISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE customerG
*************************** 1. row ***************************
Table: customer
Create Table: CREATE TABLE `customer` (
`my_row_id` bigint unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`my_row_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
隐藏索引
ALTER TABLE mytable ALTER INDEX my_idx INVISIBLE;
ALTER TABLE mytable ALTER INDEX my_idx VISIBLE;
IGNORE INDEX() 索引提示，但在这种情况下，您可能会被迫在应用程序代码中的许多查询上添加索引提示。将索引设置为不可见将允许您在很短的时间内开始测试查询。并且您可以随时轻松地将其设置回可见，而不会丢失任何更新。注意：
主键（PRIMARY Key）不能隐藏
UNIQUE 索引可以隐藏，但仍会执行唯一性检查
有关索引不可见性的信息在
information_schema中可用索引不可见性会被正确复制
总结
END
点这里 ↓↓↓ 记得 关注✔ 标星⭐ 哦
微信扫码关注该文公众号作者
戳这里提交新闻线索和高质量文章给我们。
来源: qq
点击查看作者最近其他文章