paul bennett

5 MySQL tricks you may not know

Posted on: September 16, 2009

1. Produce terminal-friendly output

Occasionally you may be using the command line and find yourself limited by the size of either the table you’re getting results from or by the size of the terminal window you’re using. (I find the DPS window particularly resistant to resizing.

You can get results in this format
column1_name: data
column2_name: data

instead of the default tabular layout by using the \G flag at the end of your select statement:

select * from my_table where id = "3"\G

2. Better, more flexible result ordering

Recently I found myself a bit hamstrung by the way a table had been designed. I wanted chronological ordering, but the default ordering by date wasn’t ordering the results exactly chronologically.

I found that I could use functions in the ‘order by’ component of my selects to get better ordering

select * from my_table order by concat(entry_date, entry_time);

(group by could also be used to similar effect)

3. Logging your command line session

Occasionally you may end a command line MySQL session and then think “Argh – I wish I’d saved that query!”. MySQL has a handy logging feature which writes your session (input and output) to a file so you can pull out those tricky queries and keep them for future reference. To do so, log in using the -tee flag:

mysql -u username -p -tee=/path/to/your/log.file

Obviously you need write access to the folder you’re writing to. If the file doesn’t exist, MySQL will create it for you.

Beware: if the file already exists, MySQL will overwrite it with the new session data.

4.  Loading .CSV file data into a table using the command line

MySQL has a very simple way to load .csv file data into tables. Once you have the syntax down, I find this a lot easier and ess error-prne than commonly used web-based or GUI tools.

Syntax I’ve used is:

load data infile '/path/to/your.csv' into table my_tables fields terminated by ',' optionally enclosed by '"' lines terminated by '\n';

Works a treat.

5. Cloning tables

This is really simple, but is something I use a lot to create development tables with live data (when necessary).

create table my_new_table as select * from my_old_table;

This does exactly what it looks like – creates an exact renamed copy of the original table (my_old_table in this instance).

Beware: keys and indexes aren’t recreated – you’ll need to add these back in manually.

What about you  – any handy MySQL tricks to share?

About these ads

2 Responses to "5 MySQL tricks you may not know"

Useful post! I didn’t know 3 of the 5 tips.
It could be maybe interesting the usage of LAST_INSERT_ID.
If you’re in doubt of what’s the result of a query or stored procedure, you can retrieve the latest ID inserted by it with SELECT LAST_INSERT_ID();

An example using perl here: http://varloggiacomolog.blogspot.com/2009/05/mysql-last-insert-id.html

Regards,
Giacomo

Thanks Giacomo,

There are still a lot of things I need to become familiar with – there are definitely a lot more great MySQL tricks then the few I’ve covered
:)
Paul

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: