MySQL道普請便り
第158回 Invisible Columnsの使いどころ
MySQL 8.
なお，
Invisible columnsのあるテーブルの作成
Invisibleなカラムのあるテーブルを作成するには，
mysql> CREATE TABLE t1(id int auto_increment primary key, name1 varchar(256), name2 varchar(256) INVISIBLE);
Query OK, 0 rows affected (0.02 sec)
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`name1` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
`name2` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL /*!80023 INVISIBLE */,
PRIMARY KEY (`id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)
mysql> ALTER TABLE t1 ALTER COLUMN name1 SET INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
実際に，
mysql> INSERT INTO t1(id, name1, name2) VALUES(1, "a", "AA"),(2, "b", "BB"), (3, "c", "CC"); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t1; +----+ | id | +----+ | 1 | | 2 | | 3 | +----+ 3 rows in set (0.00 sec) mysql> SELECT id, name1, name2 FROM t1; +----+-------+-------+ | id | name1 | name2 | +----+-------+-------+ | 1 | a | AA | | 2 | b | BB | | 3 | c | CC | +----+-------+-------+ 3 rows in set (0.00 sec)
SELECT *で取得したときはid列しか取得できませんでしたが，
詳細な動きを確認する
Invisible Columnsの基本的な機能は理解したところで，
すべてのカラムをinvisibleにすることは可能？
すべてのカラムをinvisibleにすることはできません。全カラムをinvisibleにしようとするとエラーが発生します。
mysql> ALTER TABLE t1 ALTER COLUMN id SET INVISIBLE; ERROR 4028 (HY000): A table must have at least one visible column.
デフォルト値やNot Null制約のあるカラムにInvisible Columnsは設定できる？
mysql> CREATE TABLE t1(id int auto_increment primary key, name1 varchar(256) not null INVISIBLE, name2 varchar(256) default "ZZZ" INVISIBLE); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t1(id, name2) values (1, "AA"); ERROR 1364 (HY000): Field 'name1' doesn't have a default value mysql> INSERT INTO t1(id, name1) values (1, "a"); Query OK, 1 row affected (0.00 sec) mysql> SELECT id, name1, name2 FROM t1; +----+-------+-------+ | id | name1 | name2 | +----+-------+-------+ | 1 | a | ZZZ | +----+-------+-------+ 1 row in set (0.00 sec)
設定することはできます。デフォルト値がある場合は，
Invisible ColumnsにINDEXをつけることは可能か？
InvisibleなカラムにINDEXを利用することは可能です。さらに，
mysql> CREATE TABLE t1(id int auto_increment primary key invisible, name1 varchar(256), name2 varchar(256) INVISIBLE); Query OK, 0 rows affected (0.03 sec)
GENERATED COLUMNSやCHECK制約にInvisible Columnsは有効か？
GENERATED COLUMNSやCHECK制約にもInvisible Columnsは有効になります。更新しようとした行がCHECK制約を満たさない場合も，
mysql> CREATE TABLE t1(a int, b int, c int as (a + b) VIRTUAL invisible, d int as (a - b) STORED check(d < 100) invisible); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t1(a, b) VALUES (200, 10); ERROR 3819 (HY000): Check constraint 't1_chk_1' is violated.
CREATE TABLE AS SELECT … と INSERT INTO … SELECT
では，
mysql> CREATE TABLE t1(id int auto_increment primary key, name1 varchar(256), name2 varchar(256) INVISIBLE); mysql> INSERT INTO t1(id, name1, name2) VALUES(1, "a", "AA"),(2, "b", "BB"), (3, "c", "CC");
まずCREATE TABLE AS SELECT … を*を指定して作成してみると，
mysql> CREATE TABLE cp_t1 as SELECT * FROM t1;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE cp_t1\G
*************************** 1. row ***************************
Table: cp_t1
Create Table: CREATE TABLE `cp_t1` (
`id` int NOT NULL DEFAULT '0',
`name1` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.01 sec)
続いてInvisible Columnsのカラム名も指定して実施してみます。Invisible Columnsを指定した場合は，
mysql> CREATE TABLE cp_t1 as SELECT id, name1, name2 FROM t1;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
> SHOW CREATE TABLE cp_t1\G
*************************** 1. row ***************************
Table: cp_t1
Create Table: CREATE TABLE `cp_t1` (
`id` int NOT NULL DEFAULT '0',
`name1` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL,
`name2` varchar(256) COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
1 row in set (0.00 sec)
INSERT INTO … SELECT * FROM の方はどうでしょうか。
mysql> CREATE TABLE cp_t2(id int auto_increment primary key, name1 varchar(256), name2 varchar(256) INVISIBLE); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO cp_t2 SELECT * FROM t1; Query OK, 3 rows affected (0.01 sec) mysql> SELECT id, name1, name2 FROM cp_t2; +----+-------+-------+ | id | name1 | name2 | +----+-------+-------+ | 1 | a | NULL | | 2 | b | NULL | | 3 | c | NULL | +----+-------+-------+ 3 rows in set (0.00 sec)
Invisible Columnsの列は参照されずにコピーされて，
mysql> INSERT INTO cp_t2(id, name1, name2) SELECT id, name1, name2 FROM t1; Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT id, name1, name2 FROM cp_t2; +----+-------+-------+ | id | name1 | name2 | +----+-------+-------+ | 1 | a | AA | | 2 | b | BB | | 3 | c | CC | +----+-------+-------+ 3 rows in set (0.00 sec)
VIEWについて
VIEWを作成する際にSELECT * FROMを利用した場合は，SELECT * FROMで指定してViewを作成するときに，
mysql> CREATE view view_t1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW CREATE VIEW view_t1\G
*************************** 1. row ***************************
View: view_t1
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t1` AS select `t1`.`id` AS `id`,`t1`.`name1` AS `name1` from `t1`
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
Invisible Columnsの確認
Invisible Columnsについては，
まとめ
今回はInvisible Columnsの機能について，
たとえば，
バックナンバー
MySQL道普請便り
- 第160回 pt-show-grantsを使ってユーザアカウントを移行する
- 第159回 Query Rewrite Pluginsを使ってクエリを実行時に書き換えてみる
- 第158回 Invisible Columnsの使いどころ
- 第157回 MySQLのデフォルトcollationの注意点
- 第156回 pt-archiverを使って部分的にテーブルから情報を取得する
- 第155回 エラーログのフィルタリング
- 第154回 スロークエリログに出力される項目とlog_slow_extra
- 第153回 mysqlpumpを使ってバックアップを取ってみる
- 第152回 パスワード検証必須ポリシーとログイン失敗時のアカウントの一時ロック
- 第151回 知っておくとちょっと得するperformance_schemaのテーブル