【MySQL】MYSQL5.7 Create/modify virtual columns

Updated to 1 hour ago

Create a table:

CREATE TABLE `Table name` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL COMMENT 'name',
  `attach` json DEFAULT NULL COMMENT 'JSON Field',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Create a virtual column:

alter table table name add column virtual column namevarchar(255) GENERATED ALWAYS AS (json_extract(`attach`,'$.virtual column name')) VIRTUAL;

Modify the virtual column:

alter table table name modify column virtual column namevarchar(255) GENERATED ALWAYS AS (json_extract(`attach`,'$.virtual column name')) VIRTUAL;