How do I export a multi table database with exceptions?

Question

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

Rick James · Answer

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.

danblack · Answer

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

PHPMyAdmin isn't a tool for complex tasks.

How do I export a multi table database with exceptions?

2 Answers

Add your own answers!

Ask a Question