Dynamic Column

Dynamic columns in MariaDB allow you to store data within a single column of a table in a flexible format similar to a NoSQL database. Dynamic columns can be used to store different types of data, such as strings, integers, or nested structures.

1. Enable dynamic columns: First, ensure that dynamic columns are enabled in your MariaDB installation. Dynamic columns are available in MariaDB by default, but you may need to ensure that the dynamic_columns plugin is installed and enabled in your configuration file.

   Make sure the dynamic_columns plugin is enabled in the my.cnf (MariaDB configuration) file.

[mysqld]
   dynamic_columns = ON

2. Create table: Creates a table with dynamic columns. Examples include:

CREATE TABLE my_table (
       id INT AUTO_INCREMENT PRIMARY KEY,
       dynamic_data DYNAMIC;
   );

3. Inserting data: You can use the INSERT INTO statement to insert data into a dynamic column and specify the dynamic column as a JSON-like string.

INSERT INTO my_table (dynamic_data)
   VALUES ('{"name": "John", "age": 30}');

4. Query data: You can query data from dynamic columns using a variety of features provided by MariaDB. For example, to search for the “Name” field in a dynamic column:

SELECT DYNAMIC_JSON_VALUE(dynamic_data, '$.name') AS name FROM my_table;

Features

1. Flexibility: Dynamic columns offer more flexibility because they allow you to store different types of data, including strings, integers, or nested structures, all within a single column.
2. Schema-less column: Dynamic columns do not have a schema. This means that you can change the data structure without changing the table schema.
3. Complex data: Useful when you need to store complex and heterogeneous data whose structure may vary from row to row.
4. Query: Querying dynamic columns often involves using special functions such as DYNAMIC_JSON_VALUE. Extracting and querying specific data elements can be more complex.

JSON Column

JSON columns in MariaDB allow you to store data in JSON format in a structured way, making it easier to work with JSON data within SQL.

1. Create table with JSON column: Creates a table with JSON column.

CREATE TABLE my_json_table (
       id INT AUTO_INCREMENT PRIMARY KEY,
       json_data JSON;
   );

2. Data Insertion: You can insert JSON data directly into a JSON column using the INSERT INTO statement.

INSERT INTO my_json_table (json_data)
   VALUES ('{"name": "Alice", "age": 25}');

3. Query data: You can query data in JSON columns using various JSON functions available in MariaDB. For example, to search for the “Name” field in a JSON column:

SELECT json_data->>'$.name' AS name FROM my_json_table;

Features

1. Structured storage: JSON columns provide structured storage for JSON formatted data. Makes your job easier by ensuring that your JSON data conforms to a specific structure.
2. Data validation: JSON columns validate JSON data upon insertion to ensure that it conforms to the specified JSON schema.
3. Query: The JSON column provides built-in JSON functions that simplify querying and manipulating JSON data, such as >> or >. These features allow you to easily extract specific elements from JSON data.
4. Use Case: JSON columns are ideal when you have JSON data with a consistent structure and want to leverage JSON-specific features for querying and manipulation.

Function

#return jsonOBJ::인자값으로 인스턴스 key-value OBJ 생성
JSON_OBJECT('name', 'John', 'age', 30)

#return jsonArray::인자값으로 인스턴스array 생성
JSON_ARRAY('apple', 'banana', 'cherry')

#return JSONOBJ::지정한 JSONPATH이하의 문서만 조회
JSON_EXTRACT('{"name": "Alice", "age": 25}', '$.name')
#JSON_EXTRACT(REFERRER_NOS, '$[1]')

#return STRING::remove "
JSON_UNQUOTE('"Alice"')

#return jsonOBJ::지정한 JSONPATH를 지정값으로 변경하여 조회 (없을시 추가됨)
JSON_SET('{"name": "Alice", "age": 25}', '$.age', 26)

#return jsonOBJ::지정한 JSONPATH를 지정값으로 변경하여 조회 (없을시 추가하지 않음)
JSON_REPLACE('{"name": "Alice", "age": 25}', '$.age', 26)

#return jsonOBJ::지정한 JSONPATH에 지정값을 추가하여 조회
JSON_INSERT('{"name": "Alice", "age": 25}', '$.city', 'New York')

#return jsonOBJ::지정한 JSONPATH를 제외하여 조회
JSON_REMOVE('{"name": "Alice", "age": 25}', '$.age')

#return 'all': JsonArray, 'one': JosnPath::값으로 json경로 검색::첫번째 결과 or 모든 결과
JSON_SEARCH('{"name": "Alice", "age": 25}', $one_or_all, 'Alice')

#return boolean::contains
JSON_CONTAINS('["apple", "banana", "cherry"]', '"banana"')

#return boolean::패스 존재여부 확인. 2번째 인자 'one', 'all'은 차이가 없음. 잘못된 함수 설계
JSON_CONTAINS_PATH('{"name": "Alice", "age": 25}', $one_or_all, '$.name')

Comparison

Dynamic columns provide more flexibility but may require additional processing, while JSON columns provide structured storage and built-in JSON functions for querying and manipulation. Choose the one that best suits your data modeling needs.

Dynamic columns are more flexible, but require additional effort in queries and may not enforce strict data structures. JSON columns, on the other hand, provide structured storage, built-in validation, and convenient JSON-specific features for querying. Choosing one over the other depends on your specific data and use case. If you have consistent data with a known structure, JSON columns are usually a better choice. If you need more flexibility or have complex and diverse data structures, dynamic columns may be a better fit.