I had an issue recently where I needed to get a list of keywords for one of my websites. The website uses two databases, one for the blog, and a different one for the main part of the site. So I wanted to select the unique (distinct) keywords from the keyword tables on both databases.
This is the query I ended up with, which I will explain in a moment:
SELECT `name` COLLATE utf8_unicode_ci FROM `db_1`.`tags_table` UNION SELECT `Subject` COLLATE utf8_unicode_ci FROM `db_2`.`keywords_table` INTO OUTFILE '/tmp/tags.txt';
So, to start with, we need to get the results from both tables merged together. I did this by using two queries joined by a UNION.
SELECT `name` FROM `tags_table` UNION SELECT `Subject` FROM `keywords_table`
Because the tables are in different databases, we need to prefix the table name followed by a dot (.) when specifying the table to retrieve the records from. E.g. db_1.tags_table selects from the table tags_table in the database db_1. You will also need to be signed into mysql as a user that can access both databases (e.g. the root user) if you want to query across multiple databases.
SELECT `name` FROM `db_1`.`tags_table` UNION SELECT `Subject` FROM `db_2`.`keywords_table`
The two tables I needed to select from had different collations. So I also needed to specify the collation in the query to avoid an Illegal mix of collations error.
SELECT `name` COLLATE utf8_unicode_ci FROM `db_1`.`tags_table` UNION SELECT `Subject` COLLATE utf8_unicode_ci FROM `db_2`.`keywords_table`
The default behaviour of UNION is that duplicate rows are removed from the result. So there is no need to use UNION DISTINCT (though you can if you want). If you do want duplicate rows, use UNION ALL.
All that’s left to do is to get the result into a text file. To do this use INTO OUTFILE at the end of the query, followed by the filename the results should be written to. The file must not already exist, and the mysql user must have write permission for the directory where you want to save the folder. The easiest thing to do is to write the file to the /tmp directory, and then you can move the file from there to wherever you want manually.
SELECT `name` COLLATE utf8_unicode_ci FROM `db_1`.`tags_table` UNION SELECT `Subject` COLLATE utf8_unicode_ci FROM `db_2`.`keywords_table` INTO OUTFILE '/tmp/tags.txt';