MariaDB 책을 보다 Dynamic Column 이란는게 있어 테스트 한번 해봤음
/**
MariaDB Dynamic Column Test
func: 지원하는 함수
column_create
column_add
column_get
column_delete
column_list
column_join
column_exists
column_check
Type:
지원되는 타입
Type Description
BINARY[(N)] A variable-length binary string
CHAR[(N)] A variable-length string
DATE A 3-byte date
DATETIME[(D)] A 9-byte date and time. Microseconds are supported
DECIMAL[(M[,D])] A variable-length binary decimal
INTEGER A variable-length signed integer, up to 64 bits in length
SIGNED [INTEGER] A variable-length signed integer, up to 64 bits in length
TIME[(D)] A 6-byte time. Microseconds are supported and it may be negative
UNSIGNED [INTEGER] A variable-length unsigned integer, up to 64 bits in length
*/
--
-- 01. Test Table Create
--
drop database if exists test;
create database test;
drop table if exists tbl_dyn_col_test;
CREATE TABLE `test`.`tbl_dyn_col_test`
(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
`dyn_cols` BLOB comment '동적 칼럼'
) engine = innodb default charset=utf8 comment='동적칼럼 테스트 테이블'
;
--
-- 02. Test Data Insert
--
INSERT INTO tbl_dyn_col_test (dyn_cols)
VALUES
(
COLUMN_CREATE (
'name',
't-shirt',
'color',
'blue' AS CHAR,
'size',
'XL' AS CHAR
)
),
(
COLUMN_CREATE (
'name',
't-shirt',
'color',
'blue' AS CHAR,
'size',
'L' AS CHAR
)
),
(
COLUMN_CREATE (
'name',
't-shirt',
'color',
'black' AS CHAR,
'size',
'M' AS CHAR
)
),
(
COLUMN_CREATE (
'name',
'flashlight',
'color',
'black' AS CHAR,
'size',
'AAA' AS CHAR,
'num',
2 AS INT
)
),
(
COLUMN_CREATE ('name', 'shovel', 'length', '5')
);
--
-- 02.1 결과 검증
--
SELECT
id,
column_list (dyn_cols)
FROM
tbl_dyn_col_test;
id column_list(dyn_cols)
------ -----------------------------
1 `name`,`size`,`color`
2 `name`,`size`,`color`
3 `name`,`size`,`color`
4 `num`,`name`,`size`,`color`
5 `name`,`length` ]
--
-- 03.칼럼 추가
--
UPDATE
tbl_dyn_col_test
SET
dyn_cols = COLUMN_ADD (dyn_cols, 'name', 'torch')
WHERE COLUMN_GET (dyn_cols, 'name' AS CHAR) = 'flashlight';
UPDATE
tbl_dyn_col_test
SET
dyn_cols = COLUMN_ADD (dyn_cols, 'length', 6)
WHERE COLUMN_GET (dyn_cols, 'name' AS CHAR) = 'torch';
id column_list (dyn_cols)
------ --------------------------------------
1 `name`,`size`,`color`
2 `name`,`size`,`color`
3 `name`,`size`,`color`
4 `num`,`name`,`size`,`color`,`length`
5 `name`,`length`
--
-- 04.칼럼 삭제
--
UPDATE
tbl_dyn_col_test
SET
dyn_cols = COLUMN_DELETE (dyn_cols, 'length')
WHERE COLUMN_GET (dyn_cols, 'name' AS CHAR) = 'shovel';
id column_list (dyn_cols)
------ --------------------------------------
1 `name`,`size`,`color`
2 `name`,`size`,`color`
3 `name`,`size`,`color`
4 `num`,`name`,`size`,`color`,`length`
5 `name`
--
-- 05.칼럼 조회
--
SELECT
id,
COLUMN_GET (dyn_cols, 'name' AS CHAR) AS 'name',
COLUMN_GET (dyn_cols, 'color' AS CHAR) AS 'color',
COLUMN_GET (dyn_cols, 'size' AS CHAR) AS 'size',
COLUMN_GET (dyn_cols, 'num' AS INT) AS 'num'
FROM
tbl_dyn_col_test;
id name color size num
------ ------- ------ ------ --------
1 t-shirt blue XL (NULL)
2 t-shirt blue L (NULL)
3 t-shirt black M (NULL)
4 torch black AAA 2
5 shovel (NULL) (NULL) (NULL)
--
-- 05.2 Json 형식
--
SELECT
id,
COLUMN_JSON (dyn_cols)
FROM
tbl_dyn_col_test;
id COLUMN_JSON (dyn_cols)
------ ------------------------------------------------------------------
1 {"name":"t-shirt","size":"XL","color":"blue"}
2 {"name":"t-shirt","size":"L","color":"blue"}
3 {"name":"t-shirt","size":"M","color":"black"}
4 {"num":2,"name":"torch","size":"AAA","color":"black","length":6}
5 {"name":"shovel"}
--
-- 05.3 칼럼 존재 여부 확인
--
SELECT
id,
COLUMN_EXISTS (dyn_cols, 'num')
FROM
tbl_dyn_col_test;
id COLUMN_EXISTS (dyn_cols, 'num')
------ ---------------------------------
1 0
2 0
3 0
4 1
5 0
SELECT
id,
COLUMN_CHECK (dyn_cols)
FROM
tbl_dyn_col_test;
id COLUMN_CHECK (dyn_cols)
------ -------------------------
1 1
2 1
3 1
4 1
5 1
--
-- 06. 내부 동적 칼럼
--
INSERT INTO tbl_dyn_col_test (dyn_cols)
VALUES
(
COLUMN_CREATE (
'type',
'parent',
'name',
'Mary',
'child1',
COLUMN_CREATE ('name', 'Sue', 'eyes', 'brown'),
'child2',
COLUMN_CREATE (
'name',
'Bob',
'grandchild',
COLUMN_CREATE ('name', 'baby')
)
)
);
select
id,
column_list(dyn_cols)
from
tbl_dyn_col_test
;
id COLUMN_list(dyn_cols)
------ --------------------------------------
1 `name`,`size`,`color`
2 `name`,`size`,`color`
3 `name`,`size`,`color`
4 `num`,`name`,`size`,`color`,`length`
5 `name`
6 `name`,`type`,`child1`,`child2`
--
-- 06.2 조회 방법
--
SELECT
COLUMN_GET (dyn_cols, 'child1' AS CHAR) AS 'child1',
COLUMN_GET (dyn_cols, 'child2' AS CHAR) AS 'child2'
FROM
tbl_dyn_col_test
WHERE COLUMN_GET (dyn_cols, 'type' AS CHAR) = 'parent';
child1 child2
------------------------------- ---------------------------------------------------
SELECT
COLUMN_JSON (dyn_cols)
FROM
tbl_dyn_col_test
WHERE COLUMN_GET (dyn_cols, 'type' AS CHAR) = 'parent';
COLUMN_JSON (dyn_cols)
-----------------------------------------------------------------------------------------------------------------------------
{"name":"Mary","type":"parent","child1":{"eyes":"brown","name":"Sue"},"child2":{"name":"Bob","grandchild":{"name":"baby"}}}
