A table with generated virtual column from Json, get error(3102) when insert data using procedure
Posted by: Joeson Joe
Date: December 01, 2016 03:08AM

A table with generated virtual column from Json, get error(3102) when insert data using procedure. however, I using sql DML(NSERT INTO player_js(player_js.content)VALUES('{"nam":"hehe","lv":1,"car":2,"mny":7}')) to insert data is ok. tell me why~???

the sql:
-- -------------------------------------------------------------------------
USE GameDB;
DROP TABLE if EXISTS player_js;
-- create table
CREATE TABLE `player_js` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`content` json DEFAULT NULL,
`name` varchar(100) COLLATE utf8mb4_bin GENERATED ALWAYS AS (json_extract(content,'$.nam')) VIRTUAL,
`level` int(11) GENERATED ALWAYS AS (json_extract(content,'$.lv')) VIRTUAL,
`career` int(11) GENERATED ALWAYS AS (json_extract(content,'$.car')) VIRTUAL,
`money` int(11) GENERATED ALWAYS AS (json_extract(content,'$.mny')) VIRTUAL,
PRIMARY KEY (`id`),
KEY `idx_level` (`level`),
KEY `idx_money` (`money`)
) ENGINE=InnoDB AUTO_INCREMENT=5005 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

DROP PROCEDURE IF EXISTS SavePlayer_js;
delimiter $$
-- create procedure
CREATE PROCEDURE `SavePlayer_js`(IN content JSON)
BEGIN
INSERT INTO player_js(player_js.content)VALUES(content);
END$$
delimiter;

CALL SavePlayer_js('{"nam":"hehe","lv":1,"car":2,"mny":7}'); -- get error(3102):Expression of generated column 'name' contains a disallowed function

Options: ReplyQuote


Subject
Views
Written By
Posted
A table with generated virtual column from Json, get error(3102) when insert data using procedure
2537
December 01, 2016 03:08AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.