Dynamic Columns(JSON)

1. 테이블 생성

CREATE TABLE `json` ( `json` BLOB NULL ) COLLATE='utf8_general_ci' ENGINE=InoDB;

2. 데이터 삽입(COLUMN_CREATE)

INSERT INTO json VALUES (COLUMN_CREATE('age', 22, 'name', 'kim')); INSERT INTO json VALUES (COLUMN_CREATE('age', 11, 'name', 'heo')); INSERT INTO json VALUES (COLUMN_CREATE('color', 'blue', 'size', 'XL')); INSERT INTO json VALUES  (COLUMN_CREATE('color', 'black', 'price', 500));

3. 데이터 가져오기(COLUMN_JSON, COLUMN_LIST, COLUMN_GET)

SELECT COLUMN_JSON(json), COLUMN_LIST(json), COLUMN_GET(json, 'color' AS char) AS color FROM json  -------------------------------------------------------------------------------------------------------------- (결과) COLUMN_JSON(json)  COLUMN_LIST(json) color {"size":"XL","color":"blue"} `size`,`color`  "blue"  {"color":"black","price":500}  `color`,`price` "black"  {"age":22,"name":"kim"}  `age`,`name`  NULL  {"age":11,"name":"heo"}  `age`,`name` NULL

4. COLUMN_EXISTS, COLUMN_CHECK

SELECT COLUMN_EXISTS(json, 'color'), COLUMN_CHECK(json) FROM json; -------------------------------------------------------------------------------------------------------------- (결과)  COLUMN_EXISTS(json, 'color')  COLUMN_CHECK(json)  0 1   0 1   1 1   1 1

5. COLUMN_DELETE

UPDATE json SET json = COLUMN_DELETE(json, 'age', 'name'); SELECT COLUMN_JSON(json) FROM json; ------------------------------------------------------------------------------------------------------------- (결과) COLUMN_JSON(json) {"size":"XL","color":"blue"} {"color":"black","price":500} {} {}