MariaDB 使用CONNECT存储引擎

MariaDB 使用CONNECT存储引擎

环境：

CentOS 7.1 x64

mariadb-10.1.13 x64

一.安装CONNECT存储引擎

ln -s /lib64/libodbc.so.2.0.0 /lib64/libodbc.so.1

注意:ha_connect.so依赖libodbc.so.1(unixODBC)提供，但CentOS7的版本为2，版本不符，因此在安装的时候会报libodbc.so.1找不到，通常高版本的都会兼容低版本的，所以做了软链就可以解决问题

INSTALL SONAME 'ha_connect';

SHOW ENGINES;

SHOW PLUGINS;

+-------------------------------+----------+--------------------+---------------+---------+

63 rows in set (0.00 sec)

二.创建CONNECT表

CREATE TABLE test_data (

path varchar(256) NOT NULL flag=1,

filename varchar(256) NOT NULL flag=2,

filesize double(12,0) NOT NULL flag=5

) ENGINE=CONNECT DEFAULT CHARSET=latin1

TABLE_TYPE=DIR FILE_NAME='*.frm'

OPTION_LIST='subdir=1';

TABLE_TYPE可以有CSV, XML, INI, ODBC, MYSQL, DIR

Flag Number Information

1 Path
2 File name
3 File type
4 File attributes
5 File size
6 Last write-access date
7 Last read-access date
8 File creation date

MariaDB [isfdb]> SELECT * FROM test_data;

+-----------------------------------------------------------+---------------------+----------+

| path | filename | filesize |

+-----------------------------------------------------------+---------------------+----------+

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | authors | 1974 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | award_cats | 1090 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | award_types | 1323 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | awards | 2281 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | bad_images | 964 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | canonical_author | 2484 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | cleanup | 1543 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | directory | 1012 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | emails | 1001 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | history | 1268 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | languages | 2075 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | license_keys | 1484 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | magazine | 1145 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | metadata | 578 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | missing_author_urls | 1048 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | mw_user | 3054 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | mw_user_groups | 1458 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | notes | 965 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | pseudonyms | 1969 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | pub_content | 2011 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | pub_series | 1555 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | publishers | 1541 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | notes_tokudb | 965 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | pubs | 3302 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | reference | 1093 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | series | 1607 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | submissions | 4660 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | tag_mapping | 2483 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | tags | 990 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | title_awards | 1964 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | title_relationships | 3009 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | trans_legal_names | 1015 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | trans_pub_series | 1024 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | trans_publisher | 1021 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | user_languages | 2001 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | user_preferences | 2434 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | user_sites | 2001 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | verification | 3045 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | votes | 1992 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | webpages | 1192 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | websites | 1030 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | authors_tokudb | 2980 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | titles | 4213 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | pub_authors | 1962 |

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | test_data | 1092 |

+-----------------------------------------------------------+---------------------+----------+

45 rows in set (0.01 sec)

MariaDB [isfdb]> SELECT path,COUNT(*),SUM(filesize) FROM test_data GROUP BY path;

+-----------------------------------------------------------+----------+---------------+

| path | COUNT(*) | SUM(filesize) |

+-----------------------------------------------------------+----------+---------------+

| /opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | 45 | 80898 |

+-----------------------------------------------------------+----------+---------------+

1 row in set (0.00 sec)

MariaDB [isfdb]> DROP TABLE test_data;

Query OK, 0 rows affected (0.01 sec)

三.读写CSV

1.导出CSV

SELECT author_id, author_canonical, author_legalname,

author_birthplace, author_birthdate, author_deathdate

INTO OUTFILE '/tmp/authors.csv'

FIELDS TERMINATED BY ',' ENCLOSED BY '"'

FROM authors ORDER BY author_id LIMIT 100;

2.创建CSV类型的表

CREATE TABLE authors_csv (

author_id int(11) NOT NULL,

author_canonical varchar(1024) NOT NULL,

author_legalname varchar(1024) NOT NULL,

author_birthplace varchar(1024) NOT NULL,

author_birthdate varchar(10),

author_deathdate varchar(10)

) ENGINE=CONNECT

TABLE_TYPE='CSV'

FILE_NAME='/tmp/authors.csv'

SEP_CHAR=',' QCHAR='"' QUOTED=1;

CREATE TABLE authors_csv2 (

author_id int(11) NOT NULL,

author_birthdate varchar(10) NOT NULL FLAG=5,

author_birthplace varchar(1024) NOT NULL FLAG=4,

author_canonical varchar(1024) NOT NULL FLAG=2

) ENGINE=CONNECT DEFAULT CHARSET=utf8

TABLE_TYPE='CSV'

FILE_NAME='/tmp/authors_csv.CSV'

SEP_CHAR=',' QCHAR='"' QUOTED=1;

3.插入记录

INSERT authors_csv VALUES (

101,"Fake Author",

"Author, Fake",

"Charlotte, North Carolina, USA",

"1970-01-01",""), (

102,"Really Fake Author",

"Author, Really Fake",

"St. Paul, Minnesota, USA",

"1969-12-31","");

4.直接修改/tmp/authors.csv

echo '103,"Fake","Fake","Fake, USA","1970-04-01", ' >>/tmp/authors.csv

5.查询

MariaDB [isfdb]> SELECT * FROM authors_csv WHERE author_id >= 100;

+-----------+--------------------+---------------------+---------------------------------+------------------+------------------+

+-----------+--------------------+---------------------+---------------------------------+------------------+------------------+

| 100 | Iain M. Banks | Banks, Iain Menzies | Dunfermline, Fife, Scotland, UK | 1954-02-16 | 2013-06-09 |

+-----------+--------------------+---------------------+---------------------------------+------------------+------------------+

4 rows in set (0.01 sec)

四.读写XML

1.建表

CREATE TABLE authors_xml (

author_id int,

author_canonical varchar(1024),

author_legalname varchar(1024),

author_birthplace varchar(1024),

author_birthdate char(10),

author_deathdate char(10),

note_id int,

author_wikipedia varchar(1024),

author_views int,

author_imdb varchar(1024),

author_marque int,

author_image varchar(1024),

author_annualviews int,

author_lastname varchar(1024), author_language int

) ENGINE=CONNECT TABLE_TYPE=XML FILE_NAME='/tmp/isfdb-001.xml'

TABNAME='resultset'

OPTION_LIST='rownode=row,colnode=field,coltype=HTML'

;

2.插入记录

INSERT authors_xml VALUES (

101,"Terry Pratchett","Pratchett, Terry",

"Beaconsfield, Buckinghamshire, UK",

"0000-00-00","0000-00-00",101,

"",101,"",101,"",101,"Terry",101 );

3.查询

SELECT author_id, author_canonical FROM authors_xml WHERE author_birthplace LIKE '%UK';

MariaDB [isfdb]> SELECT author_id, author_canonical FROM authors_xml WHERE author_birthplace LIKE '%UK';

+-----------+------------------+

| author_id | author_canonical |

+-----------+------------------+

| 101 | Terry Pratchett |

+-----------+------------------+

1 row in set (0.00 sec)

五.通过CONNECT访问mysql

1.建表

CREATE TABLE websites_2 (

site_id int(11),

site_name varchar(255),

site_url varchar(1024),

PRIMARY KEY (site_id)

) ENGINE=CONNECT TABLE_TYPE=MYSQL

CONNECTION='mysql://jlive:liujun@192.168.130.254/isfdb/websites';

2. 插入两条记录

INSERT websites_2 VALUES

("","MariaDB.com","https://mariadb.com"),

("","MariaDB.org","https://mariadb.org");

3.查询记录

MariaDB [isfdb]> SELECT * FROM websites WHERE LENGTH(site_url)<40;

+---------+----------------+-----------------------------------------+-------------+

+---------+----------------+-----------------------------------------+-------------+

| 8 | Barnes & Noble | http://www.barnesandnoble.com/s/%s | 1 |

| 13 | Powells | http://www.powells.com/biblio?isbn=%s | 1 |

| 15 | WorldCat | http://www.worldcat.org/isbn/%s | 1 |

| 16 | Smashwords | http://www.smashwords.com/isbn/%s | 1 |

| 19 | LibraryThing | http://www.librarything.com/isbn/%s | 1 |

| 21 | GoodReads | http://www.goodreads.com/book/isbn/%s | 1 |

| 28 | Booktopia | http://www.booktopia.com.au/prod%s.html | 1 |

+---------+----------------+-----------------------------------------+-------------+

13 rows in set (0.01 sec)

MariaDB [isfdb]> SELECT * FROM websites_2 WHERE LENGTH(site_url)<40;

+---------+----------------+-----------------------------------------+

| site_id | site_name | site_url |

+---------+----------------+-----------------------------------------+

| 5 | Amazon CA | http://www.amazon.ca/dp/%s |

| 6 | Amazon DE | http://www.amazon.de/dp/%s |

| 7 | Amazon FR | http://www.amazon.fr/dp/%s |

| 8 | Barnes & Noble | http://www.barnesandnoble.com/s/%s |

| 13 | Powells | http://www.powells.com/biblio?isbn=%s |

| 15 | WorldCat | http://www.worldcat.org/isbn/%s |

| 16 | Smashwords | http://www.smashwords.com/isbn/%s |

| 17 | Open Library | http://openlibrary.org/isbn/%s |

| 19 | LibraryThing | http://www.librarything.com/isbn/%s |

| 21 | GoodReads | http://www.goodreads.com/book/isbn/%s |

| 28 | Booktopia | http://www.booktopia.com.au/prod%s.html |

| 30 | MariaDB.com | https://mariadb.com |

| 31 | MariaDB.org | https://mariadb.org |

+---------+----------------+-----------------------------------------+

13 rows in set (0.00 sec)

六.使用XCOL表类型

当某个字段的值是一个列表时，XCOL就非常有用

1.建表

CREATE TABLE superheroes (

team varchar(50),

heroes varchar(1024)

);

2.插入记录

INSERT superheroes VALUES

("The Avengers","Thor, Iron Man, Black Widow, Hawkeye, Hulk,

Captain America"),

("The Justice League", "Superman, Batman, Aquaman, Flash, Wonder

Woman"),

("The X-Men", "Storm, Cyclops, Wolverine, Rogue, Iceman");

3.创建XCOL表

CREATE USER 'foo'@'localhost';

GRANT SELECT ON isfdb.superheroes_xcol TO 'foo'@'localhost';

GRANT SELECT ON isfdb.superheroes TO 'foo'@'localhost';

CREATE TABLE superheroes_xcol ENGINE=CONNECT

TABLE_TYPE=XCOL TABNAME='superheroes'

OPTION_LIST='user=foo,colname=heroes';

4.查询

MariaDB [isfdb]> SELECT * FROM superheroes_xcol;

+--------------------+-------------------------+

| team | heroes |

+--------------------+-------------------------+

| The Avengers | Thor |

| The Avengers | Iron Man |

| The Avengers | Black Widow |

| The Avengers | Hawkeye |

| The Avengers | Hulk |

Captain America |

| The Justice League | Superman |

| The Justice League | Batman |

| The Justice League | Aquaman |

| The Justice League | Flash |

Woman |ue | Wonder

| The X-Men | Storm |

| The X-Men | Cyclops |

| The X-Men | Wolverine |

| The X-Men | Rogue |

| The X-Men | Iceman |

+--------------------+-------------------------+

16 rows in set (0.00 sec)

MariaDB [isfdb]> SELECT * FROM superheroes_xcol WHERE heroes LIKE "S%";

+--------------------+----------+

| team | heroes |

+--------------------+----------+

| The Justice League | Superman |

| The X-Men | Storm |

+--------------------+----------+

2 rows in set (0.00 sec)

MariaDB [isfdb]> SELECT team, count(heroes) FROM superheroes_xcol GROUP BY team;

+--------------------+---------------+

| team | count(heroes) |

+--------------------+---------------+

| The Avengers | 6 |

| The Justice League | 5 |

| The X-Men | 5 |

+--------------------+---------------+

3 rows in set (0.00 sec)

七.使用PIVOT表类型

非常适合于sort和count,比group by更容易理解

1.建表

USE test;

CREATE TABLE expenses (

who varchar(64),

day varchar(10),

what varchar(64),

amount varchar(10)

);

2.插入数据

INSERT expenses VALUES

("Daniel","2013-09-01","Clothing",42.50),

("Amy","2013-09-02","Food",5.22),

("Daniel","2013-09-01","Clothing",27.75),

("Daniel","2013-09-03","Food",10.27),

("Amy","2013-09-03","Gas",42.84),

("Amy","2013-09-01","Food",15.01),

("Amy","2013-09-01","Clothing",11.00),

("Daniel","2013-09-01","Gas",34.10),

("Amy","2013-09-02","Food",15.00),

("Daniel","2013-09-01","Food",12.50),

("Daniel","2013-09-02","Gas",32.20),

("Daniel","2013-09-03","Clothing",82.80),

("Amy","2013-09-03","Food",8.72),

("Daniel","2013-09-03","Gas",15.08),

("Daniel","2013-09-02","Clothing",17.27),

("Amy","2013-09-03","Clothing",32.00) ;

3.创建PIVOT类型表

GRANT SELECT ON test.expenses TO 'foo'@'localhost';

CREATE TABLE expenses_pivot ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=expenses OPTION_LIST='user=foo';

MariaDB [test]> SELECT who, day, what, SUM(amount) FROM expenses GROUP BY who, day, what;

+--------+------------+----------+-------------+

| who | day | what | SUM(amount) |

+--------+------------+----------+-------------+

| Amy | 2013-09-01 | Clothing | 11 |

| Amy | 2013-09-01 | Food | 15.01 |

| Amy | 2013-09-02 | Food | 20.22 |

| Amy | 2013-09-03 | Clothing | 32 |

| Amy | 2013-09-03 | Food | 8.72 |

| Amy | 2013-09-03 | Gas | 42.84 |

| Daniel | 2013-09-01 | Clothing | 70.25 |

| Daniel | 2013-09-01 | Food | 12.5 |

| Daniel | 2013-09-01 | Gas | 34.1 |

| Daniel | 2013-09-02 | Clothing | 17.27 |

| Daniel | 2013-09-02 | Gas | 32.2 |

| Daniel | 2013-09-03 | Clothing | 82.8 |

| Daniel | 2013-09-03 | Food | 10.27 |

| Daniel | 2013-09-03 | Gas | 15.08 |

+--------+------------+----------+-------------+

14 rows in set (0.00 sec)

七.使用OCCUR表类型

CREATE TABLE gadgets (

who varchar(64),

phone int,

tablet int,

mp3player int,

camera int

);

INSERT gadgets VALUES

("Jim",1,2,1,2),

("Bob",0,0,3,0),

("Tom",1,1,1,0),

("Joe",1,1,1,1),

("Rob",2,2,0,0),

("Tim",0,3,1,1)

;

CREATE TABLE gadgets_occur (

who varchar(64) NOT NULL,

gadget varchar(16) NOT NULL,

number int NOT NULL

) ENGINE=CONNECT TABLE_TYPE=OCCUR TABNAME=gadgets

OPTION_LIST='user=foo,occurcol=number,rankcol=gadget'

COLIST='phone,tablet,mp3player,camera';

GRANT ALL ON isfdb.gadgets TO foo@localhost;

注意: 用户一定要有相关权限，不然无法进行查询等操作

SELECT * FROM gadgets_occur;

SELECT * FROM gadgets_occur

WHERE gadget="tablet" and number > 1;

MariaDB [isfdb]> SELECT * FROM gadgets_occur WHERE number > 1;

+-----+-----------+--------+

| who | gadget | number |

+-----+-----------+--------+

| Jim | tablet | 2 |

| Jim | camera | 2 |

| Bob | mp3player | 3 |

| Rob | phone | 2 |

| Rob | tablet | 2 |

| Tim | tablet | 3 |

+-----+-----------+--------+

6 rows in set (0.00 sec)

MariaDB [isfdb]> SELECT who,phone AS gadget FROM gadgets WHERE phone > 1 UNION ALL SELECT who,tablet FROM gadgets WHERE tablet > 1 UNION ALL SELECT who,mp3player FROM gadgets WHERE mp3player > 1 UNION ALL SELECT who,camera FROM gadgets WHERE camera > 1;

+------+--------+

| who | gadget |

+------+--------+

| Rob | 2 |

| Jim | 2 |

| Rob | 2 |

| Tim | 3 |

| Bob | 3 |

| Jim | 2 |

+------+--------+

6 rows in set (0.00 sec)

八.使用WMI表类型(二进制包不支持)

CREATE TABLE alias (

friendlyname char(32) NOT NULL,

target char(64) NOT NULL

) ENGINE=CONNECT TABLE_TYPE=WMI

OPTION_LIST='Namespace=root\cli,Class=Msft_CliAlias';

SELECT * FROM alias;

九.使用MAC地址表类型(二进制包不支持)

CREATE TABLE host (

hostname varchar(132) flag=1,

domain varchar(132) flag=2,

ipaddr char(16) flag=15,

gateway char(16) flag=17,

dhcp char(16) flag=18,

leaseexp datetime flag=23

) ENGINE=CONNECT TABLE_TYPE=MAC;

SELECT * FROM host;