MariaDB 使用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;
|
TokuDB
|
TokuDB_trx
|
TokuDB_lock_waits
|
TokuDB_locks
|
TokuDB_file_map
|
TokuDB_fractal_tree_info
|
TokuDB_fractal_tree_block_map | ACTIVE
| CONNECT
+-------------------------------+----------+--------------------+---------------+---------+
63 rows in set (0.00 sec)
二.创建CONNECT表
CREATE TABLE test_data (
) ENGINE=CONNECT DEFAULT CHARSET=latin1
TABLE_TYPE可以有CSV, XML, INI, ODBC, MYSQL, DIR
Flag Number Information |
|
MariaDB [isfdb]> SELECT * FROM test_data;
+-----------------------------------------------------------+---------------------+----------+
|
path
+-----------------------------------------------------------+---------------------+----------+
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | authors
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
award_cats
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
award_types
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
awards
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
bad_images
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
canonical_author
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | cleanup
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
directory
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
emails
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | history
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
languages
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
license_keys
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
magazine
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
metadata
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
missing_author_urls |
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | mw_user
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
mw_user_groups
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | notes
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
pseudonyms
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
pub_content
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
pub_series
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
publishers
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
notes_tokudb
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
pubs
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
reference
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
series
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
submissions
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
tag_mapping
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
tags
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
title_awards
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
title_relationships |
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
trans_legal_names
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
trans_pub_series
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
trans_publisher
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
user_languages
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
user_preferences
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
user_sites
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
verification
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ | votes
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
webpages
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
websites
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
authors_tokudb
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
titles
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
pub_authors
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
test_data
+-----------------------------------------------------------+---------------------+----------+
45 rows in set (0.01 sec)
MariaDB [isfdb]> SELECT path,COUNT(*),SUM(filesize) FROM test_data GROUP BY path;
+-----------------------------------------------------------+----------+---------------+
|
path
+-----------------------------------------------------------+----------+---------------+
|
/opt/mariadb-10.1.13-linux-glibc_214-x86_64/data/./isfdb/ |
+-----------------------------------------------------------+----------+---------------+
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,
FROM authors ORDER BY author_id LIMIT 100;
2.创建CSV类型的表
CREATE TABLE authors_csv (
CREATE TABLE authors_csv2 (
) ENGINE=CONNECT DEFAULT CHARSET=utf8
TABLE_TYPE='CSV'
FILE_NAME='/tmp/authors_csv.CSV'
SEP_CHAR=',' QCHAR='"' QUOTED=1;
3.插入记录
INSERT authors_csv VALUES (
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;
+-----------+--------------------+---------------------+---------------------------------+------------------+------------------+
|
author_id | author_canonical
+-----------+--------------------+---------------------+---------------------------------+------------------+------------------+
|
|
|
|
+-----------+--------------------+---------------------+---------------------------------+------------------+------------------+
4 rows in set (0.01 sec)
四.读写XML
1.建表
CREATE TABLE authors_xml (
2.插入记录
INSERT authors_xml VALUES (
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 |
+-----------+------------------+
|
+-----------+------------------+
1 row in set (0.00 sec)
五.通过CONNECT访问mysql
1.建表
CREATE TABLE websites_2 (
2.
插入两条记录
INSERT websites_2 VALUES
3.查询记录
MariaDB [isfdb]> SELECT * FROM websites WHERE LENGTH(site_url)<40;
+---------+----------------+-----------------------------------------+-------------+
| site_id
| site_name
+---------+----------------+-----------------------------------------+-------------+
|
|
|
|
|
|
|
|
|
|
|
|
|
+---------+----------------+-----------------------------------------+-------------+
13 rows in set (0.01 sec)
MariaDB [isfdb]> SELECT * FROM websites_2 WHERE LENGTH(site_url)<40;
+---------+----------------+-----------------------------------------+
| site_id
| site_name
+---------+----------------+-----------------------------------------+
|
|
|
|
|
|
|
|
|
|
|
|
|
+---------+----------------+-----------------------------------------+
13 rows in set (0.00 sec)
六.使用XCOL表类型
当某个字段的值是一个列表时,XCOL就非常有用
1.建表
CREATE TABLE superheroes (
2.插入记录
INSERT superheroes VALUES
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
4.查询
MariaDB [isfdb]> SELECT * FROM superheroes_xcol;
+--------------------+-------------------------+
| team
+--------------------+-------------------------+
| The
Avengers
| The
Avengers
| The
Avengers
| The
Avengers
| The
Avengers
| The
Justice League | Superman
| The
Justice League | Batman
| The
Justice League | Aquaman
| The
Justice League | Flash
| The
X-Men
| The
X-Men
| The
X-Men
| The
X-Men
| The
X-Men
+--------------------+-------------------------+
16 rows in set (0.00 sec)
MariaDB [isfdb]> SELECT * FROM superheroes_xcol WHERE heroes LIKE "S%";
+--------------------+----------+
| team
+--------------------+----------+
| The Justice League | Superman |
| The
X-Men
+--------------------+----------+
2 rows in set (0.00 sec)
MariaDB [isfdb]> SELECT team, count(heroes) FROM superheroes_xcol GROUP BY team;
+--------------------+---------------+
| team
+--------------------+---------------+
| The
Avengers
| The
Justice League |
| The
X-Men
+--------------------+---------------+
3 rows in set (0.00 sec)
七.使用PIVOT表类型
非常适合于sort和count,比group by更容易理解
1.建表
USE
test;
CREATE TABLE expenses (
);
2.插入数据
INSERT expenses VALUES
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
+--------+------------+----------+-------------+
|
Amy
|
Amy
|
Amy
|
Amy
|
Amy
|
Amy
| Daniel
| 2013-09-01 | Clothing |
| Daniel
| 2013-09-01 | Food
| Daniel
| 2013-09-01 | Gas
| Daniel
| 2013-09-02 | Clothing |
| Daniel
| 2013-09-02 | Gas
| Daniel
| 2013-09-03 | Clothing |
| Daniel
| 2013-09-03 | Food
| Daniel
| 2013-09-03 | Gas
+--------+------------+----------+-------------+
14 rows in set (0.00 sec)
七.使用OCCUR表类型
CREATE TABLE gadgets (
);
INSERT gadgets VALUES
;
CREATE TABLE gadgets_occur (
GRANT ALL ON isfdb.gadgets TO foo@localhost;
注意: 用户一定要有相关权限,不然无法进行查询等操作
SELECT * FROM gadgets_occur;
SELECT * FROM gadgets_occur
MariaDB
[isfdb]> SELECT * FROM gadgets_occur
+-----+-----------+--------+
| who |
gadget
+-----+-----------+--------+
| Jim |
tablet
| Jim |
camera
| Bob |
mp3player |
| Rob |
phone
| Rob |
tablet
| Tim |
tablet
+-----+-----------+--------+
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
+------+--------+
|
Rob
|
Jim
|
Rob
|
Tim
|
Bob
|
Jim
+------+--------+
6 rows in set (0.00 sec)
八.使用WMI表类型(二进制包不支持)
CREATE TABLE alias (
SELECT * FROM alias;
九.使用MAC地址表类型(二进制包不支持)
CREATE TABLE host (
SELECT * FROM host;