COLUMN_LIST(dyncol_blob_name);
(jlive)[test]>SELECT id, COLUMN_LIST(dyn_cols) FROM dyn_example;
+----+-----------------------------+
| id |
COLUMN_LIST(dyn_cols)
+----+-----------------------------+
|
|
|
|
|
+----+-----------------------------+
5 rows in set (0.01 sec)
(jlive)[test]>SELECT
id, COLUMN_LIST(dyn_cols) FROM dyn_example;
| id |
COLUMN_LIST(dyn_cols)
+----+--------------------------------------+
|
|
|
|
|
+----+--------------------------------------+
5 rows in set (0.00 sec)
(jlive)[test]>SELECT
id, COLUMN_LIST(dyn_cols) FROM dyn_example;
| id |
COLUMN_LIST(dyn_cols)
+----+--------------------------------------+
|
|
|
|
|
+----+--------------------------------------+
5 rows in set (0.00 sec)
(jlive)[test]>SELECT id, COLUMN_LIST(dyn_cols) FROM dyn_example;
+----+--------------------------------------+
| id |
COLUMN_LIST(dyn_cols)
+----+--------------------------------------+
|
|
|
|
|
|
+----+--------------------------------------+
6 rows in set (0.00 sec)
dynamic column查询
COLUMN_GET(dyncol_blob_name,
column_name AS type);
SELECT id,
FROM dyn_example;
+----+---------+-------+------+------+
| id |
name
+----+---------+-------+------+------+
|
|
|
|
|
|
+----+---------+-------+------+------+
6 rows in set (0.00 sec)
以JSON格式显示
COLUMN_JSON(dyncol_blob_name);
(jlive)[test]>SELECT id, COLUMN_JSON(dyn_cols) FROM dyn_example;
+----+-----------------------------------------------------------------------------------------------------------------------------+
| id |
COLUMN_JSON(dyn_cols)
+----+-----------------------------------------------------------------------------------------------------------------------------+
|
|
|
|
|
|
+----+-----------------------------------------------------------------------------------------------------------------------------+
6 rows in set (0.00 sec)
检查BLOB num列存在性
COLUMN_EXISTS(dyncol_blob_name,
column_name);
(jlive)[test]>SELECT
id, COLUMN_EXISTS(dyn_cols,
'num')
+----+--------------------------------+
| id | COLUMN_EXISTS(dyn_cols, 'num') |
+----+--------------------------------+
|
|
|
|
|
|
+----+--------------------------------+
6 rows in set (0.00 sec)
检查BLOB是否合法
COLUMN_CHECK(dyncol_blob_name);
(jlive)[test]>SELECT
id, COLUMN_CHECK(dyn_cols)
+----+------------------------+
| id | COLUMN_CHECK(dyn_cols) |
+----+------------------------+
|
|
|
|
|
|
+----+------------------------+
6 rows in set (0.00 sec)
查询嵌套值
SELECT
FROM dyn_example WHERE
+---------------------------------+---------------------------------------------------+
|
child1
+---------------------------------+---------------------------------------------------+
|
+---------------------------------+---------------------------------------------------+
1 row in set (0.00 sec)
SELECT
COLUMN_JSON(dyn_cols) FROM dyn_example WHERE
+-----------------------------------------------------------------------------------------------------------------------------+
|
COLUMN_JSON(dyn_cols)
+-----------------------------------------------------------------------------------------------------------------------------+
| {"name":"Mary","type":"parent","child1":{"eyes":"brown","name":"Sue"},"child2":{"name":"Bob","grandchild":{"name":"baby"}}} |
+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
Virtual Column
https://mariadb.com/kb/en/virtual-columns/
CREATE DATABASE IF NOT EXISTS test;
USE test;
CREATE TABLE virt_cols (
说明:
1.PERSISTENT直接存储在数据库中,VIRTUAL则每次通过计算得出,不存储在数据库中
2.只支持InnoDB,XtraDB,Aria,MyISAM
(jlive)[test]>DESC virt_cols;
+-----------+---------------------+------+-----+---------+----------------+
| Field
+-----------+---------------------+------+-----+---------+----------------+
|
id
| surname
|
givenname | varchar(64)
| uid
|
username
+-----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
INSERT INTO virt_cols (surname,givenname) VALUES
INSERT INTO virt_cols (surname,givenname,uid,username) VALUES
(jlive)[test]>SELECT * FROM virt_cols;
+----+----------------+-----------+------+----------+
| id |
surname
+----+----------------+-----------+------+----------+
|
|
|
|
|
|
|
|
+----+----------------+-----------+------+----------+
8 rows in set (0.00 sec)