MariaDB [(none)]> create database x;
|
MariaDB [(none)]> use x;
|
MariaDB [x]> -- create one table with a 16-byte binary column using innodb
|
MariaDB [x]> create table a(uuid binary(16), id integer) engine innodb;
|
MariaDB [x]> insert into a values(unhex("AABBCCDDEEFF00112233445566778899"), 1);
|
MariaDB [x]> insert into a values(unhex("AABBCC00DDEEFF001122334455667788"), 1);
|
MariaDB [x]> -- create second table with simple columns using columnstore
|
MariaDB [x]> create table b(id integer, val float(23)) engine columnstore;
|
MariaDB [x]> insert into b values (1, 3.5);
|
MariaDB [x]> -- verify insert
|
MariaDB [x]> select hex(uuid), id from a;
|
+----------------------------------+------+
|
| hex(uuid) | id |
|
+----------------------------------+------+
|
| AABBCCDDEEFF00112233445566778899 | 1 |
|
| AABBCC00DDEEFF001122334466778899 | 1 |
|
+----------------------------------+------+
|
MariaDB [x]> -- verify insert
|
MariaDB [x]> select id, val from b;
|
+------+------+
|
| id | val |
|
+------+------+
|
| 1 | 3.5 |
|
+------+------+
|
MariaDB [x]> -- join two tables on id column
|
MariaDB [x]> select a.id, hex(a.uuid), b.val from b join a on b.id=a.id;
|
+------+--------------+------+
|
| id | hex(a.uuid) | val |
|
+------+--------------+------+
|
| 1 | AABBCCDDEEFF | 3.5 |
|
| 1 | AABBCC | 3.5 |
|
+------+--------------+------+
|
^-----^------- binary values truncated at first 0x00
|
|
Commands only:
|
|
create database x;
|
use x;
|
-- create one table with a 16-byte binary column using innodb
|
create table a(uuid binary(16), id integer) engine innodb;
|
insert into a values(unhex("AABBCCDDEEFF00112233445566778899"), 1);
|
insert into a values(unhex("AABBCC00DDEEFF001122334455667788"), 1);
|
-- create second table with simple columns using columnstore
|
create table b(id integer, val float(23)) engine columnstore;
|
insert into b values (1, 3.5);
|
-- verify insert
|
select hex(uuid), id from a;
|
-- verify insert
|
select id, val from b;
|
-- join two tables on id column
|
select a.id, hex(a.uuid), b.val from b join a on b.id=a.id;
|