diff options
Diffstat (limited to 'mysql-test/suite/innodb/r/innodb-virtual-columns.result')
| -rw-r--r-- | mysql-test/suite/innodb/r/innodb-virtual-columns.result | 353 |
1 files changed, 353 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb-virtual-columns.result b/mysql-test/suite/innodb/r/innodb-virtual-columns.result new file mode 100644 index 00000000..b97eaac7 --- /dev/null +++ b/mysql-test/suite/innodb/r/innodb-virtual-columns.result @@ -0,0 +1,353 @@ +CREATE TABLE IF NOT EXISTS gso_grad_supr ( +term char(4) NOT NULL DEFAULT '', +uw_id int(8) UNSIGNED NOT NULL DEFAULT 0, +plan varchar(10) NOT NULL DEFAULT '', +wdraw_rsn varchar(4) NOT NULL DEFAULT '', +admit_term char(4) NOT NULL DEFAULT '', +CONSTRAINT gso_grad_supr_pky PRIMARY KEY (uw_id, term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1280 Name 'gso_grad_supr_pky' ignored for PRIMARY key. +INSERT INTO `gso_grad_supr` VALUES ('1031',2,'CSM','','1009'); +INSERT INTO `gso_grad_supr` VALUES ('1035',2,'CSM','ACAD','1009'); +CREATE TABLE IF NOT EXISTS grad_degree ( +student_id int(8) UNSIGNED NOT NULL, +plan varchar(10) NOT NULL, +admit_term char(4) NOT NULL, +wdraw_rsn varchar(4) NOT NULL DEFAULT '', +ofis_deg_status varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed' + ELSE 'Not Completed' + END) VIRTUAL, +deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', +deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', +CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key. +SHOW CREATE TABLE grad_degree; +Table Create Table +grad_degree CREATE TABLE `grad_degree` ( + `student_id` int(8) unsigned NOT NULL, + `plan` varchar(10) NOT NULL, + `admit_term` char(4) NOT NULL, + `wdraw_rsn` varchar(4) NOT NULL DEFAULT '', + `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL, + `deg_start_term` char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', + `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term', + PRIMARY KEY (`student_id`,`plan`,`admit_term`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci +CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn); +CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term); +INSERT IGNORE grad_degree ( +student_id, +plan, +admit_term, +wdraw_rsn, +deg_start_term, +deg_as_of_term +) +SELECT +ggs.uw_id AS c_student_id, +ggs.plan, +ggs.admit_term, +ggs.wdraw_rsn, +IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, +ggs.term AS c_as_of_term +FROM gso_grad_supr AS ggs +LEFT OUTER JOIN +grad_degree AS gd +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +WHERE +ggs.term = 1031 AND +gd.student_id IS NULL +; +UPDATE grad_degree AS gd +INNER JOIN +gso_grad_supr AS ggs +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +SET +gd.wdraw_rsn = ggs.wdraw_rsn, +gd.deg_as_of_term = 1035 +WHERE +gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND +ggs.term = 1035 +; +drop table grad_degree; +CREATE TABLE IF NOT EXISTS grad_degree ( +student_id int(8) UNSIGNED NOT NULL, +plan varchar(10) NOT NULL, +admit_term char(4) NOT NULL, +wdraw_rsn varchar(4) NOT NULL DEFAULT '', +ofis_deg_status varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed' + ELSE 'Not Completed' + END) VIRTUAL, +ofis_deg_status2 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress2' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2' + ELSE 'Not Completed2' + END) VIRTUAL, +ofis_deg_status3 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress3' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3' + ELSE 'Not Completed3' + END) VIRTUAL, +ofis_deg_status4 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress4' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4' + ELSE 'Not Completed4' + END) VIRTUAL, +ofis_deg_status5 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress5' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5' + ELSE 'Not Completed5' + END) VIRTUAL, +ofis_deg_status6 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress6' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6' + ELSE 'Not Completed6' + END) VIRTUAL, +ofis_deg_status7 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress7' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7' + ELSE 'Not Completed7' + END) VIRTUAL, +ofis_deg_status8 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress8' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8' + ELSE 'Not Completed8' + END) VIRTUAL, +deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', +deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', +CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key. +SHOW CREATE TABLE grad_degree; +Table Create Table +grad_degree CREATE TABLE `grad_degree` ( + `student_id` int(8) unsigned NOT NULL, + `plan` varchar(10) NOT NULL, + `admit_term` char(4) NOT NULL, + `wdraw_rsn` varchar(4) NOT NULL DEFAULT '', + `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL, + `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed2' else 'Not Completed2' end) VIRTUAL, + `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed3' else 'Not Completed3' end) VIRTUAL, + `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed4' else 'Not Completed4' end) VIRTUAL, + `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed5' else 'Not Completed5' end) VIRTUAL, + `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed6' else 'Not Completed6' end) VIRTUAL, + `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed7' else 'Not Completed7' end) VIRTUAL, + `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed8' else 'Not Completed8' end) VIRTUAL, + `deg_start_term` char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', + `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term', + PRIMARY KEY (`student_id`,`plan`,`admit_term`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci +CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn); +CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term); +INSERT IGNORE grad_degree ( +student_id, +plan, +admit_term, +wdraw_rsn, +deg_start_term, +deg_as_of_term +) +SELECT +ggs.uw_id AS c_student_id, +ggs.plan, +ggs.admit_term, +ggs.wdraw_rsn, +IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, +ggs.term AS c_as_of_term +FROM gso_grad_supr AS ggs +LEFT OUTER JOIN +grad_degree AS gd +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +WHERE +ggs.term = 1031 AND +gd.student_id IS NULL +; +UPDATE grad_degree AS gd +INNER JOIN +gso_grad_supr AS ggs +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +SET +gd.wdraw_rsn = ggs.wdraw_rsn, +gd.deg_as_of_term = 1035 +WHERE +gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND +ggs.term = 1035 +; +ALTER TABLE grad_degree DROP INDEX grad_degree_wdraw_rsn_ndx; +ALTER TABLE grad_degree DROP COLUMN deg_start_term; +SHOW CREATE TABLE grad_degree; +Table Create Table +grad_degree CREATE TABLE `grad_degree` ( + `student_id` int(8) unsigned NOT NULL, + `plan` varchar(10) NOT NULL, + `admit_term` char(4) NOT NULL, + `wdraw_rsn` varchar(4) NOT NULL DEFAULT '', + `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL, + `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed2' else 'Not Completed2' end) VIRTUAL, + `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed3' else 'Not Completed3' end) VIRTUAL, + `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed4' else 'Not Completed4' end) VIRTUAL, + `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed5' else 'Not Completed5' end) VIRTUAL, + `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed6' else 'Not Completed6' end) VIRTUAL, + `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed7' else 'Not Completed7' end) VIRTUAL, + `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed8' else 'Not Completed8' end) VIRTUAL, + `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term', + PRIMARY KEY (`student_id`,`plan`,`admit_term`), + KEY `grad_degree_as_of_term_ndx` (`deg_as_of_term`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci +DROP TABLE grad_degree; +CREATE TABLE IF NOT EXISTS grad_degree ( +student_id int(8) UNSIGNED NOT NULL, +plan varchar(10) NOT NULL, +admit_term char(4) NOT NULL, +wdraw_rsn varchar(4) NOT NULL DEFAULT '', +ofis_deg_status varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed' + ELSE 'Not Completed' + END) VIRTUAL, +ofis_deg_status2 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress2' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed2' + ELSE 'Not Completed2' + END) VIRTUAL, +ofis_deg_status3 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress3' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed3' + ELSE 'Not Completed3' + END) VIRTUAL, +ofis_deg_status4 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress4' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed4' + ELSE 'Not Completed4' + END) VIRTUAL, +ofis_deg_status5 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress5' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed5' + ELSE 'Not Completed5' + END) VIRTUAL, +ofis_deg_status6 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress6' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed6' + ELSE 'Not Completed6' + END) VIRTUAL, +ofis_deg_status7 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress7' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed7' + ELSE 'Not Completed7' + END) VIRTUAL, +ofis_deg_status8 varchar(15) AS ( +CASE +WHEN wdraw_rsn = '' THEN 'In progress8' + WHEN wdraw_rsn = 'DCMP' OR wdraw_rsn = 'TRDC' THEN 'Completed8' + ELSE 'Not Completed8' + END) VIRTUAL, +deg_start_term char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', +deg_as_of_term char(4) NOT NULL COMMENT 'In most cases also end term', +CONSTRAINT grad_degree_stu_plan_admit_pky PRIMARY KEY (student_id, plan, admit_term) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +Warnings: +Warning 1280 Name 'grad_degree_stu_plan_admit_pky' ignored for PRIMARY key. +SHOW CREATE TABLE grad_degree; +Table Create Table +grad_degree CREATE TABLE `grad_degree` ( + `student_id` int(8) unsigned NOT NULL, + `plan` varchar(10) NOT NULL, + `admit_term` char(4) NOT NULL, + `wdraw_rsn` varchar(4) NOT NULL DEFAULT '', + `ofis_deg_status` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed' else 'Not Completed' end) VIRTUAL, + `ofis_deg_status2` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress2' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed2' else 'Not Completed2' end) VIRTUAL, + `ofis_deg_status3` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress3' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed3' else 'Not Completed3' end) VIRTUAL, + `ofis_deg_status4` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress4' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed4' else 'Not Completed4' end) VIRTUAL, + `ofis_deg_status5` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress5' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed5' else 'Not Completed5' end) VIRTUAL, + `ofis_deg_status6` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress6' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed6' else 'Not Completed6' end) VIRTUAL, + `ofis_deg_status7` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress7' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed7' else 'Not Completed7' end) VIRTUAL, + `ofis_deg_status8` varchar(15) GENERATED ALWAYS AS (case when `wdraw_rsn` = '' then 'In progress8' when `wdraw_rsn` = 'DCMP' or `wdraw_rsn` = 'TRDC' then 'Completed8' else 'Not Completed8' end) VIRTUAL, + `deg_start_term` char(4) NOT NULL DEFAULT '' COMMENT 'Educated guess at the beginning of the data', + `deg_as_of_term` char(4) NOT NULL COMMENT 'In most cases also end term', + PRIMARY KEY (`student_id`,`plan`,`admit_term`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci +CREATE INDEX grad_degree_wdraw_rsn_ndx ON grad_degree (wdraw_rsn); +ALTER TABLE grad_degree DROP COLUMN ofis_deg_status2, DROP COLUMN ofis_deg_status3, +DROP COLUMN ofis_deg_status4, DROP COLUMN ofis_deg_status5, DROP COLUMN ofis_deg_status6, +DROP COLUMN ofis_deg_status7, DROP COLUMN ofis_deg_status8; +CREATE INDEX grad_degree_as_of_term_ndx ON grad_degree (deg_as_of_term); +INSERT IGNORE grad_degree ( +student_id, +plan, +admit_term, +wdraw_rsn, +deg_start_term, +deg_as_of_term +) +SELECT +ggs.uw_id AS c_student_id, +ggs.plan, +ggs.admit_term, +ggs.wdraw_rsn, +IF( (SELECT COUNT(*) FROM grad_degree AS gd WHERE gd.student_id = ggs.uw_id AND gd.admit_term = ggs.admit_term) > 0, ggs.term, ggs.admit_term ) AS c_deg_start_term, +ggs.term AS c_as_of_term +FROM gso_grad_supr AS ggs +LEFT OUTER JOIN +grad_degree AS gd +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +WHERE +ggs.term = 1031 AND +gd.student_id IS NULL +; +UPDATE grad_degree AS gd +INNER JOIN +gso_grad_supr AS ggs +ON ( gd.student_id = ggs.uw_id AND gd.plan = ggs.plan AND gd.admit_term = ggs.admit_term ) +SET +gd.wdraw_rsn = ggs.wdraw_rsn, +gd.deg_as_of_term = 1035 +WHERE +gd.wdraw_rsn NOT IN ('DCMP', 'TRDC') AND +ggs.term = 1035 +; +select * from grad_degree; +student_id plan admit_term wdraw_rsn ofis_deg_status deg_start_term deg_as_of_term +2 CSM 1009 ACAD Not Completed 1009 1035 +select * from gso_grad_supr; +term uw_id plan wdraw_rsn admit_term +1031 2 CSM 1009 +1035 2 CSM ACAD 1009 +drop table grad_degree; +drop table gso_grad_supr; +CREATE TABLE t1 (a INT, b CHAR(12), c INT AS (a) VIRTUAL, FULLTEXT KEY(b)) ENGINE=InnoDB; +INSERT INTO t1 (a,b) VALUES (1,'foo'); +SELECT * FROM t1; +a b c +1 foo 1 +DROP TABLE t1; +CREATE TABLE t1 (a INT, b CHAR(12), c INT AS (a) VIRTUAL) ENGINE=InnoDB; +INSERT INTO t1 (a,b) VALUES (1,'foo'); +ALTER TABLE t1 ADD FULLTEXT KEY(b); +SELECT * FROM t1; +a b c +1 foo 1 +DROP TABLE t1; |