SQL Notes

How to pull and load data from SELECT query

05.06.2019

Login to mariadb; Save the result of a SELECT query to a file

Links:

• https://mariadb.com/kb/en/library/load-data-infile/
• https://mariadb.com/kb/en/library/select-into-outfile/

 use wiki2016;
 SELECT * INTO OUTFILE 'pages.dat' FROM wiki2016.page WHERE page_id < 10 AND page_namespace = 0;

Pages pages.dat stored in /var/lib/mysql/wiki2016

dump only the db schema without data

$ mysqldump --user=andre --default-character-set=utf8 --no-data --skip-triggers "wiki2016" -p

create a new db

 CREATE DATABASE wikitest;

And import the import schema

$ mysql --user=andre wikitest -p < wiki2016.sql 

Load the data file with default tab separator and one entry per line. And test if the data was inserted to the DB

 LOAD DATA INFILE '/var/lib/mysql/wiki2016/pages.dat' REPLACE INTO TABLE wikitest.page;
 use wikitest;
 SELECT * FROM page;

+---------+----------------+--------------------+-------------------+------------------+-------------+----------------+----------------+--------------------+-------------+----------+--------------------+-----------+
| page_id | page_namespace | page_title         | page_restrictions | page_is_redirect | page_is_new | page_random    | page_touched   | page_links_updated | page_latest | page_len | page_content_model | page_lang |
+---------+----------------+--------------------+-------------------+------------------+-------------+----------------+----------------+--------------------+-------------+----------+--------------------+-----------+
|       1 |              0 | Main_Page          |                   |                0 |           0 | 0.849653152175 | 20190115085635 | 20190519111542     |        2458 |     1776 | wikitext           | NULL      |
|       2 |              0 | Test               |                   |                0 |           1 | 0.473474610327 | 20150318120109 | 20140909225556     |           4 |       18 | wikitext           | NULL      |
|       6 |              0 | WDKA               |                   |                0 |           0 |  0.43307080073 | 20190305091605 | 20190305091605     |        2464 |    12863 | wikitext           | NULL      |
|       8 |              0 | PageName           |                   |                0 |           1 | 0.033588984951 | 20150318120053 | 20140910082629     |          18 |       30 | wikitext           | NULL      |
|       9 |              0 | Calendars:PageName |                   |                0 |           1 | 0.976488411003 | 20150318120218 | 20140910082630     |          19 |        0 | wikitext           | NULL      |
+---------+----------------+--------------------+-------------------+------------------+-------------+----------------+----------------+--------------------+-------------+----------+--------------------+-----------+
5 rows in set (0.00 sec)

HackPact 2019