Table structure: v_date is a virtual column
CREATE TABLE `test` (
`json` TEXT NULL,
`date` DATETIME NULL DEFAULT NULL,
`v_date` DATE AS (json_extract(`json`,"$.date")) VIRTUAL
)
COMMENT="\r\n"
COLLATE="utf8mb4_general_ci"
ENGINE=InnoDB
;
insert:
INSERT INTO test (json) VALUES ("{"date":"2019-01-21 00:00:00"}");error report:
ERROR 3156 (22018): Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column json_extract at row 1tried various formats:
INSERT INTO `test`.`test_virtual_column` (`json`) VALUES ("{"date":"20191221"}");
/* SQL3156:Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column json_extract at row 1 */
INSERT INTO `test`.`test_virtual_column` (`json`) VALUES ("{"date":20191221}");
/* SQL3156:Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column
json_extract at row 1 */how do I save data of type Date to virtual columns?