Bulk operations:
I had used the below script to
bulk rename mysql tables from lowercase to all upper case. And it worked like charm.
The same concept can be used to do any other like column renaming, table renaming any of the alter table commands.
SELECT 'USE yourdbname;'
UNION
SELECT CONCAT("ALTER TABLE ",`table_name`," RENAME AS ",upper(`table_name`),";") from information_schema.`TABLES` where `table_schema` = 'yourdbname' INTO OUTFILE '/tmp/result.txt';
SOURCE '/tmp/result.txt';
If you are using query browser, you might try using "LOAD DATA LOCAL INFILE"
CSV Export:
======================================
Given a query such as
SELECT order_id,product_name,qty FROM orders
The below query would create a csv file with the order_id, product_name, qty.
SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
Further to add a heading row in the csv
SELECT 'order_id','product_name','qty'
UNION
SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
Out put format.
"order_id","product_name","qty"
"1","Tech-Recipes sock puppet","14.95"
"2","Tech-Recipes chef's hat","18.95"
...
Keep in mind that the output file must not already exist and that the user MySQL is running as has write permissions to the directory MySQL is attempting to write the file to.
======================================
Found part of this interesting code to export data as csv
here.
