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