The json data type is only available in MySQL versions 5.7.8 and up.
But here is how it is done:
Code:
CREATE TABLE `e_store`.`products`(
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(250) NOT NULL ,
`brand_id` INT UNSIGNED NOT NULL ,
`category_id` INT UNSIGNED NOT NULL ,
`attributes` JSON NOT NULL ,
PRIMARY KEY(`id`) ,
INDEX `CATEGORY_ID`(`category_id` ASC) ,
INDEX `BRAND_ID`(`brand_id` ASC) ,
CONSTRAINT `brand_id` FOREIGN KEY(`brand_id`) REFERENCES `e_store`.`brands`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE ,
CONSTRAINT `category_id` FOREIGN KEY(`category_id`) REFERENCES `e_store`.`categories`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE
);
/* Let's sell some televisions */
INSERT INTO `e_store`.`products`(
`name` ,
`brand_id` ,
`category_id` ,
`attributes`
)
VALUES(
'Prime' ,
'1' ,
'1' ,
'{"screen": "50 inch", "resolution": "2048 x 1152 pixels", "ports": {"hdmi": 1, "usb": 3}, "speakers": {"left": "10 watt", "right": "10 watt"}}'
);
The last line is the json being inserted into the table attributes is the json column
What did you Try? What did you Get? What did you Expect?
Joined CodeIgniter Community 2009. ( Skype: insitfx )