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道普請便り
- 第171回 MyDumperを使ってみよう[その2]
- 第170回 MySQLのキーワードと予約語をテーブルに使用したい場合
- 第169回 pt-variable-advisorを使ってみよう
- 第168回 MyDumperを使ってみよう[その1]
- 第167回 M1搭載MacのDockerでMySQLを動かしてみる
- 第166回 Performance_schema関数
- 第165回 MySQLの圧縮いろいろ
- 第164回 admin_address,admin_portを設定しよう
- 第163回 sysbench 1.0を使ってMySQLの負荷テストをする
- 第162回 mysqlクライアントの履歴を確認してみる