Mysql – How to “select * from table from database” with -e

MySQL

I want to select and show 1 row form a table with the mysql -e.

I was trying out the following but no luck,

mysql -u root -e “select * from table1 from db01 limit 1”

Actually I am trying to dump a single line from hundreads of tables to take a sample from them and redirect to a file. So I want to use shell and don’t want to be in MySQL console.

I have used show the above way but select is not working.

Everyone is suggesting to do select * from table1 but didn’t mentioned how to mention database.
So how can I achieve this?

Best Answer

You have to from in your Select use only one Like in my post

use out file like

mysql -u root -e “select * from db01.table1 limit 1” > /tmp/output

Which looks like

+----------+
| count(*) |
+----------+
|      471 | 
+----------+

Related Solutions

MySQL – How to Handle Records Turnover

Not tested, but the idea is using same query twice with different LIMITs (depending on %HOURS passed) in a UNION.

(
SELECT * FROM villa_table v
 ORDER BY villa_order ASC, v.ID
 LIMIT %HOURS, 999999999999
) UNION ALL (
SELECT * FROM villa_table v
 ORDER BY villa_order ASC, v.ID
 LIMIT 0, %HOURS
)

You'll need to fill in %HOURS in your script language or stored procedure. Also once %HOURS is larger than the COUNT(*) of villa_table you'll need to restart it from 0.

Note how the parentheses are necessary.

Also note that the ORDER BY fields must uniquely identify rows (i.e. append the PRIMARY KEY!) to prevent possible ambiguous sorting.

Mysql – How does MySQL resolve schema information

I found a cause. All folders under the data directory were hidden attribute. I changed them then I can see tables and dump data.

Best Answer

Related Solutions

MySQL – How to Handle Records Turnover

Mysql – How does MySQL resolve schema information

Related Question