0

I got 2 databases, an old one and a new one, with the exact same structure.

I got a main users table and all the tables in the database got an auto-incremented id columns as primary key, and a email column which is used to link each row in each table to a user.

The specific id I want to extract is the email.

They are no constraints and no foreign key.

The relationship is 1-n and 1-1 from the user table to the others ones.

(and yes, I am fully aware it is a really poorly designed database but it isn't mine)

I want to extract multiple users from the old database to the new one, with the corresponding data in all others tables.

AFAIK, you can do it without any problems from each tables, but I want to do it "database wide", like all tables at once.

All the other posts I've run into are to export multiples tables with the whole data in it, but I just want specific users.

I know there is a global search function in PhpMyAdmin where i can simply enter the email and it sort me all the matches in the databases like here

My problem would have been solved if there were an export option on this page but there isn't, and I'm not gonna browse each table for each user to extract the data manually.

With all these years working with MySQL, I was quite surprised that they isn't a native solution to this problem.

So I've decided to write a procedure with a array containing all the emails.

BEGIN
  SET @array = 'test@test.com,hello@test.com';
  SELECT * from users WHERE FIND_IN_SET(email, @array);
  SELECT * from address WHERE FIND_IN_SET(email, @array);
  SELECT * from messages WHERE FIND_IN_SET(email, @array);
  SELECT * from tickets WHERE FIND_IN_SET(email, @array);
END

But then, I've run into another problem, I can't export a procedure result (I've opened another topic for that), and even if I could with a workaround I've tested, I can only do it table by table since I can't export multiple SELECT at once.

I'm currently working on it, but I would rather ask if there is a move convenient solution since i'm pretty sure i'm not the only one who has run into this problem.

Thanks in advance,

EDIT

Thanks to @Akina, managed to export it with the following query to get a proper CSV:

SET @array = 'test@test.com,hello@test.com';
SELECT * INTO OUTFILE 'out.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
FROM users WHERE FIND_IN_SET(email, @array);
Tangogow
  • 23
  • 4
  • Use `SELECT .. INTO OUTFILE` for data exporting instead of simple SELECT. – Akina Aug 10 '20 at 09:52
  • join all tables, so that you have only **one** Result set and export it with OUTFILE – nbk Aug 10 '20 at 10:58
  • @nbk With a inner join right ? I could get a inner join work but how do I split up the table afterwards ? Because I need to re-import them after. Is there an easy way or do I have to split it by hand after the export ? – Tangogow Aug 10 '20 at 12:43
  • you neeed to imoort it into a temporary table and then split them apart, for taht the colums should be unique, depending on how many times you need to do that, it could be useful, but it is as practocal as importing 4 files – nbk Aug 10 '20 at 13:30

0 Answers0