TransWikia.com

How do I export a multi table database with exceptions?

Database Administrators Asked by soberuprising on December 21, 2020

I am working out of phpmyadmin to export data using an sql.

I have an 88 table database. I would like to export all data to a .csv from 85 of those tables where I am only keeping the rows in which my column Length(Status) > 0.

So far I’ve come up with being able to select all data from a single table. I would like to apply to my entire database except the 3 tables and export it a .csv.

SELECT * FROM table WHERE LENGTH(Status) > 0

2 Answers

SELECT
    CONCAT("SELECT INTO OUTFILE ... FROM ", table_name, " WHERE ...")
    FROM information_schema.tables
    WHERE table_name NOT IN ("not_me_1", "not_me_1", "not_me_1")
      AND schema_name = "my_database_name";

That will generate 85 SELECT statements. Copy/paste them into the phpadmin screen that lets you run arbitrary commands.

(And consider learning to use the mysql commandline tool.)

What kind of values do you have in status? LENGTH(Status) > 0 seems like a strange test. Also, beware of NULL values; learn about IS NULL.

Answered by Rick James on December 21, 2020

Use SELECT INTO OUTFILE ... WHERE LENGTH(Status) > 0 for each table.

PHPMyAdmin isn't a tool for complex tasks.

Answered by danblack on December 21, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP