Tag Archives: mysql

MySQL change field separator for select dump

Posted on by
Reply

You can select from a table into an outfile, and change the field delimiter. This is interactive, and you must be logged in locally to the mysql server, with write permissions to wherever you want to write the file.

mysql> SELECT 'pid','email' UNION SELECT pid,email INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ',' FROM table;
Query OK, 11837 rows affected (0.21 sec)

mysql> Bye
# head /tmp/test.csv
pid,email
1081603,user1@fordodone.com
888151,user2@fordodone.com
781,user3@fordodone.com
2307364,user4@fordodone.com
2286573,user5@fordodone.com
2212194,user6@fordodone.com
2137603,user7@fordodone.com
500492,user8@fordodone.com
1514582,user9@fordodone.com

This is non-interactive and can be done from remote host:

# echo "select pid,email from table;" | mysql -h dbhost -udbuser -p -Ddbname | awk '{for (i=1;i<NF;i++) printf "%s,",$i; printf $i"\n"}' > /tmp/test.csv

MySQL count all rows in database by table

Posted on by
Reply

This gets a list of tables in database ‘interesting’ and how many rows are in each table. The second query just sums them up for a grand total.

mysql> use mysql;
Database changed
mysql> SELECT table_name,table_rows FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'interesting';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| a       |         29 |
| b       |          4 |
| c       |       1104 |
| d       |   55378308 |
| e       |   56925066 |
| f       |   53479079 |
| g       |   55963435 |
| h       |   53966893 |
| i       |   55839182 |
| j       |   56854816 |
| k       |   54613112 |
| l       |   55454111 |
| m       |   56133247 |
| n       |          7 |
| o       |          4 |
| p       |         18 |
| q       |         30 |
| r       |          5 |
| s       |          5 |
| t       |         68 |
| u       |         22 |
| v       |          5 |
| w       |          7 |
| x       |      66046 |
| y       |      69439 |
| z       |      87361 |
| aa      |      89519 |
| ab      |      75892 |
| ac      |      61785 |
| ad      |      64964 |
| ae      |      69905 |
| af      |      65599 |
| ag      |      69710 |
| ah      |        381 |
| ai      |        181 |
| aj      |    2285397 |
| ak      |    2272764 |
| al      |         10 |
+---------+------------+
38 rows in set (0.04 sec)

mysql> SELECT sum(table_rows) FROM INFORMATION_SCHEMA.tables WHERE table_schema = 'interesting';
+-----------------+
| sum(table_rows) |
+-----------------+
|       559887795 |
+-----------------+
1 row in set (0.05 sec)

mysql>

bulk change default assignee in Redmine

Posted on by
Reply

If you have hundreds of Redmine projects, and someone has created them from other projects, chances are you will have a default assignee that doesn’t belong on all those projects. Login to the Redmine database and update the projects table accordingly.

mysql> select id,name,default_assignee_id from projects where default_assignee_id=6;
+----+---------------+---------------------+
| id | name          | default_assignee_id |
+----+---------------+---------------------+
| 14 | Project1      |                   6 |
| 55 | SomeProject2  |                   6 |
| 56 | Proj3         |                   6 |
(many rows omitted)
+----+---------------+---------------------+
57 rows in set (0.01 sec)

mysql> update projecst set default_assignee_id=NULL where default_assignee_id=6;