1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
|
--echo #
--echo # Test on dynamic columns (blob)
--echo #
create table assets (
item_name varchar(32) primary key, -- A common attribute for all items
dynamic_cols blob -- Dynamic columns will be stored here
);
INSERT INTO assets VALUES ('MariaDB T-shirt',
COLUMN_CREATE('color', 'blue', 'size', 'XL'));
INSERT INTO assets VALUES ('Thinkpad Laptop',
COLUMN_CREATE('color', 'black', 'price', 500));
SELECT item_name, COLUMN_GET(dynamic_cols, 'color' as char) AS color
FROM assets;
UPDATE assets
SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '3 years')
WHERE item_name='Thinkpad Laptop';
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
UPDATE assets
SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty', '4 years')
WHERE item_name in
(select b.item_name from assets b
where COLUMN_GET(b.dynamic_cols, 'color' as char) ='black');
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
UPDATE assets SET dynamic_cols=COLUMN_ADD(dynamic_cols, 'warranty',
(select COLUMN_GET(b.dynamic_cols, 'color' as char)
from assets b
where assets.item_name = item_name));
SELECT item_name,
COLUMN_GET(dynamic_cols, 'warranty' as char) AS color
FROM assets;
drop table assets;
--echo #
--echo # Test on fulltext columns
--echo #
CREATE TABLE ft2(copy TEXT,FULLTEXT(copy));
INSERT INTO ft2(copy) VALUES
('MySQL vs MariaDB database'),
('Oracle vs MariaDB database'),
('PostgreSQL vs MariaDB database'),
('MariaDB overview'),
('Foreign keys'),
('Primary keys'),
('Indexes'),
('Transactions'),
('Triggers');
SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('database');
update ft2 set copy = (select max(concat('mykeyword ',substr(b.copy,1,5)))
from ft2 b WHERE MATCH(b.copy) AGAINST('database'))
where MATCH(copy) AGAINST('keys');
SELECT * FROM ft2 WHERE MATCH(copy) AGAINST('mykeyword');
drop table ft2;
|
