Connecting to remote MySQL installations

A question that seems to have poped up a number of time this week over on the MySQL forums is how to connect to MySQL installations on a remote machine, be it on the same network or across the internet.

If you have used Oracle the way to do this is to set up an entry in your tnsnames.ora file, for those that don't know this is a configuration file containing all of the information Oracle needs to connect to database either locally or remotely.

If we are using the SQL*Plus command line client for Oracle we can connect to another server using the following command.

SQLPlus user/password@database 

The database portion is in fact a reference to an entry in the tnsnames.ora file which contains the machine name or IP address of the machine and database SID we want to connect to.

In MySQL we don't need to use an additional file to connect to another instance, we simnply add a -h parameter to our call to mysql.

mysql -h hostname -u root -p 

This then connects us to the remote database. The -h can be a machine name, webserver or IP address.

Using IFNULL ( )

NVL() is a great function in Oracle to convert NULL columns into a more useable format, for example it's often the case on reports to convert NULL numeric columns to 0 for better readability.

In MySQL you can use IFNULL() in the same way.

mysql> select emp_id, salary, bonus from salary;
+--------+---------+-------+
| emp_id | salary  | bonus |
+--------+---------+-------+
|      1 | 2000.00 |  NULL |
+--------+---------+-------+
1 row in set (0.00 sec)

mysql> select emp_id, salary, ifnull(bonus,0) from salary;
+--------+---------+-----------------+
| emp_id | salary  | ifnull(bonus,0) |
+--------+---------+-----------------+
|      1 | 2000.00 |            0.00 |
+--------+---------+-----------------+
1 row in set (0.00 sec)

Multi Row inserts

One of the features I like best in MySQL is the ability to insert mulitple rows of data using a single insert statement. In Oracle you need to write an insert statement for each row including the list of columns each time, but in MySQL you only need to list the column names you need once and then list the values clause multiple times.

So in Oracle we have

insert into emps (emp_id, name, salary) 
values (1,'Dave',2000.00);

insert into emps (emp_id, name, salary) 
values (2,'John',2100.00);

insert into emps (emp_id, name, salary) 
values (3,'Barry',1900.00);

But in MySQL this can be done like so.

insert into emps (emp_id, name, salary)
values (1,'Dave',2000.00),(2,'John',2100.00),(3,'Barry',1900.00)

It's shorter, tidy and more logical.

IF in SQL statements

In older versions of Oracle you can use DECODE to implement conditional logic within SQL statements, from 8i you can also use CASE. In MySQL the decode functionality is replicated using IF.

IF is infact a function call rather than a part of the SQL syntax but it works pretty well in most cases. For example...

mysql> select if(emp_id=1,'X','Y') as test, emp_id from emps;
+------+--------+
| test | emp_id |
+------+--------+
| X    |      1 |
| Y    |      2 |
| Y    |      3 |
+------+--------+
3 rows in set (0.06 sec)

group_concat

One great function I came across a few days ago is group_concat. Many times I've been asked to produce a query which pivots records in a table to take data which is presented in column format into a single row. In Oracle it can get pretty complicated either using a number of subqueries (which presents it's own problems) or analytical functions (which I'll admit I haven't used anywhere near enough).

Sitting on my desk is a timesheet, lets say the boss wants the reporting flexibility of adding in data one day at a time, fine we just create a table like so...

mysql> create table ts_data(emp_id int, week_no int, work_date date, hours numeric(4,2));
Query OK, 0 rows affected (0.11 sec)

I'll insert some data so we get the following data set.

mysql> select * from ts_data;
+--------+---------+------------+-------+
| emp_id | week_no | work_date  | hours |
+--------+---------+------------+-------+
|      1 |       1 | 2005-07-18 |  8.00 |
|      1 |       1 | 2005-07-19 |  8.00 |
|      1 |       1 | 2005-07-20 |  8.00 |
|      1 |       1 | 2005-07-21 |  8.00 |
|      1 |       1 | 2005-07-22 |  8.00 |
|      1 |       2 | 2005-07-23 |  8.00 |
|      1 |       2 | 2005-07-24 |  8.00 |
+--------+---------+------------+-------+
7 rows in set (0.00 sec)

So let's say the boss wants to see one row for each week with all of the hours worked side by side. That's where group_concat comes in. All we need to do is set up our grouping rules, in this case group by emp_id and week_no then specify the data we want to concatenate on a single row.

mysql> select emp_id, week_no, group_concat(work_date,' - ', hours) from ts_data group by emp_id, week_no;
+--------+---------+-------------------------------------------------------------------------------------------+
| emp_id | week_no | group_concat(work_date,' - ', hours)                                                      |
+--------+---------+-------------------------------------------------------------------------------------------+
|      1 |       1 | 2005-07-18 - 8.00,2005-07-19 - 8.00,2005-07-20 - 8.00,2005-07-21 - 8.00,2005-07-22 - 8.00 |
|      1 |       2 | 2005-07-23 - 8.00,2005-07-24 - 8.00                                                       |
+--------+---------+-------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

An Oracle Developers Journey

If you have visited this blog before you may notice I have deleted all of the previous posts (I'd be suprised if anybody was looking however as I was pretty laxed about updating it).

This year I have been working with MySQL, building my site www.mysqldevelopment.com and working with various people in the MySQL community both on the MySQ forums and over at the Quest MySQL forums. I'm more of an Oracle developer, having worked with it for a number of years prior to looking into MySQL in a bit more detail late last year. I've been looking for something to do with the blog since I started it, it became pretty clear my private life is nowhere near interesting enough to maintain a blog on a daily basis.

So as from today I'm going to be writing about MySQL, and in particular little tips and tricks I have picked up recently that seem to make MySQL more usable and easy to live with.