MySQL :: MySQL 5.7 Reference Manual :: 13.1.14 CREATE TABLE Syntax
generated columnはあるカラムの値に関数を噛ませた結果をカラムとして実体化できたり、実体化せずに毎回計算できたりするカラム定義のことらしくて、
mysql57> CREATE TABLE t1 (num int, val varchar(32)) Engine = MyISAM; Query OK, 0 rows affected (0.01 sec) mysql57> INSERT INTO t1 SET num = 1, val = 'one'; Query OK, 1 row affected (0.00 sec) mysql57> SELECT * FROM t1; +-----+------+ | num | val | +-----+------+ | 1 | one | +-----+------+ 1 row in set (0.00 sec) $ od -c t1.MYD 0000000 003 \0 \n 006 \0 376 001 \0 \0 \0 003 o n e \0 \0 0000020 \0 \0 \0 \0 0000024
こんなテーブルに対して
mysql57> ALTER TABLE t1 ADD new_val varchar(96) AS (REPEAT(val, 3)) STORED; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql57> ALTER TABLE t1 ADD KEY (new_val); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0
"STORED"キーワードは演算結果を実体化してデータとして格納するオプション。STOREDオプションで作成されたgenerated columnにはインデックスを作成することができる。
mysql57> SELECT * FROM t1; +-----+------+-----------+ | num | val | new_val | +-----+------+-----------+ | 1 | one | oneoneone | +-----+------+-----------+ 1 row in set (0.00 sec) $ od -c t1.MYD 0000000 003 \0 024 \0 \0 374 001 \0 \0 \0 003 o n e \t o 0000020 n e o n e o n e 0000030 $ od -c t1.MYI .. * 0004000 \0 024 \0 001 \t o n e o n e o n e \0 \0 0004020 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 \0 * ..
一応、インデックスとしても実体化されているぽい。
mysql57> INSERT INTO t1 SET num = 2, val = 'two'; Query OK, 1 row affected (0.00 sec) mysql57> INSERT INTO t1 SET num = 3, val = 'three'; Query OK, 1 row affected (0.00 sec) mysql57> explain SELECT * FROM t1 WHERE new_val LIKE 'one%'; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t1 | NULL | range | new_val | new_val | 99 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql57> show warnings; +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `d1`.`t1`.`num` AS `num`,`d1`.`t1`.`val` AS `val`,`d1`.`t1`.`new_val` AS `new_val` from `d1`.`t1` where (`d1`.`t1`.`new_val` like 'one%') | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
極めてフツーにカラムを作って、極めてフツーに値を突っ込んで、極めてフツーに検索しているのと同じ感じ（当たり前）
ただし、generated columnからフツーの(?)カラムに対する逆関数は定義できないので、
mysql57> INSERT INTO t1 SET num = 4, new_val = 'fourfourfour'; ERROR 3105 (HY000): The value specified for generated column 'new_val' in table 't1' is not allowed.
突っ込めない。
おお、噂（？）の3000番台エラーだ。
で、このgenerated columnでやりたいのって
mysql57> create table json (id int auto_increment, col1 varchar(1000), primary key(id));
Query OK, 0 rows affected (0.05 sec)
mysql57> INSERT INTO json(id, col1) VALUES (1, '{"id":1,"Name":"Farmer grandmas","price":50000,"Conditions":["farms",15]}');
Query OK, 1 row affected (0.00 sec)
mysql57> INSERT INTO json(id, col1) VALUES (2, '{"id":2,"Name":"Worker grandmas","price":300000,"Conditions":["factories",15]}');
Query OK, 1 row affected (0.00 sec)
mysql57> INSERT INTO json(id, col1) VALUES (3, '{"id":3,"Name":"Miner grandmas","price":1000000,"Conditions":["mines",15]}');
Query OK, 1 row affected (0.02 sec)
mysql57> INSERT INTO json(id, col1) VALUES (4, '{"id":4,"Name":"Yoshiaki Yamasaki"}');
Query OK, 1 row affected (0.01 sec)
mysql57> ALTER TABLE json ADD price int unsigned AS (json_extract(col1, 'price')) STORED;
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql57> ALTER TABLE json ADD KEY (price);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql57> SELECT * FROM json;
+----+--------------------------------------------------------------------------------+---------+
| id | col1 | price |
+----+--------------------------------------------------------------------------------+---------+
| 1 | {"id":1,"Name":"Farmer grandmas","price":50000,"Conditions":["farms",15]} | 50000 |
| 2 | {"id":2,"Name":"Worker grandmas","price":300000,"Conditions":["factories",15]} | 300000 |
| 3 | {"id":3,"Name":"Miner grandmas","price":1000000,"Conditions":["mines",15]} | 1000000 |
| 4 | {"id":4,"Name":"Yoshiaki Yamasaki"} | NULL |
+----+--------------------------------------------------------------------------------+---------+
4 rows in set (0.00 sec)
mysql57> SELECT * FROM json WHERE price > 500000;
+----+----------------------------------------------------------------------------+---------+
| id | col1 | price |
+----+----------------------------------------------------------------------------+---------+
| 3 | {"id":3,"Name":"Miner grandmas","price":1000000,"Conditions":["mines",15]} | 1000000 |
+----+----------------------------------------------------------------------------+---------+
1 row in set (0.00 sec)
こーゆーことで合ってますかね？ :)
( ´-｀).oO（サンプルのJSONは 日本語JSON UDFの一番詳しいスライド から拝借した
ところでADD price int unsigned AS (..) STORED KEYってやるとPRIMARYつけてなくてもPRIMARY KEYにしたがるんだけどたぶんバグだよな。。
【2015/03/23 19:44】
とりあえずばぐれぽしてみた。
MySQL Bugs: #76450: generated column with "KEY" makes PRIMARY KEY
【2015/03/24 16:34】
もともと、カラム定義に"KEY"キーワードを与えた場合はPRIMARY KEYを作る動作なんだと教えてもらった(´･ω･｀)
KEY is normally a synonym for INDEX. The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.
http://dev.mysql.com/doc/refman/5.7/en/create-table.html
mysqldump/mysqlpumpともgenerated columnの値を含んだINSERT文が出力されてしまいますが、インポート時に回避する方法はあるのでしょうか？
返信削除( ﾟдﾟ) ﾊｯ!!
返信削除今のところなさそうなので、Feature Request挙げました！ ありがとうございます！
http://bugs.mysql.com/bug.php?id=78082